DB2 Administrative SQL Cookbook: Generating a Comma Separated List of Columns in a MON_GET Table Function

Purpose

To generate a comma separated list of columns in a MON_GET table function, in the same order you should get the columns if you query the table. This SQL should handle larger tables like MON_GET_DATABASE that have a very large number of columns.

This is especially useful when pulling data into Excel to play with it or getting it in a format to use in another database.

Version

Tested on 10.5. Should work on 10.1 and up.

Statement

select
  cast(listagg(cast(rtrim(substr(P.PARMNAME,1,40)) as varchar(32000)), ',') within group (order by P.Ordinal) as varchar(32000)) as PARMNAME
from sysibm.sysroutines r join sysibm.sysroutineparms p on p.routineschema=r.routineschema and p.routinename=r.routinename and p.specificname=r.specificname
where
  r.function_type='T'
  and r.ROUTINENAME='MON_GET_DATABASE'
;

Sample Output

MEMBER,MEMBER,DB_STATUS,DB_ACTIVATION_STATE,DB_CONN_TIME,CATALOG_PARTITION,LAST_BACKUP,CONNECTIONS_TOP,TOTAL_CONS,TOTAL_SEC_CONS,APPLS_CUR_CONS,APPLS_IN_DB2,NUM_ASSOC_AGENTS,AGENTS_TOP,NUM_COORD_AGENTS,COORD_AGENTS_TOP,NUM_LOCKS_HELD,NUM_LOCKS_WAITING,LOCK_LIST_IN_USE,ACTIVE_SORTS,ACTIVE_HASH_JOINS,ACTIVE_OLAP_FUNCS,DB_PATH,ACT_ABORTED_TOTAL,ACT_COMPLETED_TOTAL,ACT_REJECTED_TOTAL,AGENT_WAIT_TIME,AGENT_WAITS_TOTAL,POOL_DATA_L_READS,POOL_INDEX_L_READS,POOL_TEMP_DATA_L_READS,POOL_TEMP_INDEX_L_READS,POOL_TEMP_XDA_L_READS,POOL_XDA_L_READS,POOL_DATA_P_READS,POOL_INDEX_P_READS,POOL_TEMP_DATA_P_READS,POOL_TEMP_INDEX_P_READS,POOL_TEMP_XDA_P_READS,POOL_XDA_P_READS,POOL_DATA_WRITES,POOL_INDEX_WRITES,POOL_XDA_WRITES,POOL_READ_TIME,POOL_WRITE_TIME,CLIENT_IDLE_WAIT_TIME,DEADLOCKS,DIRECT_READS,DIRECT_READ_TIME,DIRECT_WRITES,DIRECT_WRITE_TIME,DIRECT_READ_REQS,DIRECT_WRITE_REQS,FCM_RECV_VOLUME,FCM_RECVS_TOTAL,FCM_SEND_VOLUME,FCM_SENDS_TOTAL,FCM_RECV_WAIT_TIME,FCM_SEND_WAIT_TIME,IPC_RECV_VOLUME,IPC_RECV_WAIT_TIME,IPC_RECVS_TOTAL,IPC_SEND_VOLUME,IPC_SEND_WAIT_TIME,IPC_SENDS_TOTAL,LOCK_ESCALS,LOCK_TIMEOUTS,LOCK_WAIT_TIME,LOCK_WAITS,LOG_BUFFER_WAIT_TIME,NUM_LOG_BUFFER_FULL,LOG_DISK_WAIT_TIME,LOG_DISK_WAITS_TOTAL,RQSTS_COMPLETED_TOTAL,ROWS_MODIFIED,ROWS_READ,ROWS_RETURNED,TCPIP_RECV_VOLUME,TCPIP_SEND_VOLUME,TCPIP_RECV_WAIT_TIME,TCPIP_RECVS_TOTAL,TCPIP_SEND_WAIT_TIME,TCPIP_SENDS_TOTAL,TOTAL_APP_RQST_TIME,TOTAL_RQST_TIME,WLM_QUEUE_TIME_TOTAL,WLM_QUEUE_ASSIGNMENTS_TOTAL,TOTAL_RQST_MAPPED_IN,TOTAL_RQST_MAPPED_OUT,TOTAL_CPU_TIME,TOTAL_WAIT_TIME,APP_RQSTS_COMPLETED_TOTAL,TOTAL_SECTION_SORT_TIME,TOTAL_SECTION_SORT_PROC_TIME,TOTAL_SECTION_SORTS,TOTAL_SORTS,POST_THRESHOLD_SORTS,POST_SHRTHRESHOLD_SORTS,SORT_OVERFLOWS,TOTAL_COMPILE_TIME,TOTAL_COMPILE_PROC_TIME,TOTAL_COMPILATIONS,TOTAL_IMPLICIT_COMPILE_TIME,TOTAL_IMPLICIT_COMPILE_PROC_TIME,TOTAL_IMPLICIT_COMPILATIONS,TOTAL_SECTION_TIME,TOTAL_SECTION_PROC_TIME,TOTAL_APP_SECTION_EXECUTIONS,TOTAL_ACT_TIME,TOTAL_ACT_WAIT_TIME,ACT_RQSTS_TOTAL,TOTAL_ROUTINE_TIME,TOTAL_ROUTINE_INVOCATIONS,TOTAL_COMMIT_TIME,TOTAL_COMMIT_PROC_TIME,TOTAL_APP_COMMITS,INT_COMMITS,TOTAL_ROLLBACK_TIME,TOTAL_ROLLBACK_PROC_TIME,TOTAL_APP_ROLLBACKS,INT_ROLLBACKS,TOTAL_RUNSTATS_TIME,TOTAL_RUNSTATS_PROC_TIME,TOTAL_RUNSTATS,TOTAL_REORG_TIME,TOTAL_REORG_PROC_TIME,TOTAL_REORGS,TOTAL_LOAD_TIME,TOTAL_LOAD_PROC_TIME,TOTAL_LOADS,CAT_CACHE_INSERTS,CAT_CACHE_LOOKUPS,PKG_CACHE_INSERTS,PKG_CACHE_LOOKUPS,THRESH_VIOLATIONS,NUM_LW_THRESH_EXCEEDED,LOCK_WAITS_GLOBAL,LOCK_WAIT_TIME_GLOBAL,LOCK_TIMEOUTS_GLOBAL,LOCK_ESCALS_MAXLOCKS,LOCK_ESCALS_LOCKLIST,LOCK_ESCALS_GLOBAL,DATA_SHARING_REMOTE_LOCKWAIT_COUNT,DATA_SHARING_REMOTE_LOCKWAIT_TIME,RECLAIM_WAIT_TIME,SPACEMAPPAGE_RECLAIM_WAIT_TIME,CF_WAITS,CF_WAIT_TIME,POOL_DATA_GBP_L_READS,POOL_DATA_GBP_P_READS,POOL_DATA_LBP_PAGES_FOUND,POOL_DATA_GBP_INVALID_PAGES,POOL_INDEX_GBP_L_READS,POOL_INDEX_GBP_P_READS,POOL_INDEX_LBP_PAGES_FOUND,POOL_INDEX_GBP_INVALID_PAGES,POOL_XDA_GBP_L_READS,POOL_XDA_GBP_P_READS,POOL_XDA_LBP_PAGES_FOUND,POOL_XDA_GBP_INVALID_PAGES,AUDIT_EVENTS_TOTAL,AUDIT_FILE_WRITES_TOTAL,AUDIT_FILE_WRITE_WAIT_TIME,AUDIT_SUBSYSTEM_WAITS_TOTAL,AUDIT_SUBSYSTEM_WAIT_TIME,DIAGLOG_WRITES_TOTAL,DIAGLOG_WRITE_WAIT_TIME,FCM_MESSAGE_RECVS_TOTAL,FCM_MESSAGE_RECV_VOLUME,FCM_MESSAGE_RECV_WAIT_TIME,FCM_MESSAGE_SENDS_TOTAL,FCM_MESSAGE_SEND_VOLUME,FCM_MESSAGE_SEND_WAIT_TIME,FCM_TQ_RECVS_TOTAL,FCM_TQ_RECV_VOLUME,FCM_TQ_RECV_WAIT_TIME,FCM_TQ_SENDS_TOTAL,FCM_TQ_SEND_VOLUME,FCM_TQ_SEND_WAIT_TIME,TOTAL_ROUTINE_USER_CODE_PROC_TIME,TOTAL_ROUTINE_USER_CODE_TIME,TQ_TOT_SEND_SPILLS,EVMON_WAIT_TIME,EVMON_WAITS_TOTAL,TOTAL_EXTENDED_LATCH_WAIT_TIME,TOTAL_EXTENDED_LATCH_WAITS,TOTAL_STATS_FABRICATION_TIME,TOTAL_STATS_FABRICATION_PROC_TIME,TOTAL_STATS_FABRICATIONS,TOTAL_SYNC_RUNSTATS_TIME,TOTAL_SYNC_RUNSTATS_PROC_TIME,TOTAL_SYNC_RUNSTATS,TOTAL_DISP_RUN_QUEUE_TIME,TOTAL_PEDS,DISABLED_PEDS,POST_THRESHOLD_PEDS,TOTAL_PEAS,POST_THRESHOLD_PEAS,TQ_SORT_HEAP_REQUESTS,TQ_SORT_HEAP_REJECTIONS,POOL_QUEUED_ASYNC_DATA_REQS,POOL_QUEUED_ASYNC_INDEX_REQS,POOL_QUEUED_ASYNC_XDA_REQS,POOL_QUEUED_ASYNC_TEMP_DATA_REQS,POOL_QUEUED_ASYNC_TEMP_INDEX_REQS,POOL_QUEUED_ASYNC_TEMP_XDA_REQS,POOL_QUEUED_ASYNC_OTHER_REQS,POOL_QUEUED_ASYNC_DATA_PAGES,POOL_QUEUED_ASYNC_INDEX_PAGES,POOL_QUEUED_ASYNC_XDA_PAGES,POOL_QUEUED_ASYNC_TEMP_DATA_PAGES,POOL_QUEUED_ASYNC_TEMP_INDEX_PAGES,POOL_QUEUED_ASYNC_TEMP_XDA_PAGES,POOL_FAILED_ASYNC_DATA_REQS,POOL_FAILED_ASYNC_INDEX_REQS,POOL_FAILED_ASYNC_XDA_REQS,POOL_FAILED_ASYNC_TEMP_DATA_REQS,POOL_FAILED_ASYNC_TEMP_INDEX_REQS,POOL_FAILED_ASYNC_TEMP_XDA_REQS,POOL_FAILED_ASYNC_OTHER_REQS,PREFETCH_WAIT_TIME,PREFETCH_WAITS,APP_ACT_COMPLETED_TOTAL,APP_ACT_ABORTED_TOTAL,APP_ACT_REJECTED_TOTAL,TOTAL_CONNECT_REQUEST_TIME,TOTAL_CONNECT_REQUEST_PROC_TIME,TOTAL_CONNECT_REQUESTS,TOTAL_CONNECT_AUTHENTICATION_TIME,TOTAL_CONNECT_AUTHENTICATION_PROC_TIME,TOTAL_CONNECT_AUTHENTICATIONS,POOL_DATA_GBP_INDEP_PAGES_FOUND_IN_LBP,POOL_INDEX_GBP_INDEP_PAGES_FOUND_IN_LBP,POOL_XDA_GBP_INDEP_PAGES_FOUND_IN_LBP,COMM_EXIT_WAIT_TIME,COMM_EXIT_WAITS,POOL_ASYNC_DATA_READS,POOL_ASYNC_DATA_READ_REQS,POOL_ASYNC_DATA_WRITES,POOL_ASYNC_INDEX_READS,POOL_ASYNC_INDEX_READ_REQS,POOL_ASYNC_INDEX_WRITES,POOL_ASYNC_XDA_READS,POOL_ASYNC_XDA_READ_REQS,POOL_ASYNC_XDA_WRITES,POOL_NO_VICTIM_BUFFER,POOL_LSN_GAP_CLNS,POOL_DRTY_PG_STEAL_CLNS,POOL_DRTY_PG_THRSH_CLNS,VECTORED_IOS,PAGES_FROM_VECTORED_IOS,BLOCK_IOS,PAGES_FROM_BLOCK_IOS,UNREAD_PREFETCH_PAGES,FILES_CLOSED,POOL_ASYNC_DATA_GBP_L_READS,POOL_ASYNC_DATA_GBP_P_READS,POOL_ASYNC_DATA_LBP_PAGES_FOUND,POOL_ASYNC_DATA_GBP_INVALID_PAGES,POOL_ASYNC_INDEX_GBP_L_READS,POOL_ASYNC_INDEX_GBP_P_READS,POOL_ASYNC_INDEX_LBP_PAGES_FOUND,POOL_ASYNC_INDEX_GBP_INVALID_PAGES,POOL_ASYNC_XDA_GBP_L_READS,POOL_ASYNC_XDA_GBP_P_READS,POOL_ASYNC_XDA_LBP_PAGES_FOUND,POOL_ASYNC_XDA_GBP_INVALID_PAGES,POOL_ASYNC_READ_TIME,POOL_ASYNC_WRITE_TIME,SKIPPED_PREFETCH_DATA_P_READS,SKIPPED_PREFETCH_INDEX_P_READS,SKIPPED_PREFETCH_XDA_P_READS,SKIPPED_PREFETCH_TEMP_DATA_P_READS,SKIPPED_PREFETCH_TEMP_INDEX_P_READS,SKIPPED_PREFETCH_TEMP_XDA_P_READS,SKIPPED_PREFETCH_UOW_DATA_P_READS,SKIPPED_PREFETCH_UOW_INDEX_P_READS,SKIPPED_PREFETCH_UOW_XDA_P_READS,SKIPPED_PREFETCH_UOW_TEMP_DATA_P_READS,SKIPPED_PREFETCH_UOW_TEMP_INDEX_P_READS,SKIPPED_PREFETCH_UOW_TEMP_XDA_P_READS,POOL_ASYNC_DATA_GBP_INDEP_PAGES_FOUND_IN,POOL_ASYNC_INDEX_GBP_INDEP_PAGES_FOUND_I,POOL_ASYNC_XDA_GBP_INDEP_PAGES_FOUND_IN_,CACHING_TIER,CACHING_TIER_IO_ERRORS,POOL_DATA_CACHING_TIER_L_READS,POOL_INDEX_CACHING_TIER_L_READS,POOL_XDA_CACHING_TIER_L_READS,POOL_COL_CACHING_TIER_L_READS,POOL_DATA_CACHING_TIER_PAGE_WRITES,POOL_INDEX_CACHING_TIER_PAGE_WRITES,POOL_XDA_CACHING_TIER_PAGE_WRITES,POOL_COL_CACHING_TIER_PAGE_WRITES,POOL_DATA_CACHING_TIER_PAGE_UPDATES,POOL_INDEX_CACHING_TIER_PAGE_UPDATES,POOL_XDA_CACHING_TIER_PAGE_UPDATES,POOL_COL_CACHING_TIER_PAGE_UPDATES,POOL_CACHING_TIER_PAGE_READ_TIME,POOL_CACHING_TIER_PAGE_WRITE_TIME,POOL_DATA_CACHING_TIER_PAGES_FOUND,POOL_INDEX_CACHING_TIER_PAGES_FOUND,POOL_XDA_CACHING_TIER_PAGES_FOUND,POOL_COL_CACHING_TIER_PAGES_FOUND,POOL_DATA_CACHING_TIER_GBP_INVALID_PAGES,POOL_INDEX_CACHING_TIER_GBP_INVALID_PAGE,POOL_XDA_CACHING_TIER_GBP_INVALID_PAGES,POOL_COL_CACHING_TIER_GBP_INVALID_PAGES,POOL_DATA_CACHING_TIER_GBP_INDEP_PAGES_F,POOL_INDEX_CACHING_TIER_GBP_INDEP_PAGES_,POOL_XDA_CACHING_TIER_GBP_INDEP_PAGES_FO,POOL_COL_CACHING_TIER_GBP_INDEP_PAGES_FO,POOL_ASYNC_DATA_CACHING_TIER_READS,POOL_ASYNC_INDEX_CACHING_TIER_READS,POOL_ASYNC_XDA_CACHING_TIER_READS,POOL_ASYNC_COL_CACHING_TIER_READS,POOL_ASYNC_DATA_CACHING_TIER_PAGE_WRITES,POOL_ASYNC_INDEX_CACHING_TIER_PAGE_WRITE,POOL_ASYNC_XDA_CACHING_TIER_PAGE_WRITES,POOL_ASYNC_COL_CACHING_TIER_PAGE_WRITES,POOL_ASYNC_DATA_CACHING_TIER_PAGE_UPDATE,POOL_ASYNC_INDEX_CACHING_TIER_PAGE_UPDAT,POOL_ASYNC_XDA_CACHING_TIER_PAGE_UPDATES,POOL_ASYNC_COL_CACHING_TIER_PAGE_UPDATES,POOL_ASYNC_DATA_CACHING_TIER_PAGES_FOUND,POOL_ASYNC_INDEX_CACHING_TIER_PAGES_FOUN,POOL_ASYNC_XDA_CACHING_TIER_PAGES_FOUND,POOL_ASYNC_COL_CACHING_TIER_PAGES_FOUND,POOL_ASYNC_DATA_CACHING_TIER_GBP_INVALID,POOL_ASYNC_INDEX_CACHING_TIER_GBP_INVALI,POOL_ASYNC_XDA_CACHING_TIER_GBP_INVALID_,POOL_ASYNC_COL_CACHING_TIER_GBP_INVALID_,POOL_ASYNC_DATA_CACHING_TIER_GBP_INDEP_P,POOL_ASYNC_INDEX_CACHING_TIER_GBP_INDEP_,POOL_ASYNC_XDA_CACHING_TIER_GBP_INDEP_PA,POOL_ASYNC_COL_CACHING_TIER_GBP_INDEP_PA,ROWS_DELETED,ROWS_INSERTED,ROWS_UPDATED,TOTAL_HASH_JOINS,TOTAL_HASH_LOOPS,HASH_JOIN_OVERFLOWS,HASH_JOIN_SMALL_OVERFLOWS,POST_SHRTHRESHOLD_HASH_JOINS,TOTAL_OLAP_FUNCS,OLAP_FUNC_OVERFLOWS,DYNAMIC_SQL_STMTS,STATIC_SQL_STMTS,FAILED_SQL_STMTS,SELECT_SQL_STMTS,UID_SQL_STMTS,DDL_SQL_STMTS,MERGE_SQL_STMTS,XQUERY_STMTS,IMPLICIT_REBINDS,BINDS_PRECOMPILES,INT_ROWS_DELETED,INT_ROWS_INSERTED,INT_ROWS_UPDATED,CALL_SQL_STMTS,POOL_COL_L_READS,POOL_TEMP_COL_L_READS,POOL_COL_P_READS,POOL_TEMP_COL_P_READS,POOL_COL_LBP_PAGES_FOUND,POOL_COL_WRITES,POOL_ASYNC_COL_READS,POOL_ASYNC_COL_READ_REQS,POOL_ASYNC_COL_WRITES,POOL_ASYNC_COL_LBP_PAGES_FOUND,POOL_COL_GBP_L_READS,POOL_COL_GBP_P_READS,POOL_COL_GBP_INVALID_PAGES,POOL_COL_GBP_INDEP_PAGES_FOUND_IN_LBP,POOL_ASYNC_COL_GBP_L_READS,POOL_ASYNC_COL_GBP_P_READS,POOL_ASYNC_COL_GBP_INVALID_PAGES,POOL_ASYNC_COL_GBP_INDEP_PAGES_FOUND_IN_,POOL_QUEUED_ASYNC_COL_REQS,POOL_QUEUED_ASYNC_TEMP_COL_REQS,POOL_QUEUED_ASYNC_COL_PAGES,POOL_QUEUED_ASYNC_TEMP_COL_PAGES,POOL_FAILED_ASYNC_COL_REQS,POOL_FAILED_ASYNC_TEMP_COL_REQS,SKIPPED_PREFETCH_COL_P_READS,SKIPPED_PREFETCH_TEMP_COL_P_READS,SKIPPED_PREFETCH_UOW_COL_P_READS,SKIPPED_PREFETCH_UOW_TEMP_COL_P_READS,TOTAL_COL_TIME,TOTAL_COL_PROC_TIME,TOTAL_COL_EXECUTIONS,NUM_POOLED_AGENTS,POST_THRESHOLD_HASH_JOINS,PKG_CACHE_NUM_OVERFLOWS,CAT_CACHE_OVERFLOWS,TOTAL_ASYNC_RUNSTATS,STATS_CACHE_SIZE,TOTAL_HASH_GRPBYS,HASH_GRPBY_OVERFLOWS,POST_THRESHOLD_HASH_GRPBYS,ACTIVE_HASH_GRPBYS,SORT_HEAP_ALLOCATED,SORT_SHRHEAP_ALLOCATED,SORT_SHRHEAP_TOP,POST_THRESHOLD_OLAP_FUNCS,POST_THRESHOLD_COL_VECTOR_CONSUMERS,TOTAL_COL_VECTOR_CONSUMERS,ACTIVE_HASH_GRPBYS_TOP,ACTIVE_HASH_JOINS_TOP,ACTIVE_OLAP_FUNCS_TOP,ACTIVE_PEAS,ACTIVE_PEAS_TOP,ACTIVE_PEDS,ACTIVE_PEDS_TOP,ACTIVE_SORT_CONSUMERS,ACTIVE_SORT_CONSUMERS_TOP,ACTIVE_SORTS_TOP,ACTIVE_COL_VECTOR_CONSUMERS,ACTIVE_COL_VECTOR_CONSUMERS_TOP,SORT_CONSUMER_HEAP_TOP,SORT_CONSUMER_SHRHEAP_TOP,SORT_HEAP_TOP,TOTAL_BACKUP_TIME,TOTAL_BACKUP_PROC_TIME,TOTAL_BACKUPS,TOTAL_INDEX_BUILD_TIME,TOTAL_INDEX_BUILD_PROC_TIME,TOTAL_INDEXES_BUILT,IDA_SEND_WAIT_TIME,IDA_SENDS_TOTAL,IDA_SEND_VOLUME,IDA_RECV_WAIT_TIME,IDA_RECVS_TOTAL,IDA_RECV_VOLUME,FCM_TQ_RECV_WAITS_TOTAL,FCM_MESSAGE_RECV_WAITS_TOTAL,FCM_TQ_SEND_WAITS_TOTAL,FCM_MESSAGE_SEND_WAITS_TOTAL,FCM_SEND_WAITS_TOTAL,FCM_RECV_WAITS_TOTAL

Modifications

This can obviously be modified to show the pages in a different MON_GET table function.

References

Also, see Using DB2 LUW’s Monitoring Table Functions for SQL to list available table functions and to get more details on the columns available.