MON_CONNECTION_SUMMARY - Retrieve metrics for all connections

The MON_CONNECTION_SUMMARY administrative view returns key metrics for all connections in the currently connected database.

This view is designed to help monitor the system in a high-level manner, showing incoming work per connection.

The metrics returned represent the accumulation of all metrics for requests that were submitted by the identified connection across all members of the database.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:
  • SELECT privilege on the MON_CONNECTION_SUMMARY administrative view
  • CONTROL privilege on the MON_CONNECTION_SUMMARY administrative view
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority
  • ACCESSCTRL authority
  • SECADM authority

Default PUBLIC privilege

None

Information returned

Table 1. Information returned by the MON_CONNECTION_SUMMARY administrative view
Column name Data type Description or Monitor element
APPLICATION_HANDLE BIGINT application_handle - Application handle
APPLICATION_NAME VARCHAR(128) appl_name - Application name
APPLICATION_ID VARCHAR(128) appl_id - Application ID
SESSION_AUTH_ID VARCHAR(128) session_auth_id - Session authorization ID
TOTAL_APP_COMMITS BIGINT total_app_commits - Total application commits monitor elements
TOTAL_APP_ROLLBACKS BIGINT total_app_rollbacks - Total application rollbacks monitor element
ACT_COMPLETED_TOTAL BIGINT act_completed_total - Total completed activities monitor element
APP_RQSTS_COMPLETED_TOTAL BIGINT app_rqsts_completed_total - Total application requests completed monitor element
AVG_RQST_CPU_TIME BIGINT Average amount of CPU time, in microseconds, used by all external requests that completed successfully. It represents the total of both user and system CPU time. Formula to calculate ratio: TOTAL_CPU_TIME / APP_RQSTS_COMPLETED_TOTAL
ROUTINE_TIME_RQST_PERCENT DECIMAL(5,2) The percentage of time the database server spent working on requests that was spent executing user routines. Formula to calculate ratio: TOTAL_ROUTINE_TIME / TOTAL_RQST_TIME
RQST_WAIT_TIME_PERCENT DECIMAL(5,2) The percentage of the time spent working on requests that was spent waiting within the database server. Formula to calculate ratio: TOTAL_WAIT_TIME / TOTAL_RQST_TIME
ACT_WAIT_TIME_PERCENT DECIMAL(5,2) The percentage of the time spent executing activities that was spent waiting within the database server. Formula to calculate ratio: TOTAL_ACT_WAIT_TIME / TOTAL_ACT_TIME
IO_WAIT_TIME_PERCENT DECIMAL(5,2) The percentage of the time spent waiting within the database server that was due to I/O operations. This includes time spent performing direct reads or direct writes, and time spent reading data and index pages from the table space to the bufferpool or writing them back to disk. Formula to calculate ratio: (POOL_READ_TIME + POOL_WRITE_TIME + DIRECT_READ_TIME + DIRECT_WRITE_TIME) / TOTAL_WAIT_TIME
LOCK_WAIT_TIME_PERCENT DECIMAL(5,2) The percentage of time spent waiting within the database server that was spent waiting on locks. Formula to calculate ratio: LOCK_WAIT_TIME / TOTAL_WAIT_TIME
AGENT_WAIT_TIME_PERCENT DECIMAL(5,2) The percentage of time spent waiting within the database server that was spent by an application queued to wait for an agent under concentrator configurations. Formula to calculate ratio: AGENT_WAIT_TIME / TOTAL_WAIT_TIME
NETWORK_WAIT_TIME_PERCENT DECIMAL(5,2) The percentage of time spent waiting within the database server that was spent on client-server communications. This includes time spent sending and receiving data over TCP/IP or using the IPC protocol. Formula to calculate ratio: (TCPIP_SEND_WAIT_TIME + TCPIP_RECV_WAIT_TIME + IPC_SEND_WAIT_TIME + IPC_RECV_WAIT_TIME) / TOTAL_WAIT_TIME
SECTION_PROC_TIME_PERCENT DECIMAL(5,2) The percentage of time the database server spent actively working on requests that was spent executing sections. This includes the time spent performing sorts. Formula to calculate ratio: TOTAL_SECTION_PROC_TIME / (TOTAL_RQST_TIME - TOTAL_WAIT_TIME)
SECTION_SORT_PROC_TIME_PERCENT DECIMAL(5,2) The percentage of time the database server spent actively working on requests that was spent performing sorts while executing sections. Formula to calculate ratio: TOTAL_SECTION_SORT_PROC_TIME / (TOTAL_RQST_TIME - TOTAL_WAIT_TIME)
COMPILE_PROC_TIME_PERCENT DECIMAL(5,2) The percentage of time the database server spent actively working on requests that was spent compiling an SQL statement. This includes explicit and implicit compile times. Formula to calculate ratio: (TOTAL_COMPILE_PROC_TIME + TOTAL_IMPLICIT_COMPILE_PROC_TIME) / (TOTAL_RQST_TIME - TOTAL_WAIT_TIME)
TRANSACT_END_PROC_TIME_PERCENT DECIMAL(5,2) The percentage of time the database server spent actively working on requests that was spent performing commit processing or rolling back transactions. Formula to calculate ratio: (TOTAL_COMMIT_PROC_TIME + TOTAL_ROLLBACK_PROC_TIME) / (TOTAL_RQST_TIME - TOTAL_WAIT_TIME)
UTILS_PROC_TIME_PERCENT DECIMAL(5,2) The percentage of time the database server spent actively working on requests that was spent running utilities. This includes performing runstats, reorganization, and load operations. Formula to calculate ratio: (TOTAL_RUNSTATS_PROC_TIME + TOTAL_REORG_PROC_TIME + TOTAL_LOAD_PROC_TIME) / (TOTAL_RQST_TIME - TOTAL_WAIT_TIME)
AVG_LOCK_WAITS_PER_ACT BIGINT The average number of times that applications or connections waited for locks per coordinator activities (successful and aborted). Formula to calculate ratio: LOCK_WAITS / (ACT_COMPLETED_TOTAL + ACT_ABORTED_TOTAL)
AVG_LOCK_TIMEOUTS_PER_ACT BIGINT The average number of times that a request to lock an object timed out per coordinator activities (successful and aborted). Formula to calculate ratio: LOCK_TIMEOUTS / (ACT_COMPLETED_TOTAL + ACT_ABORTED_TOTAL)
AVG_DEADLOCKS_PER_ACT BIGINT The average number of deadlocks per coordinator activities (successful and aborted). Formula to calculate ratio: DEADLOCKS / (ACT_COMPLETED_TOTAL + ACT_ABORTED_TOTAL)
AVG_LOCK_ESCALS_PER_ACT BIGINT The average number of times that locks have been escalated from several row locks to a table lock per coordinator activities (successful and aborted). Formula to calculate ratio: LOCK_ESCALS / (ACT_COMPLETED_TOTAL + ACT_ABORTED_TOTAL)
ROWS_READ_PER_ROWS_RETURNED BIGINT The average number of rows read from the table per rows returned to the application. Formula to calculate ratio: ROWS_READ / ROWS_RETURNED
TOTAL_BP_HIT_RATIO_PERCENT DECIMAL(5,2) The percentage of time that the database manager did not need to load a page from disk to service a data request, index page request, or a request to process column-organized table data, including requests for XML storage objects (XDAs). In a Db2® pureScale® environment, this value represents the total hit ratio for the local bufferpool. Formula to calculate ratio: (POOL_DATA_LBP_PAGES_FOUND + POOL_INDEX_LBP_PAGES_FOUND + POOL_XDA_LBP_PAGES_FOUND + POOL_COL_LBP_PAGES_FOUND) /
(POOL_DATA_L_READS + POOL_TEMP_DATA_L_READS + POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS + POOL_XDA_L_READS + POOL_TEMP_XDA_L_READS + POOL_COL_L_READS + POOL_TEMP_COL_L_READS)
TOTAL_GBP_HIT_RATIO_PERCENT DECIMAL(5,2) On a Db2 pureScale system, the percentage of time that the database manager did not need to load a page from disk into the local bufferpool to service a data request, index request, XML storage object (XDA) page request, or a request to process column-organized table data, as the page was located in the group bufferpool. This value will always be 0 for systems outside of Db2 pureScale.
TOTAL_CACHING_TIER_HIT_RATIO_PERCENT DECIMAL(5,2) The percentage of time that the database manager was able to service data-type, columnar-type, XML data-type, or index-type page read requests by utilizing the caching tier.
CF_WAIT_TIME_PERCENT DECIMAL(5,2) On a Db2 pureScale system, the percentage of the total wait time spent waiting for caching facility communications. This value will always be 0 for systems outside of Db2 pureScale.
RECLAIM_WAIT_TIME_PERCENT DECIMAL(5,2) On a Db2 pureScale system, the percentage of the total wait time spent waiting for page reclaims. This value will always be 0 for systems outside of Db2 pureScale.
SPACEMAPPAGE_RECLAIM_WAIT_TIME_PERCENT DECIMAL(5,2) On a Db2 pureScale system, the percentage of the total wait time spent waiting for space map page reclaims. This value will always be 0 for systems outside of Db2 pureScale.