Querying the DB2 or DB2 for VM SYSCOLUMNS Catalog

With proper authorization, you can read the RDBMS catalog table, SYSCOLUMNS, as an alternate source of column information. For easy access to the catalog table, the data adapter provides Master Files DB2CAT for DB2 and SYSCOL for DB2 for VM. You can issue a FOCUS report request that queries the catalog table for column characteristics, creator ids, and table names.

For example, this FOCUS request lists column names, data types, and lengths for each table created by USER1:

table file syscol 
print 
?f
    CNAME        TNAME         CREATOR        COLNO           COLTYPE
    LENGTH       SYSLENGTH     NULLS          REMARKS         COLCOUNT
    HIGH2KEY     LOW2KEY       AVGCOLLEN      ORDERFIELD
 
cname coltype length 
by tname 
where creator is 'user1' 
end
 
NUMBER OF RECORDS IN TABLE=       84  LINES=     84

The first page of the report follows:

TNAME        CNAME            COLTYPE                LENGTH
-----        -----            -------                ------
ADDRESS      EID              CHAR                   9
             LN1              CHAR                   20
             AT               CHAR                   4
             LN2              CHAR                   20
             LN3              CHAR                   20
             ANO              INTEGER
BILLING      PART_NUM         CHAR                   7
             ORDER_NO         INTEGER
             DELIVER_DT       DATE
             INVOICE_DT       DATE
             INVOICE_AMT      DECIMAL                (15,3)
             DEL_INVOICE      INTEGER
CLIENTS      C_STREET         CHAR                   20
             C_CITY           CHAR                   10
             CUSTOMER_NUM     CHAR                   4
             C_ZIP            CHAR                   5
             C_COUNTRY        CHAR                   6
             BRANCH_NAME       CHAR                   8