The MON_GET_SERVICE_SUBCLASS_DETAILS table function returns detailed metrics for one or more service subclasses.
>>-MON_GET_SERVICE_SUBCLASS_DETAILS--(--service_superclass_name--,--> >--service_subclass_name--,--member--)-------------------------><
The schema is SYSPROC.
EXECUTE privilege on the MON_GET_SERVICE_SUBCLASS_DETAILS function.
Display the total CPU time used and total number of requests processed for each service superclass, ordered by CPU usage in relational format (using XMLTABLE).
SELECT varchar(scmetrics.service_superclass_name,30) as service_superclass,
sum(detmetrics.total_cpu_time) as total_cpu,
sum(detmetrics.app_rqsts_completed_total) as total_rqsts
FROM TABLE(MON_GET_SERVICE_SUBCLASS_DETAILS('','',-2)) AS SCMETRICS,
XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'),
'$detmetric/db2_service_subclass'
PASSING XMLPARSE(DOCUMENT SCMETRICS.DETAILS)
as "detmetric"
COLUMNS "TOTAL_CPU_TIME" INTEGER PATH 'system_metrics/total_cpu_time',
"APP_RQSTS_COMPLETED_TOTAL" INTEGER PATH
'system_metrics/app_rqsts_completed_total')
AS DETMETRICS
GROUP BY service_superclass_name
ORDER BY total_cpu desc
The following example is a sample output from this query.
SERVICE_SUPERCLASS TOTAL_CPU TOTAL_RQSTS
------------------------------ -------------------- --------------------
SYSDEFAULTUSERCLASS 2428188 26
SYSDEFAULTMAINTENANCECLASS 0 0
SYSDEFAULTSYSTEMCLASS 0 0
3 record(s) selected.
Metrics are rolled up to a service class on 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_SERVICE_SUBCLASS_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 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_SERVICE_SUBCLASS_DETAILS table function returns one row of data per service subclass and per member. No aggregation across service classes (on a member), or across members (for a service class or more), is performed. However, aggregation can be achieved through SQL queries (see the example). The input parameters have the effect of being ANDed together. Therefore, if you specify conflicting input parameters (for example, a superclass name SUPA and subclass name SUBB that is not a subclass of SUPA), no rows are returned.
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 |
---|---|---|
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 |
DETAILS | BLOB(1M) | XML document that contains detailed metrics for the service class. See Table 2 for a description of the elements in this document. |
<db2_service_subclass xmlns="http://www.ibm.com/xmlns/prod/db2/mon" release="90700000">
<service_superclass_name>SYSDEFAULTSYSTEMCLASS</service_superclass_name>
<service_subclass_name>SYSDEFAULTSUBCLASS</service_subclass_name>
<service_subclass_id>11</service_subclass_id>
<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_service_subclass>
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 |
member | xs:nonNegativeInteger | member- Database member |
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: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_idle_wait_time | xs:nonNegativeInteger | client_idle_wait_time - Client idle wait time |
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 |
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_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 |
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 |
total_rqst_mapped_in | xs:nonNegativeInteger | total_rqst_mapped_in - Total request mapped-in |
total_rqst_mapped_out | xs:nonNegativeInteger | total_rqst_mapped_out - Total request mapped-out |
rows_modified | xs:nonNegativeInteger | rows_modified - Rows modified |
rows_read | xs:nonNegativeInteger | rows_read - Rows read |
rows_returned | xs:nonNegativeInteger | rows_returned - Rows returned |
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_time | xs:nonNegativeInteger | total_routine_time - Total routine time |
total_routine_user_ |
xs:nonNegativeInteger | total_routine_user_code_proc_time - Total routine user code processing time |
total_routine_user_ |
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_shrthreshold_sorts - Post shared 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 |
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 |