If you’re not familiar with MONREPORT, see my DB2 Basics entry on MONREPORT first.
Most of the references I’ve seen to customizing the MONREPORT module basically say “get the code out of the database, modify it, and deploy it”. (see IBM DB2 Knowledge Center Entry on Customizing the MONREPORT module reports)
I have found this rather frustrating when I’ve run across it, and when I find a lack of documentation frustrating, I tend to blog to fill the hole as much as possible. I also have a nice use case for doing this at work, so it is a good opportunity to play with it.
Why Customize
For some of the metrics, MONREPORT calculates the differences in parameters between the start and the end of a time period that you specify. This is the only built-in way to emulate the former monitor reset capability for the mon_get table functions. While you can create objects to do this yourself (see Monitoring in DB2 9.7, Part 1: Emulating data reset with the new DB2 9.7 monitoring table functions ), that requires the creation of multiple objects that some clients may not want you creating in their databases. Also, it can require quite a bit of effort to update the objects created in that article for each subsequent version of DB2.
My Goal in Customizing
My goal in customizing is that through the addition or modification of a single procedure in this module, I can get data out in a CSV format I want to load into some monitoring/tracking tables I use in a different database.
Challenges
There are a number of challenges here. The first is in simply extracting and getting the code into a managable format. Second is in understanding what the heck it’s all doing. Finally, there’s altering it to get it to do what I want.
Extracting the Code
This didn’t turn out to be too hard. I used this export statement:
export to DBSUMMARY.sql of del LOBFILE DBSUMMARY.lob select TEXT from syscat.procedures where procname='DBSUMMARY' with ur
I then took the lob file generated – called DBSUMMARY.lob.001.lob – and copied the text into a text editor on my laptop. I introduced line breaks (very helpful) to make it readable. I found that replacing a semicolon with a semicolon followed by a line break, a close commment (*/) with a close comment followed by a line break, and a comma with a a line break and a tab followed by a comma went a long way in this direction. I ended up with something that looked like this:
>
ALTER MODULE SYSIBMADM.MONREPORT PUBLISH OR REPLACE PROCEDURE DBSUMMARY ( IN TIMEINTERVAL INTEGER DEFAULT 10) SPECIFIC MONREPORT_DBSUMMARY LANGUAGE SQL DYNAMIC RESULT SETS 1 BEGIN DECLARE LOC VARCHAR(33);
DECLARE REPOUTPUT
, MSGCACHE
, CR_MSGCACHE REPORT_TYPE;
DECLARE DBNAME
, TODAY VARCHAR(38);
DECLARE I
, L
, IR
, LAST_IR
, IC
, CR_IC
, MSGCOUNT
, MSGINDEX INTEGER;
DECLARE DATE_COMPAT_ARRAY MONMETRICS_CHAR3_TYPE;
DECLARE CPU_PER_RQST
, WAIT_TIME_PCT DOUBLE;
DECLARE SC_SERVICE_CLASS_ID_ARRAY MONMETRICS_INT_TYPE;
DECLARE WL_WORKLOAD_NAME_ARRAY MONMETRICS_CHAR128_TYPE;
DECLARE PM_MEMBER_ARRAY MONMETRICS_SMALLINT_TYPE;
DECLARE CN_APPLICATION_HANDLE_ARRAY
, CN_TOTAL_APP_COMMITS_ARRAY
, CN_APP_RQSTS_COMPLETED_TOTAL_ARRAY
, CN_TOTAL_RQST_TIME_ARRAY
, CN_TOTAL_WAIT_TIME_ARRAY
, CN_ROWS_READ_ARRAY
, CN_ROWS_MODIFIED_ARRAY
, CN_TOTAL_CPU_TIME_ARRAY
, SC_TOTAL_APP_COMMITS_ARRAY
, SC_APP_RQSTS_COMPLETED_TOTAL_ARRAY
, SC_TOTAL_RQST_TIME_ARRAY
, SC_TOTAL_WAIT_TIME_ARRAY
, SC_ROWS_READ_ARRAY
, SC_ROWS_MODIFIED_ARRAY
, SC_TOTAL_CPU_TIME_ARRAY
, WL_TOTAL_APP_COMMITS_ARRAY
, WL_APP_RQSTS_COMPLETED_TOTAL_ARRAY
, WL_TOTAL_RQST_TIME_ARRAY
, WL_TOTAL_WAIT_TIME_ARRAY
, WL_ROWS_READ_ARRAY
, WL_ROWS_MODIFIED_ARRAY
, WL_TOTAL_CPU_TIME_ARRAY
, PM_RQSTS_COMPLETED_TOTAL_ARRAY
, PM_TOTAL_RQST_TIME_ARRAY
, PM_TOTAL_WAIT_TIME_ARRAY
, PM_IO_WAIT_TIME_ARRAY
, PM_TOTAL_CPU_TIME_ARRAY MONMETRICS_BIGINT_TYPE;
DECLARE WLM_QUEUE_TIME_TOTAL
, WLM_QUEUE_TIME_TOTAL2
, AGENT_WAIT_TIME
, AGENT_WAIT_TIME2
, TCPIP_RECVS_TOTAL
, TCPIP_RECVS_TOTAL2
, TCPIP_SENDS_TOTAL
, TCPIP_SENDS_TOTAL2
, IPC_RECVS_TOTAL
, IPC_RECVS_TOTAL2
, IPC_SENDS_TOTAL
, IPC_SENDS_TOTAL2
, TCPIP_SEND_WAIT_TIME
, TCPIP_SEND_WAIT_TIME2
, TCPIP_RECV_WAIT_TIME
, TCPIP_RECV_WAIT_TIME2
, IPC_SEND_WAIT_TIME
, IPC_SEND_WAIT_TIME2
, IPC_RECV_WAIT_TIME
, IPC_RECV_WAIT_TIME2
, TCPIP_RECV_VOLUME
, TCPIP_RECV_VOLUME2
, IPC_RECV_VOLUME
, IPC_RECV_VOLUME2
, TCPIP_SEND_VOLUME
, TCPIP_SEND_VOLUME2
, IPC_SEND_VOLUME
, IPC_SEND_VOLUME2
, LOCK_ESCALS
, LOCK_ESCALS2
, LOCK_WAITS
, LOCK_WAITS2
, LOCK_TIMEOUTS
, LOCK_TIMEOUTS2
, DEADLOCKS
, DEADLOCKS2
, ACT_COMPLETED_TOTAL
, ACT_COMPLETED_TOTAL2
, ACT_ABORTED_TOTAL
, ACT_ABORTED_TOTAL2
, ACT_REJECTED_TOTAL
, ACT_REJECTED_TOTAL2
, APP_RQSTS_COMPLETED_TOTAL
, APP_RQSTS_COMPLETED_TOTAL2
, TOTAL_RQST_TIME
, TOTAL_RQST_TIME2
, TOTAL_CPU_TIME
, TOTAL_CPU_TIME2
, TOTAL_WAIT_TIME
, TOTAL_WAIT_TIME2
, TOTAL_ACT_TIME
, TOTAL_ACT_TIME2
, TOTAL_ACT_WAIT_TIME
, TOTAL_ACT_WAIT_TIME2
, ROWS_READ
, ROWS_READ2
, ROWS_RETURNED
, ROWS_RETURNED2
, ROWS_MODIFIED
, ROWS_MODIFIED2
, POOL_DATA_L_READS
, POOL_DATA_L_READS2
, POOL_INDEX_L_READS
, POOL_INDEX_L_READS2
, POOL_TEMP_DATA_L_READS
, POOL_TEMP_DATA_L_READS2
, POOL_TEMP_INDEX_L_READS
, POOL_TEMP_INDEX_L_READS2
, POOL_TEMP_XDA_L_READS
, POOL_TEMP_XDA_L_READS2
, POOL_XDA_L_READS
, POOL_XDA_L_READS2
, POOL_DATA_P_READS
, POOL_DATA_P_READS2
, POOL_INDEX_P_READS
, POOL_INDEX_P_READS2
, POOL_TEMP_DATA_P_READS
, POOL_TEMP_DATA_P_READS2
, POOL_TEMP_INDEX_P_READS
, POOL_TEMP_INDEX_P_READS2
, POOL_TEMP_XDA_P_READS
, POOL_TEMP_XDA_P_READS2
, POOL_XDA_P_READS
, POOL_XDA_P_READS2
, POOL_READ_TIME
, POOL_READ_TIME2
, POOL_ASYNC_READ_TIME
, POOL_ASYNC_READ_TIME2
, POOL_WRITE_TIME
, POOL_WRITE_TIME2
, POOL_ASYNC_WRITE_TIME
, POOL_ASYNC_WRITE_TIME2
, POOL_DATA_WRITES
, POOL_DATA_WRITES2
, POOL_INDEX_WRITES
, POOL_INDEX_WRITES2
, POOL_XDA_WRITES
, POOL_XDA_WRITES2
, DIRECT_READ_TIME
, DIRECT_READ_TIME2
, DIRECT_READS
, DIRECT_READS2
, DIRECT_READ_REQS
, DIRECT_READ_REQS2
, DIRECT_WRITE_TIME
, DIRECT_WRITE_TIME2
, DIRECT_WRITES
, DIRECT_WRITES2
, DIRECT_WRITE_REQS
, DIRECT_WRITE_REQS2
, LOG_DISK_WAIT_TIME
, LOG_DISK_WAIT_TIME2
, LOG_DISK_WAITS_TOTAL
, LOG_DISK_WAITS_TOTAL2
, NUM_LOG_BUFFER_FULL
, NUM_LOG_BUFFER_FULL2
, CLIENT_IDLE_WAIT_TIME
, CLIENT_IDLE_WAIT_TIME2
, LOCK_WAIT_TIME
, LOCK_WAIT_TIME2
, FCM_SEND_WAIT_TIME
, FCM_SEND_WAIT_TIME2
, TOTAL_SORTS
, TOTAL_SORTS2
, SORT_OVERFLOWS
, SORT_OVERFLOWS2
, POST_THRESHOLD_SORTS
, POST_THRESHOLD_SORTS2
, POST_SHRTHRESHOLD_SORTS
, POST_SHRTHRESHOLD_SORTS2
, FCM_SEND_VOLUME
, FCM_SEND_VOLUME2
, FCM_SENDS_TOTAL
, FCM_SENDS_TOTAL2
, FCM_RECV_VOLUME
, FCM_RECV_VOLUME2
, FCM_RECVS_TOTAL
, FCM_RECVS_TOTAL2
, WLM_QUEUE_ASSIGNMENTS_TOTAL
, WLM_QUEUE_ASSIGNMENTS_TOTAL2
, FCM_RECV_WAIT_TIME
, FCM_RECV_WAIT_TIME2
, TOTAL_SECTION_PROC_TIME
, TOTAL_SECTION_PROC_TIME2
, TOTAL_SECTION_SORT_PROC_TIME
, TOTAL_SECTION_SORT_PROC_TIME2
, TOTAL_COMPILE_PROC_TIME
, TOTAL_COMPILE_PROC_TIME2
, TOTAL_IMPLICIT_COMPILE_PROC_TIME
, TOTAL_IMPLICIT_COMPILE_PROC_TIME2
, TOTAL_COMMIT_PROC_TIME
, TOTAL_COMMIT_PROC_TIME2
, TOTAL_ROLLBACK_PROC_TIME
, TOTAL_ROLLBACK_PROC_TIME2
, TOTAL_RUNSTATS_PROC_TIME
, TOTAL_RUNSTATS_PROC_TIME2
, TOTAL_REORG_PROC_TIME
, TOTAL_REORG_PROC_TIME2
, TOTAL_LOAD_PROC_TIME
, TOTAL_LOAD_PROC_TIME2
, TOTAL_APP_COMMITS
, TOTAL_APP_COMMITS2
, TOTAL_ROUTINE_INVOCATIONS
, TOTAL_ROUTINE_INVOCATIONS2
, TOTAL_ROUTINE_TIME
, TOTAL_ROUTINE_TIME2
, TOTAL_COMPILATIONS
, TOTAL_COMPILATIONS2
, PKG_CACHE_INSERTS
, PKG_CACHE_INSERTS2
, PKG_CACHE_LOOKUPS
, PKG_CACHE_LOOKUPS2
, CAT_CACHE_INSERTS
, CAT_CACHE_INSERTS2
, CAT_CACHE_LOOKUPS
, CAT_CACHE_LOOKUPS2
, INT_COMMITS
, INT_COMMITS2
, TOTAL_APP_ROLLBACKS
, TOTAL_APP_ROLLBACKS2
, INT_ROLLBACKS
, INT_ROLLBACKS2
, TOTAL_RUNSTATS
, TOTAL_RUNSTATS2
, TOTAL_REORGS
, TOTAL_REORGS2
, TOTAL_LOADS
, TOTAL_LOADS2
,CF_WAIT_TIME
, CF_WAIT_TIME2
,RECLAIM_WAIT_TIME
, RECLAIM_WAIT_TIME2
,SPACEMAPPAGE_RECLAIM_WAIT_TIME
, SPACEMAPPAGE_RECLAIM_WAIT_TIME2
,POOL_DATA_GBP_L_READS
, POOL_DATA_GBP_L_READS2
,POOL_DATA_GBP_P_READS
, POOL_DATA_GBP_P_READS2
,POOL_INDEX_GBP_L_READS
, POOL_INDEX_GBP_L_READS2
,POOL_INDEX_GBP_P_READS
, POOL_INDEX_GBP_P_READS2
,POOL_DATA_LBP_PAGES_FOUND
, POOL_DATA_LBP_PAGES_FOUND2
,POOL_INDEX_LBP_PAGES_FOUND
, POOL_INDEX_LBP_PAGES_FOUND2
,POOL_ASYNC_DATA_LBP_PAGES_FOUND
, POOL_ASYNC_DATA_LBP_PAGES_FOUND2
,POOL_ASYNC_INDEX_LBP_PAGES_FOUND
, POOL_ASYNC_INDEX_LBP_PAGES_FOUND2
,POOL_XDA_GBP_L_READS
, POOL_XDA_GBP_L_READS2
,POOL_XDA_GBP_P_READS
, POOL_XDA_GBP_P_READS2
,POOL_XDA_LBP_PAGES_FOUND
, POOL_XDA_LBP_PAGES_FOUND2
,POOL_ASYNC_XDA_LBP_PAGES_FOUND
, POOL_ASYNC_XDA_LBP_PAGES_FOUND2
,POOL_COL_L_READS
, POOL_COL_L_READS2
,POOL_TEMP_COL_L_READS
, POOL_TEMP_COL_L_READS2
,POOL_COL_P_READS
, POOL_COL_P_READS2
,POOL_TEMP_COL_P_READS
, POOL_TEMP_COL_P_READS2
,POOL_COL_LBP_PAGES_FOUND
, POOL_COL_LBP_PAGES_FOUND2
,POOL_COL_WRITES
, POOL_COL_WRITES2
,POOL_COL_GBP_L_READS
, POOL_COL_GBP_L_READS2
,POOL_COL_GBP_P_READS
, POOL_COL_GBP_P_READS2
,POOL_ASYNC_COL_LBP_PAGES_FOUND
, POOL_ASYNC_COL_LBP_PAGES_FOUND2
,POOL_ASYNC_DATA_READS
, POOL_ASYNC_DATA_READS2
,POOL_ASYNC_INDEX_READS
, POOL_ASYNC_INDEX_READS2
,POOL_ASYNC_XDA_READS
, POOL_ASYNC_XDA_READS2
,POOL_ASYNC_COL_READS
, POOL_ASYNC_COL_READS2 BIGINT;
/* this cursor is used to return the report as a result set */
DECLARE RES CURSOR WITH RETURN TO CALLER FOR SELECT R.TEXT FROM UNNEST(REPOUTPUT) AS R(TEXT);
SET LOC = SUBSTR(CURRENT LOCALE LC_MESSAGES
,1
,33);
/* check input argument */
IF (TIMEINTERVAL IS NULL) THEN SET TIMEINTERVAL = 10;
ELSEIF ((TIMEINTERVAL < 0) OR (TIMEINTERVAL > 3600)) THEN CALL SYSIBMINTERNAL.SQLEML_RAISE_ERROR( -171
, '1'
, 'DBSUMMARY'
, 'TIMEINTERVAL' );
END IF;
/* fetch and cache all localized text for building report output */
VALUES (12707
, 49
, 1
, 1) INTO MSGINDEX
, MSGCOUNT
, IC
, IR;
CALL INITMSGCACHE(MSGCACHE
,IC
,MSGINDEX
,MSGCOUNT
,LOC);
VALUES (12491
, 211
, 1) INTO MSGINDEX
, MSGCOUNT
, CR_IC;
CALL INITMSGCACHE(CR_MSGCACHE
,CR_IC
,MSGINDEX
,MSGCOUNT
,LOC);
/* IR is the index in the report output array */
/* IC is the index in the message cache array */
/* ==================================== */
/* collect metrics at start of interval */
/* ==================================== */
SELECT SUM(WLM_QUEUE_TIME_TOTAL) AS WLM_QUEUE_TIME_TOTAL
, SUM(AGENT_WAIT_TIME) AS AGENT_WAIT_TIME
, SUM(TCPIP_RECVS_TOTAL) AS TCPIP_RECVS_TOTAL
, SUM(TCPIP_SENDS_TOTAL) AS TCPIP_SENDS_TOTAL
, SUM(IPC_RECVS_TOTAL) AS IPC_RECVS_TOTAL
, SUM(IPC_SENDS_TOTAL) AS IPC_SENDS_TOTAL
, SUM(TCPIP_SEND_WAIT_TIME) AS TCPIP_SEND_WAIT_TIME
, SUM(TCPIP_RECV_WAIT_TIME) AS TCPIP_RECV_WAIT_TIME
, SUM(IPC_SEND_WAIT_TIME) AS IPC_SEND_WAIT_TIME
, SUM(IPC_RECV_WAIT_TIME) AS IPC_RECV_WAIT_TIME
, SUM(TCPIP_RECV_VOLUME) AS TCPIP_RECV_VOLUME
, SUM(IPC_RECV_VOLUME) AS IPC_RECV_VOLUME
, SUM(TCPIP_SEND_VOLUME) AS TCPIP_SEND_VOLUME
, SUM(IPC_SEND_VOLUME) AS IPC_SEND_VOLUME
, SUM(LOCK_ESCALS) AS LOCK_ESCALS
, SUM(LOCK_WAITS) AS LOCK_WAITS
, SUM(LOCK_TIMEOUTS) AS LOCK_TIMEOUTS
, SUM(DEADLOCKS) AS DEADLOCKS
, SUM(ACT_COMPLETED_TOTAL) AS ACT_COMPLETED_TOTAL
, SUM(ACT_ABORTED_TOTAL) AS ACT_ABORTED_TOTAL
, SUM(ACT_REJECTED_TOTAL) AS ACT_REJECTED_TOTAL
, SUM(APP_RQSTS_COMPLETED_TOTAL) AS APP_RQSTS_COMPLETED_TOTAL
, SUM(TOTAL_RQST_TIME) AS TOTAL_RQST_TIME
, SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME
, SUM(TOTAL_WAIT_TIME) AS TOTAL_WAIT_TIME
, SUM(TOTAL_ACT_TIME) AS TOTAL_ACT_TIME
, SUM(TOTAL_ACT_WAIT_TIME) AS TOTAL_ACT_WAIT_TIME
, SUM(ROWS_READ) AS ROWS_READ
, SUM(ROWS_RETURNED) AS ROWS_RETURNED
, SUM(ROWS_MODIFIED) AS ROWS_MODIFIED
, SUM(DIRECT_READ_TIME) AS DIRECT_READ_TIME
, SUM(DIRECT_READS) AS DIRECT_READS
, SUM(DIRECT_READ_REQS) AS DIRECT_READ_REQS
, SUM(DIRECT_WRITE_TIME) AS DIRECT_WRITE_TIME
, SUM(DIRECT_WRITES) AS DIRECT_WRITES
, SUM(DIRECT_WRITE_REQS) AS DIRECT_WRITE_REQS
, SUM(LOG_DISK_WAIT_TIME) AS LOG_DISK_WAIT_TIME
, SUM(LOG_DISK_WAITS_TOTAL) AS LOG_DISK_WAITS_TOTAL
, SUM(NUM_LOG_BUFFER_FULL) AS NUM_LOG_BUFFER_FULL
, SUM(CLIENT_IDLE_WAIT_TIME) AS CLIENT_IDLE_WAIT_TIME
, SUM(LOCK_WAIT_TIME) AS LOCK_WAIT_TIME
, SUM(FCM_SEND_WAIT_TIME) AS FCM_SEND_WAIT_TIME
, SUM(TOTAL_SORTS) AS TOTAL_SORTS
, SUM(SORT_OVERFLOWS) AS SORT_OVERFLOWS
, SUM(POST_THRESHOLD_SORTS) AS POST_THRESHOLD_SORTS
, SUM(POST_SHRTHRESHOLD_SORTS) AS POST_SHRTHRESHOLD_SORTS
, SUM(FCM_SEND_VOLUME) AS FCM_SEND_VOLUME
, SUM(FCM_SENDS_TOTAL) AS FCM_SENDS_TOTAL
, SUM(FCM_RECV_VOLUME) AS FCM_RECV_VOLUME
, SUM(FCM_RECVS_TOTAL) AS FCM_RECVS_TOTAL
, SUM(WLM_QUEUE_ASSIGNMENTS_TOTAL) AS WLM_QUEUE_ASSIGNMENTS_TOTAL
, SUM(FCM_RECV_WAIT_TIME) AS FCM_RECV_WAIT_TIME
, SUM(TOTAL_SECTION_PROC_TIME) AS TOTAL_SECTION_PROC_TIME
, SUM(TOTAL_SECTION_SORT_PROC_TIME) AS TOTAL_SECTION_SORT_PROC_TIME
, SUM(TOTAL_COMPILE_PROC_TIME) AS TOTAL_COMPILE_PROC_TIME
, SUM(TOTAL_IMPLICIT_COMPILE_PROC_TIME) AS TOTAL_IMPLICIT_COMPILE_PROC_TIME
, SUM(TOTAL_COMMIT_PROC_TIME) AS TOTAL_COMMIT_PROC_TIME
, SUM(TOTAL_ROLLBACK_PROC_TIME) AS TOTAL_ROLLBACK_PROC_TIME
, SUM(TOTAL_RUNSTATS_PROC_TIME) AS TOTAL_RUNSTATS_PROC_TIME
, SUM(TOTAL_REORG_PROC_TIME) AS TOTAL_REORG_PROC_TIME
, SUM(TOTAL_LOAD_PROC_TIME) AS TOTAL_LOAD_PROC_TIME
, SUM(TOTAL_APP_COMMITS) AS TOTAL_APP_COMMITS
, SUM(TOTAL_ROUTINE_INVOCATIONS) AS TOTAL_ROUTINE_INVOCATIONS
, SUM(TOTAL_ROUTINE_TIME) AS TOTAL_ROUTINE_TIME
, SUM(TOTAL_COMPILATIONS) AS TOTAL_COMPILATIONS
, SUM(PKG_CACHE_INSERTS) AS PKG_CACHE_INSERTS
, SUM(PKG_CACHE_LOOKUPS) AS PKG_CACHE_LOOKUPS
, SUM(CAT_CACHE_INSERTS) AS CAT_CACHE_INSERTS
, SUM(CAT_CACHE_LOOKUPS) AS CAT_CACHE_LOOKUPS
, SUM(INT_COMMITS) AS INT_COMMITS
, SUM(TOTAL_APP_ROLLBACKS) AS TOTAL_APP_ROLLBACKS
, SUM(INT_ROLLBACKS) AS INT_ROLLBACKS
, SUM(TOTAL_RUNSTATS) AS TOTAL_RUNSTATS
, SUM(TOTAL_REORGS) AS TOTAL_REORGS
, SUM(TOTAL_LOADS) AS TOTAL_LOADS
,SUM(CF_WAIT_TIME) AS CF_WAIT_TIME
,SUM(RECLAIM_WAIT_TIME) AS RECLAIM_WAIT_TIME
,SUM(SPACEMAPPAGE_RECLAIM_WAIT_TIME) AS SPACEMAPPAGE_RECLAIM_WAIT_TIME
,SUM(POOL_ASYNC_DATA_LBP_PAGES_FOUND) AS POOL_ASYNC_DATA_LBP_PAGES_FOUND
,SUM(POOL_ASYNC_INDEX_LBP_PAGES_FOUND) AS POOL_ASYNC_INDEX_LBP_PAGES_FOUND
,SUM(POOL_ASYNC_XDA_LBP_PAGES_FOUND) AS POOL_ASYNC_XDA_LBP_PAGES_FOUND
,SUM(POOL_DATA_L_READS) AS POOL_DATA_L_READS
,SUM(POOL_COL_L_READS) AS POOL_COL_L_READS
,SUM(POOL_TEMP_COL_L_READS) AS POOL_COL_L_READS
,SUM(POOL_COL_P_READS) AS POOL_COL_P_READS
,SUM(POOL_TEMP_COL_P_READS) AS POOL_TEMP_COL_P_READS
,SUM(POOL_COL_LBP_PAGES_FOUND) AS POOL_COL_LBP_PAGES_FOUND
,SUM(POOL_COL_WRITES) AS POOL_COL_WRITES
,SUM(POOL_COL_GBP_L_READS) AS POOL_COL_GBP_L_READS
,SUM(POOL_COL_GBP_P_READS) AS POOL_COL_GBP_P_READS
,SUM(POOL_ASYNC_COL_LBP_PAGES_FOUND) AS POOL_ASYNC_COL_LBP_PAGES_FOUND
, SUM(POOL_INDEX_L_READS) AS POOL_INDEX_L_READS
, SUM(POOL_TEMP_DATA_L_READS) AS POOL_TEMP_DATA_L_READS
, SUM(POOL_TEMP_INDEX_L_READS) AS POOL_TEMP_INDEX_L_READS
, SUM(POOL_TEMP_XDA_L_READS) AS POOL_TEMP_XDA_L_READS
, SUM(POOL_XDA_L_READS) AS POOL_XDA_L_READS
, SUM(POOL_DATA_P_READS) AS POOL_DATA_P_READS
, SUM(POOL_INDEX_P_READS) AS POOL_INDEX_P_READS
, SUM(POOL_TEMP_DATA_P_READS) AS POOL_TEMP_DATA_P_READS
, SUM(POOL_TEMP_INDEX_P_READS) AS POOL_TEMP_INDEX_P_READS
, SUM(POOL_TEMP_XDA_P_READS) AS POOL_TEMP_XDA_P_READS
, SUM(POOL_XDA_P_READS) AS POOL_XDA_P_READS
, SUM(POOL_READ_TIME) AS POOL_READ_TIME
, SUM(POOL_ASYNC_READ_TIME) AS POOL_ASYNC_READ_TIME
, SUM(POOL_WRITE_TIME) AS POOL_WRITE_TIME
, SUM(POOL_ASYNC_WRITE_TIME) AS POOL_ASYNC_WRITE_TIME
, SUM(POOL_DATA_WRITES) AS POOL_DATA_WRITES
, SUM(POOL_INDEX_WRITES) AS POOL_INDEX_WRITES
, SUM(POOL_XDA_WRITES) AS POOL_XDA_WRITES
, SUM(POOL_DATA_GBP_L_READS) AS POOL_DATA_GBP_L_READS
,SUM(POOL_DATA_GBP_P_READS) AS POOL_DATA_GBP_P_READS
,SUM(POOL_INDEX_GBP_L_READS) AS POOL_INDEX_GBP_L_READS
,SUM(POOL_INDEX_GBP_P_READS) AS POOL_INDEX_GBP_P_READS
,SUM(POOL_DATA_LBP_PAGES_FOUND) AS POOL_DATA_LBP_PAGES_FOUND
,SUM(POOL_INDEX_LBP_PAGES_FOUND) AS POOL_INDEX_LBP_PAGES_FOUND
,SUM(POOL_XDA_GBP_L_READS) AS POOL_XDA_GBP_L_READS
,SUM(POOL_XDA_GBP_P_READS) AS POOL_XDA_GBP_P_READS
,SUM(POOL_XDA_LBP_PAGES_FOUND) AS POOL_XDA_LBP_PAGES_FOUND
,SUM(POOL_ASYNC_DATA_READS) AS POOL_ASYNC_DATA_READS
,SUM(POOL_ASYNC_INDEX_READS) AS POOL_ASYNC_INDEX_READS
,SUM(POOL_ASYNC_XDA_READS) AS POOL_ASYNC_XDA_READS
,SUM(POOL_ASYNC_COL_READS) AS POOL_ASYNC_COL_READS INTO WLM_QUEUE_TIME_TOTAL
, AGENT_WAIT_TIME
, TCPIP_RECVS_TOTAL
, TCPIP_SENDS_TOTAL
, IPC_RECVS_TOTAL
, IPC_SENDS_TOTAL
, TCPIP_SEND_WAIT_TIME
, TCPIP_RECV_WAIT_TIME
, IPC_SEND_WAIT_TIME
, IPC_RECV_WAIT_TIME
, TCPIP_RECV_VOLUME
, IPC_RECV_VOLUME
, TCPIP_SEND_VOLUME
, IPC_SEND_VOLUME
, LOCK_ESCALS
, LOCK_WAITS
, LOCK_TIMEOUTS
, DEADLOCKS
, ACT_COMPLETED_TOTAL
, ACT_ABORTED_TOTAL
, ACT_REJECTED_TOTAL
, APP_RQSTS_COMPLETED_TOTAL
, TOTAL_RQST_TIME
, TOTAL_CPU_TIME
, TOTAL_WAIT_TIME
, TOTAL_ACT_TIME
, TOTAL_ACT_WAIT_TIME
, ROWS_READ
, ROWS_RETURNED
, ROWS_MODIFIED
, DIRECT_READ_TIME
, DIRECT_READS
, DIRECT_READ_REQS
, DIRECT_WRITE_TIME
, DIRECT_WRITES
, DIRECT_WRITE_REQS
, LOG_DISK_WAIT_TIME
, LOG_DISK_WAITS_TOTAL
, NUM_LOG_BUFFER_FULL
, CLIENT_IDLE_WAIT_TIME
, LOCK_WAIT_TIME
, FCM_SEND_WAIT_TIME
, TOTAL_SORTS
, SORT_OVERFLOWS
, POST_THRESHOLD_SORTS
, POST_SHRTHRESHOLD_SORTS
, FCM_SEND_VOLUME
, FCM_SENDS_TOTAL
, FCM_RECV_VOLUME
, FCM_RECVS_TOTAL
, WLM_QUEUE_ASSIGNMENTS_TOTAL
, FCM_RECV_WAIT_TIME
, TOTAL_SECTION_PROC_TIME
, TOTAL_SECTION_SORT_PROC_TIME
, TOTAL_COMPILE_PROC_TIME
, TOTAL_IMPLICIT_COMPILE_PROC_TIME
, TOTAL_COMMIT_PROC_TIME
, TOTAL_ROLLBACK_PROC_TIME
, TOTAL_RUNSTATS_PROC_TIME
, TOTAL_REORG_PROC_TIME
, TOTAL_LOAD_PROC_TIME
, TOTAL_APP_COMMITS
, TOTAL_ROUTINE_INVOCATIONS
, TOTAL_ROUTINE_TIME
, TOTAL_COMPILATIONS
, PKG_CACHE_INSERTS
, PKG_CACHE_LOOKUPS
, CAT_CACHE_INSERTS
, CAT_CACHE_LOOKUPS
, INT_COMMITS
, TOTAL_APP_ROLLBACKS
, INT_ROLLBACKS
, TOTAL_RUNSTATS
, TOTAL_REORGS
, TOTAL_LOADS
,CF_WAIT_TIME
,RECLAIM_WAIT_TIME
,SPACEMAPPAGE_RECLAIM_WAIT_TIME
,POOL_ASYNC_DATA_LBP_PAGES_FOUND
,POOL_ASYNC_INDEX_LBP_PAGES_FOUND
,POOL_ASYNC_XDA_LBP_PAGES_FOUND
,POOL_DATA_L_READS
,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_COL_GBP_L_READS
,POOL_COL_GBP_P_READS
,POOL_ASYNC_COL_LBP_PAGES_FOUND
, 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_READ_TIME
, POOL_ASYNC_READ_TIME
, POOL_WRITE_TIME
, POOL_ASYNC_WRITE_TIME
, POOL_DATA_WRITES
, POOL_INDEX_WRITES
, POOL_XDA_WRITES
, POOL_DATA_GBP_L_READS
,POOL_DATA_GBP_P_READS
,POOL_INDEX_GBP_L_READS
,POOL_INDEX_GBP_P_READS
,POOL_DATA_LBP_PAGES_FOUND
,POOL_INDEX_LBP_PAGES_FOUND
,POOL_XDA_GBP_L_READS
,POOL_XDA_GBP_P_READS
,POOL_XDA_LBP_PAGES_FOUND
,POOL_ASYNC_DATA_READS
,POOL_ASYNC_INDEX_READS
,POOL_ASYNC_XDA_READS
,POOL_ASYNC_COL_READS FROM TABLE(MON_GET_DATABASE(-2));
/* key metrics by service connection */
SELECT ARRAY_AGG(APPLICATION_HANDLE ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(TOTAL_APP_COMMITS ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(APP_RQSTS_COMPLETED_TOTAL ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(TOTAL_RQST_TIME ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(TOTAL_WAIT_TIME ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(ROWS_READ ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(ROWS_MODIFIED ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(TOTAL_CPU_TIME ORDER BY APPLICATION_HANDLE) INTO CN_APPLICATION_HANDLE_ARRAY
, CN_TOTAL_APP_COMMITS_ARRAY
, CN_APP_RQSTS_COMPLETED_TOTAL_ARRAY
, CN_TOTAL_RQST_TIME_ARRAY
, CN_TOTAL_WAIT_TIME_ARRAY
, CN_ROWS_READ_ARRAY
, CN_ROWS_MODIFIED_ARRAY
, CN_TOTAL_CPU_TIME_ARRAY FROM (SELECT APPLICATION_HANDLE
, SUM(TOTAL_APP_COMMITS) AS TOTAL_APP_COMMITS
, SUM(APP_RQSTS_COMPLETED_TOTAL) AS APP_RQSTS_COMPLETED_TOTAL
, SUM(TOTAL_RQST_TIME) AS TOTAL_RQST_TIME
, SUM(TOTAL_WAIT_TIME) AS TOTAL_WAIT_TIME
, SUM(ROWS_READ) AS ROWS_READ
, SUM(ROWS_MODIFIED) AS ROWS_MODIFIED
, SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME FROM TABLE(MON_GET_CONNECTION(NULL
, -2)) GROUP BY APPLICATION_HANDLE);
/* key metrics by service class */
SELECT ARRAY_AGG(SERVICE_CLASS_ID ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(TOTAL_APP_COMMITS ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(APP_RQSTS_COMPLETED_TOTAL ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(TOTAL_RQST_TIME ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(TOTAL_WAIT_TIME ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(ROWS_READ ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(ROWS_MODIFIED ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(TOTAL_CPU_TIME ORDER BY SERVICE_CLASS_ID) INTO SC_SERVICE_CLASS_ID_ARRAY
, SC_TOTAL_APP_COMMITS_ARRAY
, SC_APP_RQSTS_COMPLETED_TOTAL_ARRAY
, SC_TOTAL_RQST_TIME_ARRAY
, SC_TOTAL_WAIT_TIME_ARRAY
, SC_ROWS_READ_ARRAY
, SC_ROWS_MODIFIED_ARRAY
, SC_TOTAL_CPU_TIME_ARRAY FROM (SELECT SERVICE_CLASS_ID
, SUM(TOTAL_APP_COMMITS) AS TOTAL_APP_COMMITS
, SUM(APP_RQSTS_COMPLETED_TOTAL) AS APP_RQSTS_COMPLETED_TOTAL
, SUM(TOTAL_RQST_TIME) AS TOTAL_RQST_TIME
, SUM(TOTAL_WAIT_TIME) AS TOTAL_WAIT_TIME
, SUM(ROWS_READ) AS ROWS_READ
, SUM(ROWS_MODIFIED) AS ROWS_MODIFIED
, SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME FROM TABLE(MON_GET_SERVICE_SUBCLASS(NULL
, NULL
, -2)) GROUP BY SERVICE_CLASS_ID);
/* key metrics by workload */
SELECT ARRAY_AGG(WORKLOAD_NAME ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(TOTAL_APP_COMMITS ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(APP_RQSTS_COMPLETED_TOTAL ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(TOTAL_RQST_TIME ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(TOTAL_WAIT_TIME ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(ROWS_READ ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(ROWS_MODIFIED ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(TOTAL_CPU_TIME ORDER BY WORKLOAD_NAME) INTO WL_WORKLOAD_NAME_ARRAY
, WL_TOTAL_APP_COMMITS_ARRAY
, WL_APP_RQSTS_COMPLETED_TOTAL_ARRAY
, WL_TOTAL_RQST_TIME_ARRAY
, WL_TOTAL_WAIT_TIME_ARRAY
, WL_ROWS_READ_ARRAY
, WL_ROWS_MODIFIED_ARRAY
, WL_TOTAL_CPU_TIME_ARRAY FROM (SELECT WORKLOAD_NAME
, SUM(TOTAL_APP_COMMITS) AS TOTAL_APP_COMMITS
, SUM(APP_RQSTS_COMPLETED_TOTAL) AS APP_RQSTS_COMPLETED_TOTAL
, SUM(TOTAL_RQST_TIME) AS TOTAL_RQST_TIME
, SUM(TOTAL_WAIT_TIME) AS TOTAL_WAIT_TIME
, SUM(ROWS_READ) AS ROWS_READ
, SUM(ROWS_MODIFIED) AS ROWS_MODIFIED
, SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME FROM TABLE(MON_GET_WORKLOAD(NULL
, -2)) GROUP BY WORKLOAD_NAME);
/* key metrics by member */
SELECT ARRAY_AGG(MEMBER ORDER BY MEMBER)
, ARRAY_AGG(RQSTS_COMPLETED_TOTAL ORDER BY MEMBER)
, ARRAY_AGG(TOTAL_RQST_TIME ORDER BY MEMBER)
, ARRAY_AGG(TOTAL_WAIT_TIME ORDER BY MEMBER)
, ARRAY_AGG(IO_WAIT_TIME ORDER BY MEMBER)
, ARRAY_AGG(TOTAL_CPU_TIME ORDER BY MEMBER) INTO PM_MEMBER_ARRAY
, PM_RQSTS_COMPLETED_TOTAL_ARRAY
, PM_TOTAL_RQST_TIME_ARRAY
, PM_TOTAL_WAIT_TIME_ARRAY
, PM_IO_WAIT_TIME_ARRAY
, PM_TOTAL_CPU_TIME_ARRAY FROM (SELECT MEMBER
, SUM(RQSTS_COMPLETED_TOTAL) AS RQSTS_COMPLETED_TOTAL
, SUM(TOTAL_RQST_TIME) AS TOTAL_RQST_TIME
, SUM(TOTAL_WAIT_TIME) AS TOTAL_WAIT_TIME
, SUM(POOL_READ_TIME + POOL_WRITE_TIME + DIRECT_READ_TIME + DIRECT_WRITE_TIME) AS IO_WAIT_TIME
, SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME FROM TABLE(MON_GET_SERVICE_SUBCLASS(NULL
, NULL
, -2)) GROUP BY MEMBER);
/* ================================== */
/* collect metrics at end of interval */
/* ================================== */
IF (TIMEINTERVAL > 0) THEN CALL DBMS_ALERT.SLEEP(TIMEINTERVAL);
SELECT SUM(WLM_QUEUE_TIME_TOTAL) AS WLM_QUEUE_TIME_TOTAL
, SUM(AGENT_WAIT_TIME) AS AGENT_WAIT_TIME
, SUM(TCPIP_RECVS_TOTAL) AS TCPIP_RECVS_TOTAL
, SUM(TCPIP_SENDS_TOTAL) AS TCPIP_SENDS_TOTAL
, SUM(IPC_RECVS_TOTAL) AS IPC_RECVS_TOTAL
, SUM(IPC_SENDS_TOTAL) AS IPC_SENDS_TOTAL
, SUM(TCPIP_SEND_WAIT_TIME) AS TCPIP_SEND_WAIT_TIME
, SUM(TCPIP_RECV_WAIT_TIME) AS TCPIP_RECV_WAIT_TIME
, SUM(IPC_SEND_WAIT_TIME) AS IPC_SEND_WAIT_TIME
, SUM(IPC_RECV_WAIT_TIME) AS IPC_RECV_WAIT_TIME
, SUM(TCPIP_RECV_VOLUME) AS TCPIP_RECV_VOLUME
, SUM(IPC_RECV_VOLUME) AS IPC_RECV_VOLUME
, SUM(TCPIP_SEND_VOLUME) AS TCPIP_SEND_VOLUME
, SUM(IPC_SEND_VOLUME) AS IPC_SEND_VOLUME
, SUM(LOCK_ESCALS) AS LOCK_ESCALS
, SUM(LOCK_WAITS) AS LOCK_WAITS
, SUM(LOCK_TIMEOUTS) AS LOCK_TIMEOUTS
, SUM(DEADLOCKS) AS DEADLOCKS
, SUM(ACT_COMPLETED_TOTAL) AS ACT_COMPLETED_TOTAL
, SUM(ACT_ABORTED_TOTAL) AS ACT_ABORTED_TOTAL
, SUM(ACT_REJECTED_TOTAL) AS ACT_REJECTED_TOTAL
, SUM(APP_RQSTS_COMPLETED_TOTAL) AS APP_RQSTS_COMPLETED_TOTAL
, SUM(TOTAL_RQST_TIME) AS TOTAL_RQST_TIME
, SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME
, SUM(TOTAL_WAIT_TIME) AS TOTAL_WAIT_TIME
, SUM(TOTAL_ACT_TIME) AS TOTAL_ACT_TIME
, SUM(TOTAL_ACT_WAIT_TIME) AS TOTAL_ACT_WAIT_TIME
, SUM(ROWS_READ) AS ROWS_READ
, SUM(ROWS_RETURNED) AS ROWS_RETURNED
, SUM(ROWS_MODIFIED) AS ROWS_MODIFIED
, SUM(DIRECT_READ_TIME) AS DIRECT_READ_TIME
, SUM(DIRECT_READS) AS DIRECT_READS
, SUM(DIRECT_READ_REQS) AS DIRECT_READ_REQS
, SUM(DIRECT_WRITE_TIME) AS DIRECT_WRITE_TIME
, SUM(DIRECT_WRITES) AS DIRECT_WRITES
, SUM(DIRECT_WRITE_REQS) AS DIRECT_WRITE_REQS
, SUM(LOG_DISK_WAIT_TIME) AS LOG_DISK_WAIT_TIME
, SUM(LOG_DISK_WAITS_TOTAL) AS LOG_DISK_WAITS_TOTAL
, SUM(NUM_LOG_BUFFER_FULL) AS NUM_LOG_BUFFER_FULL
, SUM(CLIENT_IDLE_WAIT_TIME) AS CLIENT_IDLE_WAIT_TIME
, SUM(LOCK_WAIT_TIME) AS LOCK_WAIT_TIME
, SUM(FCM_SEND_WAIT_TIME) AS FCM_SEND_WAIT_TIME
, SUM(TOTAL_SORTS) AS TOTAL_SORTS
, SUM(SORT_OVERFLOWS) AS SORT_OVERFLOWS
, SUM(POST_THRESHOLD_SORTS) AS POST_THRESHOLD_SORTS
, SUM(POST_SHRTHRESHOLD_SORTS) AS POST_SHRTHRESHOLD_SORTS
, SUM(FCM_SEND_VOLUME) AS FCM_SEND_VOLUME
, SUM(FCM_SENDS_TOTAL) AS FCM_SENDS_TOTAL
, SUM(FCM_RECV_VOLUME) AS FCM_RECV_VOLUME
, SUM(FCM_RECVS_TOTAL) AS FCM_RECVS_TOTAL
, SUM(WLM_QUEUE_ASSIGNMENTS_TOTAL) AS WLM_QUEUE_ASSIGNMENTS_TOTAL
, SUM(FCM_RECV_WAIT_TIME) AS FCM_RECV_WAIT_TIME
, SUM(TOTAL_SECTION_PROC_TIME) AS TOTAL_SECTION_PROC_TIME
, SUM(TOTAL_SECTION_SORT_PROC_TIME) AS TOTAL_SECTION_SORT_PROC_TIME
, SUM(TOTAL_COMPILE_PROC_TIME) AS TOTAL_COMPILE_PROC_TIME
, SUM(TOTAL_IMPLICIT_COMPILE_PROC_TIME) AS TOTAL_IMPLICIT_COMPILE_PROC_TIME
, SUM(TOTAL_COMMIT_PROC_TIME) AS TOTAL_COMMIT_PROC_TIME
, SUM(TOTAL_ROLLBACK_PROC_TIME) AS TOTAL_ROLLBACK_PROC_TIME
, SUM(TOTAL_RUNSTATS_PROC_TIME) AS TOTAL_RUNSTATS_PROC_TIME
, SUM(TOTAL_REORG_PROC_TIME) AS TOTAL_REORG_PROC_TIME
, SUM(TOTAL_LOAD_PROC_TIME) AS TOTAL_LOAD_PROC_TIME
, SUM(TOTAL_APP_COMMITS) AS TOTAL_APP_COMMITS
, SUM(TOTAL_ROUTINE_INVOCATIONS) AS TOTAL_ROUTINE_INVOCATIONS
, SUM(TOTAL_ROUTINE_TIME) AS TOTAL_ROUTINE_TIME
, SUM(TOTAL_COMPILATIONS) AS TOTAL_COMPILATIONS
, SUM(PKG_CACHE_INSERTS) AS PKG_CACHE_INSERTS
, SUM(PKG_CACHE_LOOKUPS) AS PKG_CACHE_LOOKUPS
, SUM(CAT_CACHE_INSERTS) AS CAT_CACHE_INSERTS
, SUM(CAT_CACHE_LOOKUPS) AS CAT_CACHE_LOOKUPS
, SUM(INT_COMMITS) AS INT_COMMITS
, SUM(TOTAL_APP_ROLLBACKS) AS TOTAL_APP_ROLLBACKS
, SUM(INT_ROLLBACKS) AS INT_ROLLBACKS
, SUM(TOTAL_RUNSTATS) AS TOTAL_RUNSTATS
, SUM(TOTAL_REORGS) AS TOTAL_REORGS
, SUM(TOTAL_LOADS) AS TOTAL_LOADS
,SUM(CF_WAIT_TIME) AS CF_WAIT_TIME
,SUM(RECLAIM_WAIT_TIME) AS RECLAIM_WAIT_TIME
,SUM(SPACEMAPPAGE_RECLAIM_WAIT_TIME) AS SPACEMAPPAGE_RECLAIM_WAIT_TIME
,SUM(POOL_ASYNC_DATA_LBP_PAGES_FOUND) AS POOL_ASYNC_DATA_LBP_PAGES_FOUND
,SUM(POOL_ASYNC_INDEX_LBP_PAGES_FOUND) AS POOL_ASYNC_INDEX_LBP_PAGES_FOUND
,SUM(POOL_ASYNC_XDA_LBP_PAGES_FOUND) AS POOL_ASYNC_XDA_LBP_PAGES_FOUND
,SUM(POOL_DATA_L_READS) AS POOL_DATA_L_READS
, SUM(POOL_INDEX_L_READS) AS POOL_INDEX_L_READS
, SUM(POOL_TEMP_DATA_L_READS) AS POOL_TEMP_DATA_L_READS
, SUM(POOL_TEMP_INDEX_L_READS) AS POOL_TEMP_INDEX_L_READS
, SUM(POOL_TEMP_XDA_L_READS) AS POOL_TEMP_XDA_L_READS
, SUM(POOL_XDA_L_READS) AS POOL_XDA_L_READS
, SUM(POOL_DATA_P_READS) AS POOL_DATA_P_READS
, SUM(POOL_INDEX_P_READS) AS POOL_INDEX_P_READS
, SUM(POOL_TEMP_DATA_P_READS) AS POOL_TEMP_DATA_P_READS
, SUM(POOL_TEMP_INDEX_P_READS) AS POOL_TEMP_INDEX_P_READS
, SUM(POOL_TEMP_XDA_P_READS) AS POOL_TEMP_XDA_P_READS
, SUM(POOL_XDA_P_READS) AS POOL_XDA_P_READS
, SUM(POOL_READ_TIME) AS POOL_READ_TIME
, SUM(POOL_ASYNC_READ_TIME) AS POOL_ASYNC_READ_TIME
, SUM(POOL_WRITE_TIME) AS POOL_WRITE_TIME
, SUM(POOL_ASYNC_WRITE_TIME) AS POOL_ASYNC_WRITE_TIME
, SUM(POOL_DATA_WRITES) AS POOL_DATA_WRITES
, SUM(POOL_INDEX_WRITES) AS POOL_INDEX_WRITES
, SUM(POOL_XDA_WRITES) AS POOL_XDA_WRITES
,SUM(POOL_COL_L_READS) AS POOL_COL_L_READS
,SUM(POOL_TEMP_COL_L_READS) AS POOL_TEMP_COL_L_READS
,SUM(POOL_COL_P_READS) AS POOL_COL_P_READS
,SUM(POOL_TEMP_COL_P_READS) AS POOL_TEMP_COL_P_READS
,SUM(POOL_COL_LBP_PAGES_FOUND) AS POOL_COL_LBP_PAGES_FOUND
,SUM(POOL_COL_WRITES) AS POOL_COL_WRITES
,SUM(POOL_COL_GBP_L_READS) AS POOL_COL_GBP_L_READS
,SUM(POOL_COL_GBP_P_READS) AS POOL_COL_GBP_P_READS
,SUM(POOL_ASYNC_COL_LBP_PAGES_FOUND) AS POOL_ASYNC_COL_LBP_PAGES_FOUND
,SUM(POOL_DATA_GBP_L_READS) AS POOL_DATA_GBP_L_READS
,SUM(POOL_DATA_GBP_P_READS) AS POOL_DATA_GBP_P_READS
,SUM(POOL_INDEX_GBP_L_READS) AS POOL_INDEX_GBP_L_READS
,SUM(POOL_INDEX_GBP_P_READS) AS POOL_INDEX_GBP_P_READS
,SUM(POOL_DATA_LBP_PAGES_FOUND) AS POOL_DATA_LBP_PAGES_FOUND
,SUM(POOL_INDEX_LBP_PAGES_FOUND) AS POOL_INDEX_LBP_PAGES_FOUND
,SUM(POOL_XDA_GBP_L_READS) AS POOL_XDA_GBP_L_READS
,SUM(POOL_XDA_GBP_P_READS) AS POOL_XDA_GBP_P_READS
,SUM(POOL_XDA_LBP_PAGES_FOUND) AS POOL_XDA_LBP_PAGES_FOUND
,SUM(POOL_ASYNC_DATA_READS) AS POOL_ASYNC_DATA_READS
,SUM(POOL_ASYNC_INDEX_READS) AS POOL_ASYNC_INDEX_READS
,SUM(POOL_ASYNC_XDA_READS) AS POOL_ASYNC_XDA_READS
,SUM(POOL_ASYNC_COL_READS) AS POOL_ASYNC_COL_READS INTO WLM_QUEUE_TIME_TOTAL2
, AGENT_WAIT_TIME2
, TCPIP_RECVS_TOTAL2
, TCPIP_SENDS_TOTAL2
, IPC_RECVS_TOTAL2
, IPC_SENDS_TOTAL2
, TCPIP_SEND_WAIT_TIME2
, TCPIP_RECV_WAIT_TIME2
, IPC_SEND_WAIT_TIME2
, IPC_RECV_WAIT_TIME2
, TCPIP_RECV_VOLUME2
, IPC_RECV_VOLUME2
, TCPIP_SEND_VOLUME2
, IPC_SEND_VOLUME2
, LOCK_ESCALS2
, LOCK_WAITS2
, LOCK_TIMEOUTS2
, DEADLOCKS2
, ACT_COMPLETED_TOTAL2
, ACT_ABORTED_TOTAL2
, ACT_REJECTED_TOTAL2
, APP_RQSTS_COMPLETED_TOTAL2
, TOTAL_RQST_TIME2
, TOTAL_CPU_TIME2
, TOTAL_WAIT_TIME2
, TOTAL_ACT_TIME2
, TOTAL_ACT_WAIT_TIME2
, ROWS_READ2
, ROWS_RETURNED2
, ROWS_MODIFIED2
, DIRECT_READ_TIME2
, DIRECT_READS2
, DIRECT_READ_REQS2
, DIRECT_WRITE_TIME2
, DIRECT_WRITES2
, DIRECT_WRITE_REQS2
, LOG_DISK_WAIT_TIME2
, LOG_DISK_WAITS_TOTAL2
, NUM_LOG_BUFFER_FULL2
, CLIENT_IDLE_WAIT_TIME2
, LOCK_WAIT_TIME2
, FCM_SEND_WAIT_TIME2
, TOTAL_SORTS2
, SORT_OVERFLOWS2
, POST_THRESHOLD_SORTS2
, POST_SHRTHRESHOLD_SORTS2
, FCM_SEND_VOLUME2
, FCM_SENDS_TOTAL2
, FCM_RECV_VOLUME2
, FCM_RECVS_TOTAL2
, WLM_QUEUE_ASSIGNMENTS_TOTAL2
, FCM_RECV_WAIT_TIME2
, TOTAL_SECTION_PROC_TIME2
, TOTAL_SECTION_SORT_PROC_TIME2
, TOTAL_COMPILE_PROC_TIME2
, TOTAL_IMPLICIT_COMPILE_PROC_TIME2
, TOTAL_COMMIT_PROC_TIME2
, TOTAL_ROLLBACK_PROC_TIME2
, TOTAL_RUNSTATS_PROC_TIME2
, TOTAL_REORG_PROC_TIME2
, TOTAL_LOAD_PROC_TIME2
, TOTAL_APP_COMMITS2
, TOTAL_ROUTINE_INVOCATIONS2
, TOTAL_ROUTINE_TIME2
, TOTAL_COMPILATIONS2
, PKG_CACHE_INSERTS2
, PKG_CACHE_LOOKUPS2
, CAT_CACHE_INSERTS2
, CAT_CACHE_LOOKUPS2
, INT_COMMITS2
, TOTAL_APP_ROLLBACKS2
, INT_ROLLBACKS2
, TOTAL_RUNSTATS2
, TOTAL_REORGS2
, TOTAL_LOADS2
,CF_WAIT_TIME2
,RECLAIM_WAIT_TIME2
,SPACEMAPPAGE_RECLAIM_WAIT_TIME2
,POOL_ASYNC_DATA_LBP_PAGES_FOUND2
,POOL_ASYNC_INDEX_LBP_PAGES_FOUND2
,POOL_ASYNC_XDA_LBP_PAGES_FOUND2
,POOL_DATA_L_READS2
, POOL_INDEX_L_READS2
, POOL_TEMP_DATA_L_READS2
, POOL_TEMP_INDEX_L_READS2
, POOL_TEMP_XDA_L_READS2
, POOL_XDA_L_READS2
, POOL_DATA_P_READS2
, POOL_INDEX_P_READS2
, POOL_TEMP_DATA_P_READS2
, POOL_TEMP_INDEX_P_READS2
, POOL_TEMP_XDA_P_READS2
, POOL_XDA_P_READS2
, POOL_READ_TIME2
, POOL_ASYNC_READ_TIME2
, POOL_WRITE_TIME2
, POOL_ASYNC_WRITE_TIME2
, POOL_DATA_WRITES2
, POOL_INDEX_WRITES2
, POOL_XDA_WRITES2
,POOL_COL_L_READS2
,POOL_TEMP_COL_L_READS2
,POOL_COL_P_READS2
,POOL_TEMP_COL_P_READS2
,POOL_COL_LBP_PAGES_FOUND2
,POOL_COL_WRITES2
,POOL_COL_GBP_L_READS2
,POOL_COL_GBP_P_READS2
,POOL_ASYNC_COL_LBP_PAGES_FOUND2
, POOL_DATA_GBP_L_READS2
,POOL_DATA_GBP_P_READS2
,POOL_INDEX_GBP_L_READS2
,POOL_INDEX_GBP_P_READS2
,POOL_DATA_LBP_PAGES_FOUND2
,POOL_INDEX_LBP_PAGES_FOUND2
,POOL_XDA_GBP_L_READS2
,POOL_XDA_GBP_P_READS2
,POOL_XDA_LBP_PAGES_FOUND2
,POOL_ASYNC_DATA_READS2
,POOL_ASYNC_INDEX_READS2
,POOL_ASYNC_XDA_READS2
,POOL_ASYNC_COL_READS2 FROM TABLE(MON_GET_DATABASE(-2));
/* key metrics by connection */
SELECT ARRAY_AGG(APPLICATION_HANDLE ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(TOTAL_APP_COMMITS ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(APP_RQSTS_COMPLETED_TOTAL ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(TOTAL_RQST_TIME ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(TOTAL_WAIT_TIME ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(ROWS_READ ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(ROWS_MODIFIED ORDER BY APPLICATION_HANDLE)
, ARRAY_AGG(TOTAL_CPU_TIME ORDER BY APPLICATION_HANDLE) INTO CN_APPLICATION_HANDLE_ARRAY
, CN_TOTAL_APP_COMMITS_ARRAY
, CN_APP_RQSTS_COMPLETED_TOTAL_ARRAY
, CN_TOTAL_RQST_TIME_ARRAY
, CN_TOTAL_WAIT_TIME_ARRAY
, CN_ROWS_READ_ARRAY
, CN_ROWS_MODIFIED_ARRAY
, CN_TOTAL_CPU_TIME_ARRAY FROM (SELECT A.APPLICATION_HANDLE
, B.TOTAL_APP_COMMITS - A.TOTAL_APP_COMMITS AS TOTAL_APP_COMMITS
, B.APP_RQSTS_COMPLETED_TOTAL - A.APP_RQSTS_COMPLETED_TOTAL AS APP_RQSTS_COMPLETED_TOTAL
, B.TOTAL_RQST_TIME - A.TOTAL_RQST_TIME AS TOTAL_RQST_TIME
, B.TOTAL_WAIT_TIME - A.TOTAL_WAIT_TIME AS TOTAL_WAIT_TIME
, B.ROWS_READ - A.ROWS_READ AS ROWS_READ
, B.ROWS_MODIFIED - A.ROWS_MODIFIED AS ROWS_MODIFIED
, B.TOTAL_CPU_TIME - A.TOTAL_CPU_TIME AS TOTAL_CPU_TIME FROM (SELECT APPLICATION_HANDLE
, SUM(TOTAL_APP_COMMITS) AS TOTAL_APP_COMMITS
, SUM(APP_RQSTS_COMPLETED_TOTAL) AS APP_RQSTS_COMPLETED_TOTAL
, SUM(TOTAL_RQST_TIME) AS TOTAL_RQST_TIME
, SUM(TOTAL_WAIT_TIME) AS TOTAL_WAIT_TIME
, SUM(ROWS_READ) AS ROWS_READ
, SUM(ROWS_MODIFIED) AS ROWS_MODIFIED
, SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME FROM TABLE(MON_GET_CONNECTION(NULL
, -2)) GROUP BY APPLICATION_HANDLE ) AS B
, UNNEST (CN_APPLICATION_HANDLE_ARRAY
, CN_TOTAL_APP_COMMITS_ARRAY
, CN_APP_RQSTS_COMPLETED_TOTAL_ARRAY
, CN_TOTAL_RQST_TIME_ARRAY
, CN_TOTAL_WAIT_TIME_ARRAY
, CN_ROWS_READ_ARRAY
, CN_ROWS_MODIFIED_ARRAY
, CN_TOTAL_CPU_TIME_ARRAY ) AS A (APPLICATION_HANDLE
, TOTAL_APP_COMMITS
, APP_RQSTS_COMPLETED_TOTAL
, TOTAL_RQST_TIME
, TOTAL_WAIT_TIME
, ROWS_READ
, ROWS_MODIFIED
, TOTAL_CPU_TIME ) WHERE A.APPLICATION_HANDLE = B.APPLICATION_HANDLE);
/* key metrics by service class */
SELECT ARRAY_AGG(SERVICE_CLASS_ID ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(TOTAL_APP_COMMITS ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(APP_RQSTS_COMPLETED_TOTAL ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(TOTAL_RQST_TIME ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(TOTAL_WAIT_TIME ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(ROWS_READ ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(ROWS_MODIFIED ORDER BY SERVICE_CLASS_ID)
, ARRAY_AGG(TOTAL_CPU_TIME ORDER BY SERVICE_CLASS_ID) INTO SC_SERVICE_CLASS_ID_ARRAY
, SC_TOTAL_APP_COMMITS_ARRAY
, SC_APP_RQSTS_COMPLETED_TOTAL_ARRAY
, SC_TOTAL_RQST_TIME_ARRAY
, SC_TOTAL_WAIT_TIME_ARRAY
, SC_ROWS_READ_ARRAY
, SC_ROWS_MODIFIED_ARRAY
, SC_TOTAL_CPU_TIME_ARRAY FROM (SELECT A.SERVICE_CLASS_ID
, B.TOTAL_APP_COMMITS - A.TOTAL_APP_COMMITS AS TOTAL_APP_COMMITS
, B.APP_RQSTS_COMPLETED_TOTAL - A.APP_RQSTS_COMPLETED_TOTAL AS APP_RQSTS_COMPLETED_TOTAL
, B.TOTAL_RQST_TIME - A.TOTAL_RQST_TIME AS TOTAL_RQST_TIME
, B.TOTAL_WAIT_TIME - A.TOTAL_WAIT_TIME AS TOTAL_WAIT_TIME
, B.ROWS_READ - A.ROWS_READ AS ROWS_READ
, B.ROWS_MODIFIED - A.ROWS_MODIFIED AS ROWS_MODIFIED
, B.TOTAL_CPU_TIME - A.TOTAL_CPU_TIME AS TOTAL_CPU_TIME FROM (SELECT SERVICE_CLASS_ID
, SUM(TOTAL_APP_COMMITS) AS TOTAL_APP_COMMITS
, SUM(APP_RQSTS_COMPLETED_TOTAL) AS APP_RQSTS_COMPLETED_TOTAL
, SUM(TOTAL_RQST_TIME) AS TOTAL_RQST_TIME
, SUM(TOTAL_WAIT_TIME) AS TOTAL_WAIT_TIME
, SUM(ROWS_READ) AS ROWS_READ
, SUM(ROWS_MODIFIED) AS ROWS_MODIFIED
, SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME FROM TABLE(MON_GET_SERVICE_SUBCLASS(NULL
, NULL
, -2)) GROUP BY SERVICE_CLASS_ID ) AS B
, UNNEST (SC_SERVICE_CLASS_ID_ARRAY
, SC_TOTAL_APP_COMMITS_ARRAY
, SC_APP_RQSTS_COMPLETED_TOTAL_ARRAY
, SC_TOTAL_RQST_TIME_ARRAY
, SC_TOTAL_WAIT_TIME_ARRAY
, SC_ROWS_READ_ARRAY
, SC_ROWS_MODIFIED_ARRAY
, SC_TOTAL_CPU_TIME_ARRAY ) AS A (SERVICE_CLASS_ID
, TOTAL_APP_COMMITS
, APP_RQSTS_COMPLETED_TOTAL
, TOTAL_RQST_TIME
, TOTAL_WAIT_TIME
, ROWS_READ
, ROWS_MODIFIED
, TOTAL_CPU_TIME ) WHERE A.SERVICE_CLASS_ID = B.SERVICE_CLASS_ID);
/* key metrics by workload */
SELECT ARRAY_AGG(WORKLOAD_NAME ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(TOTAL_APP_COMMITS ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(APP_RQSTS_COMPLETED_TOTAL ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(TOTAL_RQST_TIME ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(TOTAL_WAIT_TIME ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(ROWS_READ ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(ROWS_MODIFIED ORDER BY WORKLOAD_NAME)
, ARRAY_AGG(TOTAL_CPU_TIME ORDER BY WORKLOAD_NAME) INTO WL_WORKLOAD_NAME_ARRAY
, WL_TOTAL_APP_COMMITS_ARRAY
, WL_APP_RQSTS_COMPLETED_TOTAL_ARRAY
, WL_TOTAL_RQST_TIME_ARRAY
, WL_TOTAL_WAIT_TIME_ARRAY
, WL_ROWS_READ_ARRAY
, WL_ROWS_MODIFIED_ARRAY
, WL_TOTAL_CPU_TIME_ARRAY FROM (SELECT A.WORKLOAD_NAME
, B.TOTAL_APP_COMMITS - A.TOTAL_APP_COMMITS AS TOTAL_APP_COMMITS
, B.APP_RQSTS_COMPLETED_TOTAL - A.APP_RQSTS_COMPLETED_TOTAL AS APP_RQSTS_COMPLETED_TOTAL
, B.TOTAL_RQST_TIME - A.TOTAL_RQST_TIME AS TOTAL_RQST_TIME
, B.TOTAL_WAIT_TIME - A.TOTAL_WAIT_TIME AS TOTAL_WAIT_TIME
, B.ROWS_READ - A.ROWS_READ AS ROWS_READ
, B.ROWS_MODIFIED - A.ROWS_MODIFIED AS ROWS_MODIFIED
, B.TOTAL_CPU_TIME - A.TOTAL_CPU_TIME AS TOTAL_CPU_TIME FROM (SELECT WORKLOAD_NAME
, SUM(TOTAL_APP_COMMITS) AS TOTAL_APP_COMMITS
, SUM(APP_RQSTS_COMPLETED_TOTAL) AS APP_RQSTS_COMPLETED_TOTAL
, SUM(TOTAL_RQST_TIME) AS TOTAL_RQST_TIME
, SUM(TOTAL_WAIT_TIME) AS TOTAL_WAIT_TIME
, SUM(ROWS_READ) AS ROWS_READ
, SUM(ROWS_MODIFIED) AS ROWS_MODIFIED
, SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME FROM TABLE(MON_GET_WORKLOAD(NULL
, -2)) GROUP BY WORKLOAD_NAME ) AS B
, UNNEST (WL_WORKLOAD_NAME_ARRAY
, WL_TOTAL_APP_COMMITS_ARRAY
, WL_APP_RQSTS_COMPLETED_TOTAL_ARRAY
, WL_TOTAL_RQST_TIME_ARRAY
, WL_TOTAL_WAIT_TIME_ARRAY
, WL_ROWS_READ_ARRAY
, WL_ROWS_MODIFIED_ARRAY
, WL_TOTAL_CPU_TIME_ARRAY ) AS A (WORKLOAD_NAME
, TOTAL_APP_COMMITS
, APP_RQSTS_COMPLETED_TOTAL
, TOTAL_RQST_TIME
, TOTAL_WAIT_TIME
, ROWS_READ
, ROWS_MODIFIED
, TOTAL_CPU_TIME ) WHERE A.WORKLOAD_NAME = B.WORKLOAD_NAME);
/* key metrics by member */
SELECT ARRAY_AGG(MEMBER ORDER BY MEMBER)
, ARRAY_AGG(RQSTS_COMPLETED_TOTAL ORDER BY MEMBER)
, ARRAY_AGG(TOTAL_RQST_TIME ORDER BY MEMBER)
, ARRAY_AGG(TOTAL_WAIT_TIME ORDER BY MEMBER)
, ARRAY_AGG(IO_WAIT_TIME ORDER BY MEMBER)
, ARRAY_AGG(TOTAL_CPU_TIME ORDER BY MEMBER) INTO PM_MEMBER_ARRAY
, PM_RQSTS_COMPLETED_TOTAL_ARRAY
, PM_TOTAL_RQST_TIME_ARRAY
, PM_TOTAL_WAIT_TIME_ARRAY
, PM_IO_WAIT_TIME_ARRAY
, PM_TOTAL_CPU_TIME_ARRAY FROM (SELECT A.MEMBER
, B.RQSTS_COMPLETED_TOTAL - A.RQSTS_COMPLETED_TOTAL AS RQSTS_COMPLETED_TOTAL
, B.TOTAL_RQST_TIME - A.TOTAL_RQST_TIME AS TOTAL_RQST_TIME
, B.TOTAL_WAIT_TIME - A.TOTAL_WAIT_TIME AS TOTAL_WAIT_TIME
, B.IO_WAIT_TIME - A.IO_WAIT_TIME AS IO_WAIT_TIME
, B.TOTAL_CPU_TIME - A.TOTAL_CPU_TIME AS TOTAL_CPU_TIME FROM (SELECT MEMBER
, SUM(RQSTS_COMPLETED_TOTAL) AS RQSTS_COMPLETED_TOTAL
, SUM(TOTAL_RQST_TIME) AS TOTAL_RQST_TIME
, SUM(TOTAL_WAIT_TIME) AS TOTAL_WAIT_TIME
, SUM(POOL_READ_TIME + POOL_WRITE_TIME + DIRECT_READ_TIME + DIRECT_WRITE_TIME) AS IO_WAIT_TIME
, SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME FROM TABLE(MON_GET_SERVICE_SUBCLASS(NULL
, NULL
, -2)) GROUP BY MEMBER ) AS B
, UNNEST (PM_MEMBER_ARRAY
, PM_RQSTS_COMPLETED_TOTAL_ARRAY
, PM_TOTAL_RQST_TIME_ARRAY
, PM_TOTAL_WAIT_TIME_ARRAY
, PM_IO_WAIT_TIME_ARRAY
, PM_TOTAL_CPU_TIME_ARRAY ) AS A (MEMBER
, RQSTS_COMPLETED_TOTAL
, TOTAL_RQST_TIME
, TOTAL_WAIT_TIME
, IO_WAIT_TIME
, TOTAL_CPU_TIME ) WHERE A.MEMBER = B.MEMBER);
/* compute difference for common request metrics */
VALUES (WLM_QUEUE_TIME_TOTAL2 - WLM_QUEUE_TIME_TOTAL
, AGENT_WAIT_TIME2 - AGENT_WAIT_TIME
, TCPIP_RECVS_TOTAL2 - TCPIP_RECVS_TOTAL
, TCPIP_SENDS_TOTAL2 - TCPIP_SENDS_TOTAL
, IPC_RECVS_TOTAL2 - IPC_RECVS_TOTAL
, IPC_SENDS_TOTAL2 - IPC_SENDS_TOTAL
, TCPIP_SEND_WAIT_TIME2 - TCPIP_SEND_WAIT_TIME
, TCPIP_RECV_WAIT_TIME2 - TCPIP_RECV_WAIT_TIME
, IPC_SEND_WAIT_TIME2 - IPC_SEND_WAIT_TIME
, IPC_RECV_WAIT_TIME2 - IPC_RECV_WAIT_TIME
, TCPIP_RECV_VOLUME2 - TCPIP_RECV_VOLUME
, IPC_RECV_VOLUME2 - IPC_RECV_VOLUME
, TCPIP_SEND_VOLUME2 - TCPIP_SEND_VOLUME
, IPC_SEND_VOLUME2 - IPC_SEND_VOLUME
, LOCK_ESCALS2 - LOCK_ESCALS
, LOCK_WAITS2 - LOCK_WAITS
, LOCK_TIMEOUTS2 - LOCK_TIMEOUTS
, DEADLOCKS2 - DEADLOCKS
, ACT_COMPLETED_TOTAL2 - ACT_COMPLETED_TOTAL
, ACT_ABORTED_TOTAL2 - ACT_ABORTED_TOTAL
, ACT_REJECTED_TOTAL2 - ACT_REJECTED_TOTAL
, APP_RQSTS_COMPLETED_TOTAL2 - APP_RQSTS_COMPLETED_TOTAL
, TOTAL_RQST_TIME2 - TOTAL_RQST_TIME
, TOTAL_CPU_TIME2 - TOTAL_CPU_TIME
, TOTAL_WAIT_TIME2 - TOTAL_WAIT_TIME
, TOTAL_ACT_TIME2 - TOTAL_ACT_TIME
, TOTAL_ACT_WAIT_TIME2 - TOTAL_ACT_WAIT_TIME
, ROWS_READ2 - ROWS_READ
, ROWS_RETURNED2 - ROWS_RETURNED
, ROWS_MODIFIED2 - ROWS_MODIFIED
, POOL_DATA_L_READS2 - POOL_DATA_L_READS
, POOL_INDEX_L_READS2 - POOL_INDEX_L_READS
, POOL_TEMP_DATA_L_READS2 - POOL_TEMP_DATA_L_READS
, POOL_TEMP_INDEX_L_READS2 - POOL_TEMP_INDEX_L_READS
, POOL_TEMP_XDA_L_READS2 - POOL_TEMP_XDA_L_READS
, POOL_XDA_L_READS2 - POOL_XDA_L_READS
, POOL_DATA_P_READS2 - POOL_DATA_P_READS
, POOL_INDEX_P_READS2 - POOL_INDEX_P_READS
, POOL_TEMP_DATA_P_READS2 - POOL_TEMP_DATA_P_READS
, POOL_TEMP_INDEX_P_READS2 - POOL_TEMP_INDEX_P_READS
, POOL_TEMP_XDA_P_READS2 - POOL_TEMP_XDA_P_READS
, POOL_XDA_P_READS2 - POOL_XDA_P_READS
, POOL_READ_TIME2 - POOL_READ_TIME
, POOL_ASYNC_READ_TIME2 - POOL_ASYNC_READ_TIME
, POOL_WRITE_TIME2 - POOL_WRITE_TIME
, POOL_ASYNC_WRITE_TIME2 - POOL_ASYNC_WRITE_TIME
, POOL_DATA_WRITES2 - POOL_DATA_WRITES
, POOL_INDEX_WRITES2 - POOL_INDEX_WRITES
, POOL_XDA_WRITES2 - POOL_XDA_WRITES
, DIRECT_READ_TIME2 - DIRECT_READ_TIME
, DIRECT_READS2 - DIRECT_READS
, DIRECT_READ_REQS2 - DIRECT_READ_REQS
, DIRECT_WRITE_TIME2 - DIRECT_WRITE_TIME
, DIRECT_WRITES2 - DIRECT_WRITES
, DIRECT_WRITE_REQS2 - DIRECT_WRITE_REQS
, LOG_DISK_WAIT_TIME2 - LOG_DISK_WAIT_TIME
, LOG_DISK_WAITS_TOTAL2 - LOG_DISK_WAITS_TOTAL
, NUM_LOG_BUFFER_FULL2 - NUM_LOG_BUFFER_FULL
, CLIENT_IDLE_WAIT_TIME2 - CLIENT_IDLE_WAIT_TIME
, LOCK_WAIT_TIME2 - LOCK_WAIT_TIME
, FCM_SEND_WAIT_TIME2 - FCM_SEND_WAIT_TIME
, TOTAL_SORTS2 - TOTAL_SORTS
, SORT_OVERFLOWS2 - SORT_OVERFLOWS
, POST_THRESHOLD_SORTS2 - POST_THRESHOLD_SORTS
, POST_SHRTHRESHOLD_SORTS2 - POST_SHRTHRESHOLD_SORTS
, FCM_SEND_VOLUME2 - FCM_SEND_VOLUME
, FCM_SENDS_TOTAL2 - FCM_SENDS_TOTAL
, FCM_RECV_VOLUME2 - FCM_RECV_VOLUME
, FCM_RECVS_TOTAL2 - FCM_RECVS_TOTAL
, WLM_QUEUE_ASSIGNMENTS_TOTAL2 - WLM_QUEUE_ASSIGNMENTS_TOTAL
, FCM_RECV_WAIT_TIME2 - FCM_RECV_WAIT_TIME
, TOTAL_SECTION_PROC_TIME2 - TOTAL_SECTION_PROC_TIME
, TOTAL_SECTION_SORT_PROC_TIME2 - TOTAL_SECTION_SORT_PROC_TIME
, TOTAL_COMPILE_PROC_TIME2 - TOTAL_COMPILE_PROC_TIME
, TOTAL_IMPLICIT_COMPILE_PROC_TIME2 - TOTAL_IMPLICIT_COMPILE_PROC_TIME
, TOTAL_COMMIT_PROC_TIME2 - TOTAL_COMMIT_PROC_TIME
, TOTAL_ROLLBACK_PROC_TIME2 - TOTAL_ROLLBACK_PROC_TIME
, TOTAL_RUNSTATS_PROC_TIME2 - TOTAL_RUNSTATS_PROC_TIME
, TOTAL_REORG_PROC_TIME2 - TOTAL_REORG_PROC_TIME
, TOTAL_LOAD_PROC_TIME2 - TOTAL_LOAD_PROC_TIME
, TOTAL_APP_COMMITS2 - TOTAL_APP_COMMITS
, TOTAL_ROUTINE_INVOCATIONS2 - TOTAL_ROUTINE_INVOCATIONS
, TOTAL_ROUTINE_TIME2 - TOTAL_ROUTINE_TIME
, TOTAL_COMPILATIONS2 - TOTAL_COMPILATIONS
, PKG_CACHE_INSERTS2 - PKG_CACHE_INSERTS
, PKG_CACHE_LOOKUPS2 - PKG_CACHE_LOOKUPS
, CAT_CACHE_INSERTS2 - CAT_CACHE_INSERTS
, CAT_CACHE_LOOKUPS2 - CAT_CACHE_LOOKUPS
, INT_COMMITS2 - INT_COMMITS
, TOTAL_APP_ROLLBACKS2 - TOTAL_APP_ROLLBACKS
, INT_ROLLBACKS2 - INT_ROLLBACKS
, TOTAL_RUNSTATS2 - TOTAL_RUNSTATS
, TOTAL_REORGS2 - TOTAL_REORGS
, TOTAL_LOADS2 - TOTAL_LOADS
,CF_WAIT_TIME2 - CF_WAIT_TIME
,RECLAIM_WAIT_TIME2 - RECLAIM_WAIT_TIME
,SPACEMAPPAGE_RECLAIM_WAIT_TIME2 - SPACEMAPPAGE_RECLAIM_WAIT_TIME
,POOL_DATA_GBP_L_READS2 - POOL_DATA_GBP_L_READS
,POOL_DATA_GBP_P_READS2 - POOL_DATA_GBP_P_READS
,POOL_INDEX_GBP_L_READS2 - POOL_INDEX_GBP_L_READS
,POOL_INDEX_GBP_P_READS2 - POOL_INDEX_GBP_P_READS
,POOL_DATA_LBP_PAGES_FOUND2 - POOL_DATA_LBP_PAGES_FOUND
,POOL_INDEX_LBP_PAGES_FOUND2 - POOL_INDEX_LBP_PAGES_FOUND
,POOL_ASYNC_DATA_LBP_PAGES_FOUND2 - POOL_ASYNC_DATA_LBP_PAGES_FOUND
,POOL_ASYNC_INDEX_LBP_PAGES_FOUND2 - POOL_ASYNC_INDEX_LBP_PAGES_FOUND
,POOL_XDA_GBP_L_READS2 - POOL_XDA_GBP_L_READS
,POOL_XDA_GBP_P_READS2 - POOL_XDA_GBP_P_READS
,POOL_XDA_LBP_PAGES_FOUND2 - POOL_XDA_LBP_PAGES_FOUND
,POOL_ASYNC_XDA_LBP_PAGES_FOUND2 - POOL_ASYNC_XDA_LBP_PAGES_FOUND
,POOL_COL_L_READS2 - POOL_COL_L_READS
,POOL_TEMP_COL_L_READS2 - POOL_TEMP_COL_L_READS
,POOL_COL_P_READS2 - POOL_COL_P_READS
,POOL_TEMP_COL_P_READS2 - POOL_TEMP_COL_P_READS
,POOL_COL_LBP_PAGES_FOUND2 - POOL_COL_LBP_PAGES_FOUND
,POOL_COL_WRITES2 - POOL_COL_WRITES
,POOL_COL_GBP_L_READS2 - POOL_COL_GBP_L_READS
,POOL_COL_GBP_P_READS2 - POOL_COL_GBP_P_READS
,POOL_ASYNC_COL_LBP_PAGES_FOUND2 - POOL_ASYNC_COL_LBP_PAGES_FOUND
,POOL_ASYNC_DATA_READS2 - POOL_ASYNC_DATA_READS
,POOL_ASYNC_INDEX_READS2 - POOL_ASYNC_INDEX_READS
,POOL_ASYNC_XDA_READS2 - POOL_ASYNC_XDA_READS
,POOL_ASYNC_COL_READS2 - POOL_ASYNC_COL_READS) INTO WLM_QUEUE_TIME_TOTAL
, AGENT_WAIT_TIME
, TCPIP_RECVS_TOTAL
, TCPIP_SENDS_TOTAL
, IPC_RECVS_TOTAL
, IPC_SENDS_TOTAL
, TCPIP_SEND_WAIT_TIME
, TCPIP_RECV_WAIT_TIME
, IPC_SEND_WAIT_TIME
, IPC_RECV_WAIT_TIME
, TCPIP_RECV_VOLUME
, IPC_RECV_VOLUME
, TCPIP_SEND_VOLUME
, IPC_SEND_VOLUME
, LOCK_ESCALS
, LOCK_WAITS
, LOCK_TIMEOUTS
, DEADLOCKS
, ACT_COMPLETED_TOTAL
, ACT_ABORTED_TOTAL
, ACT_REJECTED_TOTAL
, APP_RQSTS_COMPLETED_TOTAL
, TOTAL_RQST_TIME
, TOTAL_CPU_TIME
, TOTAL_WAIT_TIME
, TOTAL_ACT_TIME
, TOTAL_ACT_WAIT_TIME
, ROWS_READ
, ROWS_RETURNED
, ROWS_MODIFIED
, 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_READ_TIME
, POOL_ASYNC_READ_TIME
, POOL_WRITE_TIME
, POOL_ASYNC_WRITE_TIME
, POOL_DATA_WRITES
, POOL_INDEX_WRITES
, POOL_XDA_WRITES
, DIRECT_READ_TIME
, DIRECT_READS
, DIRECT_READ_REQS
, DIRECT_WRITE_TIME
, DIRECT_WRITES
, DIRECT_WRITE_REQS
, LOG_DISK_WAIT_TIME
, LOG_DISK_WAITS_TOTAL
, NUM_LOG_BUFFER_FULL
, CLIENT_IDLE_WAIT_TIME
, LOCK_WAIT_TIME
, FCM_SEND_WAIT_TIME
, TOTAL_SORTS
, SORT_OVERFLOWS
, POST_THRESHOLD_SORTS
, POST_SHRTHRESHOLD_SORTS
, FCM_SEND_VOLUME
, FCM_SENDS_TOTAL
, FCM_RECV_VOLUME
, FCM_RECVS_TOTAL
, WLM_QUEUE_ASSIGNMENTS_TOTAL
, FCM_RECV_WAIT_TIME
, TOTAL_SECTION_PROC_TIME
, TOTAL_SECTION_SORT_PROC_TIME
, TOTAL_COMPILE_PROC_TIME
, TOTAL_IMPLICIT_COMPILE_PROC_TIME
, TOTAL_COMMIT_PROC_TIME
, TOTAL_ROLLBACK_PROC_TIME
, TOTAL_RUNSTATS_PROC_TIME
, TOTAL_REORG_PROC_TIME
, TOTAL_LOAD_PROC_TIME
, TOTAL_APP_COMMITS
, TOTAL_ROUTINE_INVOCATIONS
, TOTAL_ROUTINE_TIME
, TOTAL_COMPILATIONS
, PKG_CACHE_INSERTS
, PKG_CACHE_LOOKUPS
, CAT_CACHE_INSERTS
, CAT_CACHE_LOOKUPS
, INT_COMMITS
, TOTAL_APP_ROLLBACKS
, INT_ROLLBACKS
, TOTAL_RUNSTATS
, TOTAL_REORGS
, TOTAL_LOADS
,CF_WAIT_TIME
,RECLAIM_WAIT_TIME
,SPACEMAPPAGE_RECLAIM_WAIT_TIME
,POOL_DATA_GBP_L_READS
,POOL_DATA_GBP_P_READS
,POOL_INDEX_GBP_L_READS
,POOL_INDEX_GBP_P_READS
,POOL_DATA_LBP_PAGES_FOUND
,POOL_INDEX_LBP_PAGES_FOUND
,POOL_ASYNC_DATA_LBP_PAGES_FOUND
,POOL_ASYNC_INDEX_LBP_PAGES_FOUND
,POOL_XDA_GBP_L_READS
,POOL_XDA_GBP_P_READS
,POOL_XDA_LBP_PAGES_FOUND
,POOL_ASYNC_XDA_LBP_PAGES_FOUND
,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_COL_GBP_L_READS
,POOL_COL_GBP_P_READS
,POOL_ASYNC_COL_LBP_PAGES_FOUND
,POOL_ASYNC_DATA_READS
,POOL_ASYNC_INDEX_READS
,POOL_ASYNC_XDA_READS
,POOL_ASYNC_COL_READS;
END IF;
/* =============================== */
/* start of report output - header */
/* =============================== */
VALUES (CURRENT SERVER) INTO DBNAME;
SELECT ARRAY_AGG(VALUE) INTO DATE_COMPAT_ARRAY FROM SYSIBMADM.DBCFG WHERE NAME = 'date_compat';
IF (DATE_COMPAT_ARRAY[1] = 'ON') THEN VALUES (CURRENT DATE) INTO TODAY;
ELSE VALUES (CURRENT DATE || SPACE(1) || CURRENT TIME) INTO TODAY;
END IF;
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
SET REPOUTPUT[IR] = MSGCACHE[IC] || COALESCE(DBNAME
,'');
VALUES (IR+1
, IC+1) INTO IR
, IC;
SET REPOUTPUT[IR] = MSGCACHE[IC] || COALESCE(TODAY
,'');
VALUES (IR+1
, IC+1) INTO IR
, IC;
SET REPOUTPUT[IR] = MSGCACHE[IC] || COALESCE(VARCHAR(TIMEINTERVAL)
,'');
VALUES (IR+1
, IC+1) INTO IR
, IC;
/* =========================== */
/* part 1 - System performance */
/* =========================== */
SET LAST_IR = IR + 3;
WHILE (IR <= LAST_IR) DO SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
END WHILE;
CALL COMMONREQMETRICS (REPOUTPUT
, IR
, CR_MSGCACHE
, TIMEINTERVAL
, WLM_QUEUE_TIME_TOTAL
, AGENT_WAIT_TIME
, TCPIP_RECVS_TOTAL
, TCPIP_SENDS_TOTAL
, IPC_RECVS_TOTAL
, IPC_SENDS_TOTAL
, TCPIP_SEND_WAIT_TIME
, TCPIP_RECV_WAIT_TIME
, IPC_SEND_WAIT_TIME
, IPC_RECV_WAIT_TIME
, TCPIP_RECV_VOLUME
, IPC_RECV_VOLUME
, TCPIP_SEND_VOLUME
, IPC_SEND_VOLUME
, LOCK_ESCALS
, LOCK_WAITS
, LOCK_TIMEOUTS
, DEADLOCKS
, ACT_COMPLETED_TOTAL
, ACT_ABORTED_TOTAL
, ACT_REJECTED_TOTAL
, APP_RQSTS_COMPLETED_TOTAL
, TOTAL_RQST_TIME
, TOTAL_CPU_TIME
, TOTAL_WAIT_TIME
, TOTAL_ACT_TIME
, TOTAL_ACT_WAIT_TIME
, ROWS_READ
, ROWS_RETURNED
, ROWS_MODIFIED
, 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_READ_TIME - POOL_ASYNC_READ_TIME
, POOL_WRITE_TIME - POOL_ASYNC_WRITE_TIME
, POOL_DATA_WRITES
, POOL_INDEX_WRITES
, POOL_XDA_WRITES
, DIRECT_READ_TIME
, DIRECT_READS
, DIRECT_READ_REQS
, DIRECT_WRITE_TIME
, DIRECT_WRITES
, DIRECT_WRITE_REQS
, LOG_DISK_WAIT_TIME
, LOG_DISK_WAITS_TOTAL
, NUM_LOG_BUFFER_FULL
, CLIENT_IDLE_WAIT_TIME
, LOCK_WAIT_TIME
, FCM_SEND_WAIT_TIME
, TOTAL_SORTS
, SORT_OVERFLOWS
, POST_THRESHOLD_SORTS
, POST_SHRTHRESHOLD_SORTS
, FCM_SEND_VOLUME
, FCM_SENDS_TOTAL
, FCM_RECV_VOLUME
, FCM_RECVS_TOTAL
, WLM_QUEUE_ASSIGNMENTS_TOTAL
, FCM_RECV_WAIT_TIME
, TOTAL_SECTION_PROC_TIME
, TOTAL_SECTION_SORT_PROC_TIME
, TOTAL_COMPILE_PROC_TIME
, TOTAL_IMPLICIT_COMPILE_PROC_TIME
, TOTAL_COMMIT_PROC_TIME
, TOTAL_ROLLBACK_PROC_TIME
, TOTAL_RUNSTATS_PROC_TIME
, TOTAL_REORG_PROC_TIME
, TOTAL_LOAD_PROC_TIME
, TOTAL_APP_COMMITS
, TOTAL_ROUTINE_INVOCATIONS
, TOTAL_ROUTINE_TIME
, TOTAL_COMPILATIONS
, PKG_CACHE_INSERTS
, PKG_CACHE_LOOKUPS
, CAT_CACHE_INSERTS
, CAT_CACHE_LOOKUPS
, INT_COMMITS
, TOTAL_APP_ROLLBACKS
, INT_ROLLBACKS
, TOTAL_RUNSTATS
, TOTAL_REORGS
, TOTAL_LOADS
,CF_WAIT_TIME
,RECLAIM_WAIT_TIME
,SPACEMAPPAGE_RECLAIM_WAIT_TIME
,POOL_DATA_GBP_L_READS
,POOL_DATA_GBP_P_READS
,POOL_INDEX_GBP_L_READS
,POOL_INDEX_GBP_P_READS
,POOL_DATA_LBP_PAGES_FOUND
,POOL_INDEX_LBP_PAGES_FOUND
,POOL_ASYNC_DATA_LBP_PAGES_FOUND
,POOL_ASYNC_INDEX_LBP_PAGES_FOUND
,POOL_XDA_GBP_L_READS
,POOL_XDA_GBP_P_READS
,POOL_XDA_LBP_PAGES_FOUND
,POOL_ASYNC_XDA_LBP_PAGES_FOUND
,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_COL_GBP_L_READS
,POOL_COL_GBP_P_READS
,POOL_ASYNC_COL_LBP_PAGES_FOUND
,POOL_ASYNC_DATA_READS
,POOL_ASYNC_INDEX_READS
,POOL_ASYNC_XDA_READS
,POOL_ASYNC_COL_READS);
/* =========================================== */
/* part 2 - Application performance drill down */
/* =========================================== */
/* Application performance database-wide */
SET IC = IC - 1;
SET LAST_IR = IR + 5;
WHILE (IR <= LAST_IR) DO SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
END WHILE;
SET IC = IC + 1;
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
IF (APP_RQSTS_COMPLETED_TOTAL > 0) THEN SET CPU_PER_RQST = DOUBLE(TOTAL_CPU_TIME) / DOUBLE(APP_RQSTS_COMPLETED_TOTAL);
ELSE SET CPU_PER_RQST = 0;
END IF;
IF (TOTAL_RQST_TIME > 0) THEN SET WAIT_TIME_PCT = 100 * (DOUBLE(TOTAL_WAIT_TIME) / DOUBLE(TOTAL_RQST_TIME));
ELSE SET WAIT_TIME_PCT = 0;
END IF;
SET REPOUTPUT[IR] = RPAD(VARCHAR(DECIMAL(CPU_PER_RQST))
,22
,' ') || SPACE(2) || RPAD(VARCHAR(DECIMAL(WAIT_TIME_PCT))
,11
,' ') || SPACE(2) || RPAD(VARCHAR(TOTAL_APP_COMMITS)
,13
,' ') || SPACE(2) || RPAD(VARCHAR(ROWS_READ + ROWS_MODIFIED)
,28
,' ');
SET IR = IR + 1;
/* Application performance by connection */
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
SET IC = IC + 1;
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
VALUES (1
, CARDINALITY(CN_APPLICATION_HANDLE_ARRAY)) INTO I
, L;
WHILE (I <= L) DO IF (CN_APP_RQSTS_COMPLETED_TOTAL_ARRAY[I] > 0) THEN SET CPU_PER_RQST = DOUBLE(CN_TOTAL_CPU_TIME_ARRAY[I]) / DOUBLE(CN_APP_RQSTS_COMPLETED_TOTAL_ARRAY[I]);
ELSE SET CPU_PER_RQST = 0;
END IF;
IF (CN_TOTAL_RQST_TIME_ARRAY[I] > 0) THEN SET WAIT_TIME_PCT = 100 * (DOUBLE(CN_TOTAL_WAIT_TIME_ARRAY[I]) / DOUBLE(CN_TOTAL_RQST_TIME_ARRAY[I]));
ELSE SET WAIT_TIME_PCT = 0;
END IF;
SET REPOUTPUT[IR] = RPAD(VARCHAR(CN_APPLICATION_HANDLE_ARRAY[I])
,8
,' ') || SPACE(7) || RPAD(VARCHAR(DECIMAL(CPU_PER_RQST))
,19
,' ') || SPACE(3) || RPAD(VARCHAR(DECIMAL(WAIT_TIME_PCT))
,11
,' ') || SPACE(4) || RPAD(VARCHAR(CN_TOTAL_APP_COMMITS_ARRAY[I])
,13
,' ') || SPACE(2) || RPAD(VARCHAR(CN_ROWS_READ_ARRAY[I] + CN_ROWS_MODIFIED_ARRAY[I])
,13
,' ');
VALUES (IR + 1
, I + 1) INTO IR
, I;
END WHILE;
/* Application performance by service class */
SET LAST_IR = IR + 5;
WHILE (IR <= LAST_IR) DO SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
END WHILE;
VALUES (1
, CARDINALITY(SC_SERVICE_CLASS_ID_ARRAY)) INTO I
, L;
WHILE (I <= L) DO IF (SC_APP_RQSTS_COMPLETED_TOTAL_ARRAY[I] > 0) THEN SET CPU_PER_RQST = DOUBLE(SC_TOTAL_CPU_TIME_ARRAY[I]) / DOUBLE(SC_APP_RQSTS_COMPLETED_TOTAL_ARRAY[I]);
ELSE SET CPU_PER_RQST = 0;
END IF;
IF (SC_TOTAL_RQST_TIME_ARRAY[I] > 0) THEN SET WAIT_TIME_PCT = 100 * (DOUBLE(SC_TOTAL_WAIT_TIME_ARRAY[I]) / DOUBLE(SC_TOTAL_RQST_TIME_ARRAY[I]));
ELSE SET WAIT_TIME_PCT = 0;
END IF;
SET REPOUTPUT[IR] = RPAD(VARCHAR(SC_SERVICE_CLASS_ID_ARRAY[I])
,8
,' ') || SPACE(7) || RPAD(VARCHAR(DECIMAL(CPU_PER_RQST))
,19
,' ') || SPACE(3) || RPAD(VARCHAR(DECIMAL(WAIT_TIME_PCT))
,11
,' ') || SPACE(4) || RPAD(VARCHAR(SC_TOTAL_APP_COMMITS_ARRAY[I])
,13
,' ') || SPACE(2) || RPAD(VARCHAR(SC_ROWS_READ_ARRAY[I] + SC_ROWS_MODIFIED_ARRAY[I])
,13
,' ');
VALUES (IR + 1
, I + 1) INTO IR
, I;
END WHILE;
/* Application performance by workload */
SET LAST_IR = IR + 5;
WHILE (IR <= LAST_IR) DO SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
END WHILE;
VALUES (1
, CARDINALITY(WL_WORKLOAD_NAME_ARRAY)) INTO I
, L;
WHILE (I <= L) DO IF (WL_APP_RQSTS_COMPLETED_TOTAL_ARRAY[I] > 0) THEN SET CPU_PER_RQST = DOUBLE(WL_TOTAL_CPU_TIME_ARRAY[I]) / DOUBLE(WL_APP_RQSTS_COMPLETED_TOTAL_ARRAY[I]);
ELSE SET CPU_PER_RQST = 0;
END IF;
IF (WL_TOTAL_RQST_TIME_ARRAY[I] > 0) THEN SET WAIT_TIME_PCT = 100 * (DOUBLE(WL_TOTAL_WAIT_TIME_ARRAY[I]) / DOUBLE(WL_TOTAL_RQST_TIME_ARRAY[I]));
ELSE SET WAIT_TIME_PCT = 0;
END IF;
SET REPOUTPUT[IR] = RPAD(WL_WORKLOAD_NAME_ARRAY[I]
,13
,' ') || SPACE(2) || RPAD(VARCHAR(DECIMAL(CPU_PER_RQST))
,22
,' ') || SPACE(2) || RPAD(VARCHAR(DECIMAL(WAIT_TIME_PCT))
,11
,' ') || SPACE(2) || RPAD(VARCHAR(WL_TOTAL_APP_COMMITS_ARRAY[I])
,13
,' ') || SPACE(2) || RPAD(VARCHAR(WL_ROWS_READ_ARRAY[I] + WL_ROWS_MODIFIED_ARRAY[I])
,13
,' ');
VALUES (IR + 1
, I + 1) INTO IR
, I;
END WHILE;
/* ================================= */
/* part 3 - Member level information */
/* ================================= */
SET LAST_IR = IR + 9;
WHILE (IR <= LAST_IR) DO SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
END WHILE;
VALUES (1
, CARDINALITY(PM_MEMBER_ARRAY)) INTO I
, L;
WHILE (I <= L) DO IF (PM_RQSTS_COMPLETED_TOTAL_ARRAY[I] > 0) THEN SET CPU_PER_RQST = DOUBLE(PM_TOTAL_CPU_TIME_ARRAY[I]) / DOUBLE(PM_RQSTS_COMPLETED_TOTAL_ARRAY[I]);
ELSE SET CPU_PER_RQST = 0;
END IF;
IF (PM_TOTAL_RQST_TIME_ARRAY[I] > 0) THEN SET WAIT_TIME_PCT = 100 * (DOUBLE(PM_TOTAL_WAIT_TIME_ARRAY[I]) / DOUBLE(PM_TOTAL_RQST_TIME_ARRAY[I]));
ELSE SET WAIT_TIME_PCT = 0;
END IF;
SET REPOUTPUT[IR] = RPAD(VARCHAR(PM_MEMBER_ARRAY[I])
,6
,' ') || SPACE(2) || RPAD(VARCHAR(DECIMAL(CPU_PER_RQST))
,22
,' ') || SPACE(2) || RPAD(VARCHAR(DECIMAL(WAIT_TIME_PCT))
,11
,' ') || SPACE(2) || RPAD(VARCHAR(PM_RQSTS_COMPLETED_TOTAL_ARRAY[I])
,16
,' ') || SPACE(2) || RPAD(VARCHAR(PM_IO_WAIT_TIME_ARRAY[I])
,17
,' ');
VALUES (IR + 1
, I + 1) INTO IR
, I;
END WHILE;
SET REPOUTPUT[IR] = MSGCACHE[IC];
VALUES (IR+1
, IC+1) INTO IR
, IC;
/* open cursor to return report as result set */
OPEN RES;
END /* of procedure */
Understanding what the Code is doing.
It is easy to see the DBSUMMARY procedure getting the values needed and subtracting the before and after to get only what happened in the specified time period.
The part that’s not clear is how DB2 turns that into nicely formatted output. It seems to use SYSIBM.SQLCAMESSAGECCSID on a file called db2monreport.mo (which is in /opt/ibm/db2/V10.5/msg/en_US.iso88591 in my implementation) to pull the additional text out. However as far as I can tell, db2monreport.mo is a binary format that I cannot easily read or change. From what I read, .mo files are associated with GNU gettext. I tried to use poedit to view/edit the file, but it doesn’t work. So this file gets passed some values and based on those values, returns text in the right format. That’s my best guess anyway. I don’t see any other place where the other text returned is.
Problem
And that’s where I emailed some friends at IBM and discovered that the effort to make MONREPORT customizable was abandonned. They are removing the documentation that states it can be customized. All the same, I learned a lot just from parsing the stored procedure code, and see how I could create a similar stored procedure that does what I want it to. Hopefully I’ll find the time to do that and be able to blog about it.
I just started using MON_GET_CONNECTION yesterday, and found this post while researching other functions I have never used…. amazingly powerful and your documentation is freaking awesome, thank you for this resource. I have seen those braids several times in the past, and every time I see them I know I am going to be reading some valuable info and will be learning a lot!!!! This has made my month!
Well, this comment brought tears of joy to my eyes! Thank you!
I love to hear that others find my blog useful.
[…] Note that the most popular articles tend to be the basic things – they appear to a wider audience, I suppose. But the ones that are most fun to write are the ones where I learn and experiment. Some of MY favorites to write this year were: Activities vs. Requests Detailed Analysis of Individual Performance Metrics Runstats Issue With Columns Where There is a Long Common Prefix in Character Data Three Different Ways to Write the Same Join in SQL When Index Scans Attack! Example of A Clustering Low-Cardinality Index Helping Query Performance DB2 Explain Output in Another Format Customizing MONREPORT … or not […]