How useful is table SYSCAT.COLUMNS for DBAs?

Body

I found SYSCAT.COLUMNS very useful as it contains all column names for all tables including system tables. So if I need to look for any system table containing information about specific utility, I go through this table and look for that utility name.

For eg.

If I need to look for system tables that stores information about 'REORG' utility, I run following command:

> db2 "select unique tabschema, tabname from syscat.columns where colname like '%REORG%' "

TABSCHEMA                      TABNAME                       

------------------------------ ------------------------------

SYSIBMADM                      ADMINTABINFO                  

SYSTOOLS                       HMON_ATM_INFO                 

SYSIBMADM                      SNAPTAB                       

SYSIBMADM                      SNAPTAB_REORG                 

  4 record(s) selected.

Or, following query:

> db2 "select tabschema, tabname, colname from syscat.columns" | grep -i reorg

SYSIBMADM    ADMINTABINFO    INPLACE_REORG_STATUS
SYSIBMADM    ADMINTABINFO    NUM_REORG_REC_ALTERS
SYSIBMADM    ADMINTABINFO    REORG_PENDING
SYSIBMADM    SNAPTAB         PAGE_REORGS
SYSIBMADM    SNAPTAB_REORG    DATA_PARTITION_ID
SYSIBMADM    SNAPTAB_REORG    DBPARTITIONNUM
SYSIBMADM    SNAPTAB_REORG    MEMBER
SYSIBMADM    SNAPTAB_REORG    PAGE_REORGS
SYSIBMADM    SNAPTAB_REORG    REORG_COMPLETION
SYSIBMADM    SNAPTAB_REORG    REORG_CURRENT_COUNTER
SYSIBMADM    SNAPTAB_REORG    REORG_END
SYSIBMADM    SNAPTAB_REORG    REORG_INDEX_ID
SYSIBMADM    SNAPTAB_REORG    REORG_LONG_TBSPC_ID
SYSIBMADM    SNAPTAB_REORG    REORG_MAX_COUNTER
SYSIBMADM    SNAPTAB_REORG    REORG_MAX_PHASE
SYSIBMADM    SNAPTAB_REORG    REORG_PHASE
SYSIBMADM    SNAPTAB_REORG    REORG_PHASE_START
SYSIBMADM    SNAPTAB_REORG    REORG_ROWSCOMPRESSED
SYSIBMADM    SNAPTAB_REORG    REORG_ROWSREJECTED
SYSIBMADM    SNAPTAB_REORG    REORG_START
SYSIBMADM    SNAPTAB_REORG    REORG_STATUS
SYSIBMADM    SNAPTAB_REORG    REORG_TBSPC_ID
SYSIBMADM    SNAPTAB_REORG    REORG_TYPE
SYSIBMADM    SNAPTAB_REORG    SNAPSHOT_TIMESTAMP
SYSIBMADM    SNAPTAB_REORG    TABNAME
SYSIBMADM    SNAPTAB_REORG    TABSCHEMA
SYSTOOLS    HMON_ATM_INFO    REORG_AVG_RUNTIME
SYSTOOLS    HMON_ATM_INFO    REORG_DETAIL
SYSTOOLS    HMON_ATM_INFO    REORG_FLAG
SYSTOOLS    HMON_ATM_INFO    REORG_HISTORY
SYSTOOLS    HMON_ATM_INFO    REORG_INDEX_NAME
SYSTOOLS    HMON_ATM_INFO    REORG_INDEX_ONLINE
SYSTOOLS    HMON_ATM_INFO    REORG_INDEX_SCHEMA
SYSTOOLS    HMON_ATM_INFO    REORG_LOCK
SYSTOOLS    HMON_ATM_INFO    REORG_NOTIFY
SYSTOOLS    HMON_ATM_INFO    REORG_STATE
SYSTOOLS    HMON_ATM_INFO    REORG_TIME

Later you may find  REORG_STATUS by querying table SYSIBMADM.SNAPTAB_REORG .

$ db2 "describe table SYSIBMADM.SNAPTAB_REORG"

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
SNAPSHOT_TIMESTAMP              SYSIBM    TIMESTAMP                   10     6 Yes   
TABNAME                         SYSIBM    VARCHAR                    128     0 Yes   
TABSCHEMA                       SYSIBM    VARCHAR                    128     0 Yes   
PAGE_REORGS                     SYSIBM    BIGINT                       8     0 Yes   
REORG_PHASE                     SYSIBM    VARCHAR                     16     0 Yes   
REORG_MAX_PHASE                 SYSIBM    INTEGER                      4     0 Yes   
REORG_CURRENT_COUNTER           SYSIBM    BIGINT                       8     0 Yes   
REORG_MAX_COUNTER               SYSIBM    BIGINT                       8     0 Yes   
REORG_TYPE                      SYSIBM    VARCHAR                    128     0 Yes   
REORG_STATUS                    SYSIBM    VARCHAR                     10     0 Yes   
REORG_COMPLETION                SYSIBM    VARCHAR                     10     0 Yes   
REORG_START                     SYSIBM    TIMESTAMP                   10     6 Yes   
REORG_END                       SYSIBM    TIMESTAMP                   10     6 Yes   
REORG_PHASE_START               SYSIBM    TIMESTAMP                   10     6 Yes   
REORG_INDEX_ID                  SYSIBM    BIGINT                       8     0 Yes   
REORG_TBSPC_ID                  SYSIBM    BIGINT                       8     0 Yes   
DBPARTITIONNUM                  SYSIBM    SMALLINT                     2     0 Yes   
DATA_PARTITION_ID               SYSIBM    INTEGER                      4     0 Yes   
REORG_ROWSCOMPRESSED            SYSIBM    BIGINT                       8     0 Yes   
REORG_ROWSREJECTED              SYSIBM    BIGINT                       8     0 Yes   
REORG_LONG_TBSPC_ID             SYSIBM    BIGINT                       8     0 Yes   
MEMBER                          SYSIBM    SMALLINT                     2     0 Yes   

  22 record(s) selected.

$ db2 "select TABSCHEMA, TABNAME, REORG_START, REORG_END, REORG_STATUS from SYSIBMADM.SNAPTAB_REORG"

-- This will give you detailed information of REORGs ran/running in your system.

You may run similar queries for other utilities like RUNSTATS, REBALANCE' etc and find out about information stored in system tables.