The MON_GET_UNIT_OF_WORK_DETAILS table function returns detailed metrics for one or more units of work.
>>-MON_GET_UNIT_OF_WORK_DETAILS--(------------------------------> >--application_handle--,--member--)----------------------------><
The schema is SYSPROC.
EXECUTE privilege on the MON_GET_UNIT_OF_WORK_DETAILS function.
Identify the units of work that are consuming the highest amount of CPU time on the system.
SELECT detmetrics.application_handle,
detmetrics.uow_id,
detmetrics.total_cpu_time,
detmetrics.app_rqsts_completed_total,
detmetrics.rqsts_completed_total
FROM TABLE(MON_GET_UNIT_OF_WORK_DETAILS(NULL,-2)) AS UOWMETRICS,
XMLTABLE (
XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'),
'$detmetric/db2_unit_of_work' PASSING
XMLPARSE(DOCUMENT UOWMETRICS.DETAILS)
as "detmetric"
COLUMNS
"APPLICATION_HANDLE" INTEGER PATH 'application_handle',
"UOW_ID" INTEGER PATH 'uow_id',
"TOTAL_CPU_TIME" INTEGER PATH 'system_metrics/total_cpu_time',
"APP_RQSTS_COMPLETED_TOTAL" INTEGER
PATH 'system_metrics/app_rqsts_completed_total',
"RQSTS_COMPLETED_TOTAL" INTEGER
PATH 'system_metrics/rqsts_completed_total'
) AS DETMETRICS
ORDER BY total_cpu_time DESC
The following example is a sample output from this query.
APPLICATION_HANDLE UOW_ID TOTAL_CPU_TIME ...
-------------------- ----------- -------------------- ...
46 5 27959 ...
1 record(s) selected.
Output for query (continued).
... APP_RQSTS_COMPLETED_TOTAL RQSTS_COMPLETED_TOTAL
... ------------------------- ---------------------
... 72 48
Metrics are rolled up periodically during the unit of work. 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_UNIT OF WORK_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 at the database level. If request metrics have been disabled at the database level, and for a service superclass, the metrics reported for each unit of work that are mapped to that service superclass stop increasing (or remain at 0 if request metrics were disabled at database activation time).
The MON_GET_UNIT_OF_WORK_DETAILS table function returns one row of data per unit of work and per member. No aggregation across units of work (on a member), or across members (for a service class or more), is performed. However, aggregation can be achieved through SQL queries. The input parameters have the effect of being ANDed together.
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 or corresponding monitor element |
---|---|---|
SERVICE_SUPERCLASS_NAME | VARCHAR(128) | service_superclass_name - Service superclass name |
SERVICE_SUBCLASS_NAME | VARCHAR(128) | service_subclass_name - Service subclass name |
SERVICE_CLASS_ID | INTEGER | service_class_id - Service class ID |
MEMBER | SMALLINT | member- Database member |
COORD_MEMBER | SMALLINT | coord_member - Coordinator member Database member for the coordinator partition of the given unit of work. |
APPLICATION_HANDLE | BIGINT | application_handle - Application handle |
WORKLOAD_NAME | VARCHAR(128) | workload_name - Workload name |
WORKLOAD_OCCURRENCE_ID | INTEGER | workload_occurrence_id - Workload occurrence identifier. This ID does not uniquely identify the workload occurrence unless it is coupled with the coordinator database partition number and the workload name. |
UOW_ID | INTEGER | uow_id - Unit of work ID |
DETAILS | BLOB(1M) | XML document that contains detailed metrics for the unit of work. See Table 2 for a description of the elements in this document. |
<db2_unit_of_work xmlns="http://www.ibm.com/xmlns/prod/db2/mon" release="90700000">
<service_superclass_name>SYSDEFAULTUSERCLASS</service_superclass_name>
<service_subclass_name>SYSDEFAULTSUBCLASS</service_subclass_name>
<service_class_id>13</service_class_id>
<workload_name>SYSDEFAULTUSERWORKLOAD</workload_name>
<member>0</member>
<coord_member>0</coord_member>
<application_handle>21</application_handle>
<workload_occurrence_id>1</workload_occurrence_id>
<uow_id>2</uow_id>
<workload_occurrence_state>UOWEXEC</workload_occurrence_state>
<system_metrics>
<act_aborted_total>5</act_aborted_total>
...
<wlm_queue_assignments_total>3</wlm_queue_assignments_total>
</system_metrics>
</db2_unit_of_work_metrics>
For the full schema, see sqllib/misc/DB2MonRoutines.xsd.Element Name | Data Type | Description or corresponding monitor element |
---|---|---|
service_superclass_name | xs:string (128) | service_superclass_name - Service superclass name |
service_subclass_name | xs:string (128) | service_subclass_name - Service subclass name |
service_class_id | xs:nonNegativeInteger | service_class_id - Service class ID |
workload_name | xs:string (128) | workload_name - Workload name |
member | xs:nonNegativeInteger | member- Database member |
coord_member | xs:nonNegativeInteger | coord_member - Coordinator member |
application_handle | xs:nonNegativeInteger | application_handle - Application handle |
application_id | xs:string | appl_id - Application ID |
workload_occurrence_id | xs:nonNegativeInteger | workload_occurrence_id - Workload occurrence identifier This ID does not uniquely identify the workload occurrence unless it is coupled with the coordinator member and the workload name. |
uow_id | xs:nonNegativeInteger | uow_id - Unit of work ID |
workload_occurrence_state | xs:string | workload_occurrence_state - Workload occurrence state |
client_userid | xs:string | CURRENT CLIENT_USERID special register |
client_wrkstnname | xs:string | CURRENT CLIENT_WRKSTNNAME special register |
client_applname | xs:string | CURRENT CLIENT_APPLNAME special register |
client_acctng | xs:string | CURRENT CLIENT_ACCTNG special register |
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 |
app_rqsts_completed_total | xs:nonNegativeInteger | app_rqsts_completed_total - Total application requests completed |
audit_events_total | xs:nonNetagiveIngteger | 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_hostname | xs:string | |
client_idle_wait_time | xs:nonNegativeInteger | client_idle_wait_time - Client idle wait time |
client_port_number | xs:nonNegativeInteger | |
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 | |
tq_tot_send_spills | xs:nonNegativeInteger | tq_tot_send_spills - Total number of tablequeue buffers overflowed |
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 |
num_locks_held | xs:nonNegativeInteger | |
num_lw_thresh_exceeded | xs:nonNegativeInteger | |
thresh_violations | xs:nonNegativeInteger | thresh_violations - Number of threshold violations |
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 |
num_log_buffer_full | xs:nonNegativeInteger | num_log_buffer_full - Number of full log buffers |
rqsts_completed_total | xs:nonNegativeInteger | rqsts_completed_total - Total requests completed |
rows_modified | xs:nonNegativeInteger | rows_modified - Rows modified |
rows_read | xs:nonNegativeInteger | rows_read - Rows read |
rows_returned | xs:nonNegativeInteger | rows_returned - Rows returned |
session_auth_id | xs:string | session_auth_id - Session authorization ID |
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 |
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_ proc_time | 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_time | xs:nonNegativeInteger | total_routine_time - Total routine time |
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_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_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_section_time | xs:nonNegativeInteger | total_section_time - Total section time |
total_sorts | xs:nonNegativeInteger | total_sorts - Total Sorts |
post_threshold_sorts | xs:nonNegativeInteger | post_threshold_sorts - Post threshold sorts |
post_shrthreshold_sorts | xs:nonNegativeInteger | post_shrthreshold_sorts - Post shared threshold sorts |
sort_overflows | xs:nonNegativeInteger | sort_overflows - Sort overflows |
tq_tot_send_spills | xs:nonNegativeInteger | tq_tot_send_spills - Total number of table queue buffers overflowed |
total_wait_time | xs:nonNegativeInteger | total_wait_time - Total wait time |
uow_log_space_used | xs:nonNegativeInteger | uow_log_space_used - Unit of Work Log Space Used |
uow_start_time | xs:dateTime | uow_start_time - Unit of Work Start Timestamp |
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 |