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.
hi
what does mean of AVG_ACT_WAIT_TIME in PKG_CACHE_MON_SUMMARY ?
could please tell me about it
thanks
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0056514.html
It is the average time spent waiting for database activities per statement execution. I believe it is in miliseconds. Essentially, you can use it to get an idea of how much time is spent waiting for something in each execution of the statement.
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
Activities are described in this blog entry.
Hi
I always do this in simply way (from v.10.1)
db2 “describe select * from TABLE(MON_GET_TABLE(”,”,-1))”
It works:)