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.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 544

5 Comments

  1. Thanks
    But what does mean of “database activities” ? Is it about archive log ?
    When I used of MON_PKG_CACHE_SUMMARY I realized the bottleneck in some of query is AVG_ACT_WAIT_TIME. But what is it exactly ?
    You know there are some columns for lock info, preparation info, CPU info , .. that related to database activities and still there is a AVG_ACT_WAIT_TIME column.
    Which kind of activities is it ?
    Could you please tell me more
    Thank You in Advance

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.