The MON_GET_CONNECTION_DETAILS table function returns detailed metrics for one or more connections.
>>-MON_GET_CONNECTION_DETAILS--(--application_handle--,---------> >--member--)---------------------------------------------------><
The schema is SYSPROC.
EXECUTE privilege on the MON_GET_CONNECTION_DETAILS function.
Display connections returning the highest volume of data to clients, ordered by rows returned.
SELECT detmetrics.application_handle,
detmetrics.rows_returned,
detmetrics.tcpip_send_volume
FROM TABLE(MON_GET_CONNECTION_DETAILS(CAST(NULL as bigint), -2))
AS CONNMETRICS,
XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'),
'$detmetric/db2_connection' PASSING XMLPARSE(DOCUMENT CONNMETRICS.DETAILS)
as "detmetric"
COLUMNS "APPLICATION_HANDLE" INTEGER PATH 'application_handle',
"ROWS_RETURNED" BIGINT PATH 'system_metrics/rows_returned',
"TCPIP_SEND_VOLUME" BIGINT PATH 'system_metrics/tcpip_send_volume'
) AS DETMETRICS
ORDER BY rows_returned DESC
The following example is a sample output from this query.
APPLICATION_HANDLE ROWS_RETURNED TCPIP_SEND_VOLUME
------------------ -------------------- --------------------
21 4 0
1 record(s) selected.
Metrics are rolled up at unit of work boundaries, and periodically during the execution of requests. Therefore, the values reported by this table function reflect the current state of the system at the time of the most recent rollup. Metrics are strictly increasing in value. To determine the value of a given metric for an interval of time, use the MON_GET_CONNECTION_DETAILS table function to query the metric at the start and end of the interval, and compute the difference.
Request metrics are controlled through the COLLECT REQUEST METRICS clause on service superclasses and the mon_req_metrics database configuration parameter at the database level. Metrics are only collected for a request if the request is processed by an agent in a service subclass whose parent service superclass has request metrics enabled, or if request metrics collection is enabled for the entire database. By default, request metrics are enabled are enabled at the database level. If request metrics are disabled at the database level, and for a service superclass, the metrics reported for each connection mapped to that service superclass stop increasing (or remain at 0 if request metrics were disabled at database activation time).
The MON_GET_CONNECTION_DETAILS table function returns one row of data per connection and per member. No aggregation across members (for a service class or more) is performed. However, aggregation can be achieved through SQL queries.
The schema for the XML document that is returned in the DETAILS column is available in the file sqllib/misc/DB2MonRoutines.xsd. Further details can be found in the file sqllib/misc/DB2MonCommon.xsd.
Column Name | Data Type | Description |
---|---|---|
APPLICATION_HANDLE | BIGINT | application_handle - Application handle |
MEMBER | SMALLINT | member- Database member |
DETAILS | BLOB(1M) | XML document containing detailed metrics for the unit of work. See Table 2 for a description of the elements in this document. |
<db2_connection xmlns="http://www.ibm.com/xmlns/prod/db2/mon" release="90700000">
<application_handle>21</application_handle>
<member>0</member>
<system_metrics release="90700000">
<act_aborted_total>5</act_aborted_total>
...
<wlm_queue_assignments_total>3</wlm_queue_assignments_total>
</system_metrics>
</db2_connection>
For the full schema, see sqllib/misc/DB2MonRoutines.xsd.<xs:simpleType name="db2DbObjectString">
<xs:restriction base="xs:string">
<xs:maxLength value="128"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="db2PartitionNum">
<xs:restriction base="xs:nonNegativeInteger">
<xs:maxInclusive value="999"/>
</xs:restriction>
</xs:simpleType>
Element Name | Data Type | Description |
---|---|---|
act_aborted_total | xs:nonNegativeInteger | act_aborted_total - Total aborted activities |
act_completed_total | xs:nonNegativeInteger | act_completed_total - Total completed activities |
act_rejected_total | xs:nonNegativeInteger | act_rejected_total - Total rejected activities |
act_rqsts_total | xs:nonNegativeInteger | |
agent_wait_time | xs:nonNegativeInteger | agent_wait_time - Agent wait time |
agent_waits_total | xs:nonNegativeInteger | agent_waits_total - Total agent waits |
application_handle | xs:nonNegativeInteger | application_handle - Application handle |
application_id | xs:string | |
application_name | xs:string | |
app_rqsts_completed_total | xs:nonNegativeInteger | app_rqsts_completed_total - Total application requests completed |
audit_events_total | xs:nonNegativeInteger | audit_events_total - Total audit events |
audit_subsystem_wait_time | xs:nonNegativeInteger | audit_subsystem_wait_time - Audit subsystem wait time |
audit_subsystem_waits_total | xs:nonNegativeInteger | audit_subsystem_waits_total - Total audit subsystem waits |
audit_file_write_wait_time | xs:nonNegativeInteger | audit_file_write_wait_time - Audit file write wait time |
audit_file_writes_total | xs:nonNegativeInteger | audit_file_writes_total - Total Audit files written |
cat_cache_inserts | xs:nonNegativeInteger | cat_cache_inserts - Catalog cache inserts |
cat_cache_lookups | xs:nonNegativeInteger | cat_cache_lookups - Catalog cache lookups |
client_acctng | xs:string (255) | CURRENT CLIENT_ACCTNG special register |
client_applname | xs:string (255) | CURRENT CLIENT_APPLNAME special register |
client_hostname | xs:string | |
client_idle_wait_time | xs:nonNegativeInteger | client_idle_wait_time - Client idle wait time |
client_pid | xs:nonNegativeInteger | client_pid - Client process ID |
client_platform | xs:string | client_platform - Client platform |
client_port_number | xs:nonNegativeInteger | |
client_prdid | xs:string | client_prdid - Client product and version ID |
client_protocol | xs:string | client_protocol - Client communication protocol |
client_userid | xs:string (255) | CURRENT CLIENT_USERID special register |
client_wrkstnname | xs:string (255) | CURRENT CLIENT_WRKSTNNAME special register |
connection_start_time | xs:dateTime | |
coord_member | xs:short | coord_member - Coordinating member |
deadlocks | xs:nonNegativeInteger | deadlocks - Deadlocks detected |
diaglog_writes_total | xs:nonNegativeInteger | diaglog_writes_total - Diag log total writes |
diaglog_write_wait_time | xs:nonNegativeInteger | diaglog_write_wait_time - Diag log write time |
direct_read_time | xs:nonNegativeInteger | direct_read_time - Direct read time |
direct_write_time | xs:nonNegativeInteger | direct_write_time - Direct write time |
direct_read_reqs | xs:nonNegativeInteger | direct_read_reqs - Direct read requests |
direct_reads | xs:nonNegativeInteger | direct_reads - Direct reads from database |
direct_write_reqs | xs:nonNegativeInteger | direct_write_reqs - Direct write requests |
direct_writes | xs:nonNegativeInteger | direct_writes - Direct writes to database |
fcm_recv_volume | xs:nonNegativeInteger | fcm_recv_volume - FCM recv volume |
fcm_recv_wait_time | xs:nonNegativeInteger | fcm_recv_wait_time - FCM recv wait time |
fcm_recvs_total | xs:nonNegativeInteger | fcm_recvs_total - FCM recvs total |
fcm_message_recv_volume | xs:nonNegativeInteger | fcm_message_recv_volume - FCM message recv volume |
fcm_message_recvs_total | xs:nonNegativeInteger | fcm_message_recvs_total - FCM message recvs total |
fcm_message_recv_wait_time | xs:nonNegativeInteger | fcm_message_recv_wait_time - FCM message recv wait time |
fcm_message_send_volume | xs:nonNegativeInteger | fcm_message_send_volume - FCM message send volume |
fcm_message_send_wait_time | xs:nonNegativeInteger | fcm_message_send_wait_time - FCM message send wait time |
fcm_message_sends_total | xs:nonNegativeInteger | fcm_message_sends_total - FCM message sends total |
fcm_send_volume | xs:nonNegativeInteger | fcm_send_volume - FCM send volume |
fcm_send_wait_time | xs:nonNegativeInteger | fcm_send_wait_time - FCM send wait time |
fcm_sends_total | xs:nonNegativeInteger | fcm_sends_total - FCM sends total |
fcm_tq_recv_wait_time | xs:nonNegativeInteger | fcm_tq_recv_wait_time - FCM tablequeue recv wait time |
fcm_tq_send_wait_time | xs:nonNegativeInteger | fcm_tq_send_wait_time - FCM tablequeue send wait time |
fcm_tq_recv_volume | xs:nonNegativeInteger | fcm_tq_recv_volume - FCM tablequeue recv volume |
fcm_tq_recvs_total | xs:nonNegativeInteger | fcm_tq_recvs_total - FCM tablequeue recvs total |
fcm_tq_send_volume | xs:nonNegativeInteger | fcm_tq_send_volume - FCM tablequeue send volume |
fcm_tq_sends_total | xs:nonNegativeInteger | fcm_tq_sends_total - FCM tablequeue send total |
ida_send_wait_time | xs:nonNegativeInteger | ida_send_wait_time - Time spent waiting to send data |
ida_sends_total | xs:nonNegativeInteger | ida_sends_total - Number of times data sent |
ida_send_volume | xs:nonNegativeInteger | ida_send_volume - Total data volume sent |
ida_recv_volume | xs:nonNegativeInteger | ida_recv_volume - Total data volume received |
ida_recv_wait_time | xs:nonNegativeInteger | ida_recv_wait_time - Time spent waiting to receive data |
ida_recvs_total | xs:nonNegativeInteger | ida_recvs_total - Number of times data received |
int_commits | xs:nonNegativeInteger | int_commits - Internal commits |
int_rollbacks | xs:nonNegativeInteger | |
ipc_recv_volume | xs:nonNegativeInteger | ipc_recv_volume - Interprocess communication recv volume |
ipc_recv_wait_time | xs:nonNegativeInteger | ipc_recv_wait_time - Interprocess communication recv wait time |
ipc_recvs_total | xs:nonNegativeInteger | ipc_recvs_total - Interprocess communication recvs total |
ipc_send_volume | xs:nonNegativeInteger | ipc_send_volume - Interprocess communication send volume |
ipc_send_wait_time | xs:nonNegativeInteger | ipc_send_wait_time - Interprocess communication send wait time |
ipc_sends_total | xs:nonNegativeInteger | ipc_sends_total - Interprocess communication send total |
last_executable_id | xs:hexBinary(32) | |
last_request_type | xs:string(32) | |
lock_escals | xs:nonNegativeInteger | lock_escals - Number of lock escalations |
lock_timeouts | xs:nonNegativeInteger | lock_timeouts - Number of lock timeouts |
lock_wait_time | xs:nonNegativeInteger | lock_wait_time - Time waited on locks |
lock_waits | xs:nonNegativeInteger | lock_waits - Lock waits |
log_buffer_wait_time | xs:nonNegativeInteger | log_buffer_wait_time - Log buffer wait time |
log_disk_wait_time | xs:nonNegativeInteger | log_disk_wait_time - Log disk wait time |
log_disk_waits_total | xs:nonNegativeInteger | log_disk_waits_total - Log disk waits total |
member | xs:nonNegativeInteger | member- Database member |
num_locks_held | xs:nonNegativeInteger | |
num_log_buffer_full | xs:nonNegativeInteger | num_log_buffer_full - Number of full log buffers |
num_lw_thresh_exceeded | xs:nonNegativeInteger | |
pkg_cache_inserts | xs:nonNegativeInteger | pkg_cache_inserts - Package cache inserts |
pkg_cache_lookups | xs:nonNegativeInteger | pkg_cache_lookups - Package cache lookups |
pool_data_l_reads | xs:nonNegativeInteger | pool_data_l_reads - Buffer pool data logical reads |
pool_data_p_reads | xs:nonNegativeInteger | pool_data_p_reads - Buffer pool data physical reads |
pool_data_writes | xs:nonNegativeInteger | pool_data_writes - Buffer pool data writes |
pool_index_l_reads | xs:nonNegativeInteger | pool_index_l_reads - Buffer pool index logical reads |
pool_index_p_reads | xs:nonNegativeInteger | pool_index_p_reads - Buffer pool index physical reads |
pool_index_writes | xs:nonNegativeInteger | pool_index_writes - Buffer pool index writes |
pool_read_time | xs:nonNegativeInteger | pool_read_time - Total buffer pool physical read time |
pool_temp_data_l_reads | xs:nonNegativeInteger | pool_temp_data_l_reads - Buffer pool temporary data logical reads |
pool_temp_data_p_reads | xs:nonNegativeInteger | pool_temp_data_p_reads - Buffer pool temporary data physical reads |
pool_temp_index_l_reads | xs:nonNegativeInteger | pool_temp_index_l_reads - Buffer pool temporary index logical reads |
pool_temp_index_p_reads | xs:nonNegativeInteger | pool_temp_index_p_reads - Buffer pool temporary index physical reads |
pool_temp_xda_l_reads | xs:nonNegativeInteger | pool_temp_xda_l_reads - Buffer pool temporary XDA data logical reads |
pool_temp_xda_p_reads | xs:nonNegativeInteger | pool_temp_xda_p_reads - Buffer pool temporary XDA data physical reads |
pool_write_time | xs:nonNegativeInteger | pool_write_time - Total buffer pool physical write time |
pool_xda_l_reads | xs:nonNegativeInteger | pool_xda_l_reads - Buffer pool XDA data logical reads |
pool_xda_p_reads | xs:nonNegativeInteger | pool_xda_p_reads - Buffer pool XDA data physical reads |
pool_xda_writes | xs:nonNegativeInteger | pool_xda_writes - Buffer pool XDA data writes |
post_shrthreshold_sorts | xs:nonNegativeInteger | post_shrthreshold_sorts - Post shared threshold sorts |
post_threshold_sorts | xs:nonNegativeInteger | post_threshold_sorts - Post threshold sorts |
rows_modified | xs:nonNegativeInteger | rows_modified - Rows modified |
rows_read | xs:nonNegativeInteger | rows_read - Rows read |
rows_returned | xs:nonNegativeInteger | rows_returned - Rows returned |
rqsts_completed_total | xs:nonNegativeInteger | rqsts_completed_total - Total requests completed |
session_auth_id | xs:string | session_auth_id - Session authorization ID |
sort_overflows | xs:nonNegativeInteger | sort_overflows - Sort overflows |
system_auth_id | xs:string | |
tcpip_recv_volume | xs:nonNegativeInteger | tcpip_recv_volume - TCP/IP received volume |
tcpip_recv_wait_time | xs:nonNegativeInteger | tcpip_recv_wait_time - TCP/IP recv wait time |
tcpip_recvs_total | xs:nonNegativeInteger | tcpip_recvs_total - TCP/IP recvs total |
tcpip_send_volume | xs:nonNegativeInteger | tcpip_send_volume - TCP/IP send volume |
tcpip_send_wait_time | xs:nonNegativeInteger | tcpip_send_wait_time - TCP/IP send wait time |
tcpip_sends_total | xs:nonNegativeInteger | tcpip_sends_total - TCP/IP sends total |
thresh_violations | xs:nonNegativeInteger | thresh_violations - Number of threshold violations |
total_act_time | xs:nonNegativeInteger | total_act_time - Total activity time |
total_act_wait_time | xs:nonNegativeInteger | total_act_wait_time - Total activity wait time |
total_app_commits | xs:nonNegativeInteger | |
total_app_rollbacks | xs:nonNegativeInteger | |
total_app_rqst_time | xs:nonNegativeInteger | total_app_rqst_time - Total application request time |
total_app_section_executions | xs:nonNegativeInteger | total_app_section_executions - Total section executions |
total_commit_proc_time | xs:nonNegativeInteger | |
total_commit_time | xs:nonNegativeInteger | |
total_compilations | xs:nonNegativeInteger | |
total_compile_proc_time | xs:nonNegativeInteger | |
total_compile_time | xs:nonNegativeInteger | |
total_cpu_time | xs:nonNegativeInteger | total_cpu_time - Total CPU time |
total_implicit_compilations | xs:nonNegativeInteger | |
total_implicit_compile_ |
xs:nonNegativeInteger | total_implicit_compile_proc_time - Total implicit compile processing time |
total_implicit_compile_time | xs:nonNegativeInteger | |
total_loads | xs:nonNegativeInteger | |
total_load_proc_time | xs:nonNegativeInteger | |
total_load_time | xs:nonNegativeInteger | |
total_reorgs | xs:nonNegativeInteger | |
total_reorg_proc_time | xs:nonNegativeInteger | total_reorg_proc_time - Total reorganization processing time |
total_reorg_time | xs:nonNegativeInteger | |
total_rollback_proc_time | xs:nonNegativeInteger | |
total_rollback_time | xs:nonNegativeInteger | |
total_routine_invocations | xs:nonNegativeInteger | total_routine_invocations - Total routine invocations |
total_routine_user_code |
xs:nonNegativeInteger | total_routine_user_code_proc_time - Total routine user code processing time |
total_routine_user_code_time | xs:nonNegativeInteger | total_routine_user_code_time - Total routine user code time |
total_routine_time | xs:nonNegativeInteger | total_routine_time - Total routine time |
total_rqst_time | xs:nonNegativeInteger | total_rqst_time - Total request time |
total_runstats | xs:nonNegativeInteger | |
total_runstats_proc_time | xs:nonNegativeInteger | total_runstats_proc_time - Total runtime statistics processing time |
total_runstats_time | xs:nonNegativeInteger | |
total_section_proc_time | xs:nonNegativeInteger | total_section_proc_time - Total section processing time |
total_section_time | xs:nonNegativeInteger | total_section_time - Total section time |
total_wait_time | xs:nonNegativeInteger | total_wait_time - Total wait time |
total_section_sort_time | xs:nonNegativeInteger | total_section_sort_time - Total section sort time |
total_section_sort_proc_time | xs:nonNegativeInteger | total_section_sort_proc_time - Total section sort processing time |
total_section_sorts | xs:nonNegativeInteger | total_section_sorts - Total section sorts |
total_sorts | xs:nonNegativeInteger | total_sorts - Total Sorts |
tq_tot_send_spills | xs:nonNegativeInteger | tq_tot_send_spills - Total number of table queue buffers overflowed |
wlm_queue_time_total | xs:nonNegativeInteger | wlm_queue_time_total - Workload manager total queue time |
wlm_queue_assignments_total | xs:nonNegativeInteger | wlm_queue_assignments_total - Workload manager total queue assignments |