Technical Blog Post
Abstract
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.
UID
ibm13286713