DB2 Version 9.7 for Linux, UNIX, and Windows

MON_FORMAT_XML_TIMES_BY_ROW - Get formatted row-based combined hierarchy wait and processing times

The MON_FORMAT_XML_TIMES_BY_ROW table function returns formatted row based output for the combined hierarchy of wait and processing times that are contained in an XML metrics document.

Note: If your database was created in Version 9.7 before Fix Pack 1, to run this routine you must have already run the db2updv97 command. If your database was created before Version 9.7, it is not necessary to run the db2updv97 command (because the catalog update is automatically taken care of by the database migration). If you downgrade to Version 9.7, this routine will no longer work.

Syntax


>>-MON_FORMAT_XML_TIMES_BY_ROW--(--xmldoc--)-------------------><

The schema is SYSPROC.

Table function parameters

xmldoc

An input argument of type BLOB(8M) that contains an XML document with either a system_metrics or activity_metrics element. XML documents with these elements can be obtained from the following sources:

Returned by one of the MON_GET_*_DETAILS table functions.
From the metrics column output by statistics and activity event monitors.
From the formatted output of the unit of work, or package cache event monitors.

Authorization

EXECUTE privilege on the MON_FORMAT_XML_TIMES_BY_ROW function.

Example

To determine where time is being spent by your application within the DB2® database manger, you can run the following query to show the combined wait and processing time metrics in the metrics hierarchy.

SELECT SUBSTR(T.SERVICE_SUPERCLASS_NAME,1,15) as SUPERCLASS,
       SUBSTR(T.SERVICE_SUBCLASS_NAME,1,15) as SUBCLASS,
       T.MEMBER,
       SUBSTR(U.METRIC_NAME, 1,15) AS METRIC_NAME,
       SUBSTR(U.PARENT_METRIC_NAME,1,15) AS PARENT_NAME,
       U.TOTAL_TIME_VALUE,
       U.COUNT
FROM
TABLE(MON_GET_SERVICE_SUBCLASS_DETAILS(NULL, NULL, -2)) AS T, 
TABLE(MON_FORMAT_XML_TIMES_BY_ROW(T.DETAILS)) AS U

The following example is a sample output from this query.

SUPERCLASS SUBCLASS MEMBER METRIC_NAME     PARENT_NAME     T..._VALUE COUNT
---------- -------- ------ --------------- --------------- ---------- -----
MYSC       MYSSC         0 FCM_MESSAGE_REC FCM_RECV_WAIT_T          0     0
MYSC       MYSSC         0 FCM_TQ_RECV_WAI FCM_RECV_WAIT_T          0     0
MYSC       MYSSC         0 FCM_MESSAGE_SEN FCM_SEND_WAIT_T          0     0
MYSC       MYSSC         0 FCM_TQ_SEND_WAI FCM_SEND_WAIT_T          0     0
MYSC       MYSSC         0 TOTAL_COMMIT_PR TOTAL_RQST_TIME        300     1
MYSC       MYSSC         0 TOTAL_COMPILE_P TOTAL_RQST_TIME        700     1
MYSC       MYSSC         0 TOTAL_IMPLICIT_ TOTAL_RQST_TIME          0     0
MYSC       MYSSC         0 TOTAL_LOAD_PROC TOTAL_RQST_TIME          0     0
MYSC       MYSSC         0 TOTAL_REORG_PRO TOTAL_RQST_TIME          0     0
MYSC       MYSSC         0 TOTAL_ROLLBACK_ TOTAL_RQST_TIME          0     0
MYSC       MYSSC         0 TOTAL_RUNSTATS_ TOTAL_RQST_TIME          0     0
MYSC       MYSSC         0 TOTAL_SECTION_P TOTAL_RQST_TIME       7322     1
MYSC       MYSSC         0 TOTAL_WAIT_TIME TOTAL_RQST_TIME          0     0
MYSC       MYSSC         0 TOTAL_SECTION_S TOTAL_SECTION_P          0     0
MYSC       MYSSC         0 AGENT_WAIT_TIME TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 AUDIT_FILE_WRIT TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 AUDIT_SUBSYSTEM TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 DIAGLOG_WRITE_W TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 DIRECT_READ_TIM TOTAL_WAIT_TIME       1204    17
MYSC       MYSSC         0 DIRECT_WRITE_TI TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 FCM_RECV_WAIT_T TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 FCM_SEND_WAIT_T TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 IPC_RECV_WAIT_T TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 IPC_SEND_WAIT_T TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 LOCK_WAIT_TIME  TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 LOG_BUFFER_WAIT TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 LOG_DISK_WAIT_T TOTAL_WAIT_TIME        523     2
MYSC       MYSSC         0 POOL_READ_TIME  TOTAL_WAIT_TIME       2432     7
MYSC       MYSSC         0 POOL_WRITE_TIME TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 TCPIP_RECV_WAIT TOTAL_WAIT_TIME        523     1
MYSC       MYSSC         0 TCPIP_SEND_WAIT TOTAL_WAIT_TIME        241     1
MYSC       MYSSC         0 WLM_QUEUE_TIME_ TOTAL_WAIT_TIME          0     0
MYSC       MYSSC         0 CLIENT_IDLE_WAI -                      234     -
MYSC       MYSSC         0 TOTAL_RQST_TIME -                    13245     1
  34 record(s) selected.

Information returned

Table 1. Information returned for MON_FORMAT_XML_TIMES_BY_ROW
Column Name	Data Type	Description
METRIC_NAME	VARCHAR(128)	The unique identifier for the total time metric value.
TOTAL_TIME_VALUE	BIGINT	The total time value in milliseconds corresponding to metric_name.
COUNT	BIGINT	Number of occurrences of this type of interval.
PARENT_METRIC_NAME	VARCHAR(128)	The identifier of the parent total time metric whose value contains the total_time_value as a subset.

XML documents that contain an element of type system_metrics are generated from the following interfaces:

MON_GET_CONNECTION_DETAILS
MON_GET_SERVICE_SUBCLASS_DETAILS
MON_GET_UNIT_OF_WORK_DETAILS
MON_GET_WORKLOAD_DETAILS
DETAILS_XML column from a STATISTICS event monitor
METRICS column produced by EVMON_FORMAT_UE_TO_TABLES for the UNIT OF WORK event monitor
XMLREPORT column of EVMON_FORMAT_UE_TO_XML for the UNIT OF WORK event monitor

See Table 2 for the types of metrics and their parent metrics that are returned from the XML document in this case:

Table 2. Metric names returned by MON_FORMAT_XML_TIMES_BY_ROW for XML documents containing a system_metrics element type
Metric Name	Parent Metric Name	Description of metric or Monitor element
TOTAL_RQST_TIME	NULL	total_rqst_time - Total request time
TOTAL_COMPILE_ PROC_TIME	TOTAL_RQST_TIME	total_compile_proc_time - Total compile processing time
TOTAL_IMPLICIT_ COMPILE_PROC_TIME	TOTAL_RQST_TIME	total_implicit_compile_proc_time - Total implicit compile processing time
TOTAL_SECTION_ PROC_TIME	TOTAL_RQST_TIME	total_section_proc_time - Total section processing time
TOTAL_COMMIT_ PROC_TIME	TOTAL_RQST_TIME	total_commit_proc_time - Total commits processing time
TOTAL_ROLLBACK _PROC_TIME	TOTAL_RQST_TIME	total_rollback_proc_time - Total rollback processing time
TOTAL_ROUTINE_USER _CODE_PROC_TIME	TOTAL_RQST_TIME	total_routine_user_code_proc_time - Total routine user code processing time
TOTAL_RUNSTATS_ PROC_TIME	TOTAL_RQST_TIME	total_runstats_proc_time - Total runtime statistics processing time
TOTAL_REORG _PROC_TIME	TOTAL_RQST_TIME	total_reorg_proc_time - Total reorganization processing time
TOTAL_LOAD_PROC_TIME	TOTAL_RQST_TIME	total_load_proc_time - Total load processing time
TOTAL_SECTION_ SORT_PROC_TIME	TOTAL_SECTION_ PROC_TIME	total_section_sort_proc_time - Total section sort processing time
TOTAL_WAIT_TIME	TOTAL_RQST_TIME	total_wait_time - Total wait time
CLIENT_IDLE_WAIT_TIME	NULL	client_idle_wait_time - Client idle wait time
POOL_READ_TIME	TOTAL_WAIT_TIME	pool_read_time - Total buffer pool physical read time
POOL_WRITE_TIME	TOTAL_WAIT_TIME	pool_write_time - Total buffer pool physical write time
DIRECT_READ_TIME	TOTAL_WAIT_TIME	direct_read_time - Direct read time
DIRECT_WRITE_TIME	TOTAL_WAIT_TIME	direct_write_time - Direct write time
LOCK_WAIT_TIME	TOTAL_WAIT_TIME	lock_wait_time - Time waited on locks
AGENT_WAIT_TIME	TOTAL_WAIT_TIME	agent_wait_time - Agent wait time
WLM_QUEUE_TIME_TOTAL	TOTAL_WAIT_TIME	wlm_queue_time_total - Workload manager total queue time
FCM_SEND_WAIT_TIME	TOTAL_WAIT_TIME	fcm_send_wait_time - FCM send wait time
FCM_RECV_WAIT_TIME	TOTAL_WAIT_TIME	fcm_recv_wait_time - FCM recv wait time
TCPIP_SEND_WAIT_TIME	TOTAL_WAIT_TIME	tcpip_send_wait_time - TCP/IP send wait time
TCPIP_RECV_WAIT_TIME	TOTAL_WAIT_TIME	tcpip_recv_wait_time - TCP/IP recv wait time
IPC_SEND_WAIT_TIME	TOTAL_WAIT_TIME	ipc_send_wait_time - Interprocess communication send wait time
IPC_RECV_WAIT_TIME	TOTAL_WAIT_TIME	ipc_recv_wait_time - Interprocess communication recv wait time
LOG_BUFFER_WAIT_TIME	TOTAL_WAIT_TIME	log_buffer_wait_time - Log buffer wait time
LOG_DISK_WAIT_TIME	TOTAL_WAIT_TIME	log_disk_wait_time - Log disk wait time
FCM_MESSAGE_ SEND_WAIT_TIME	FCM_SEND_ WAIT_TIME	fcm_message_send_wait_time - FCM message send wait time
FCM_MESSAGE_ RECV_WAIT_TIME	FCM_RECV_ WAIT_TIME	fcm_message_recv_wait_time - FCM message recv wait time
FCM_TQ_SEND_WAIT_TIME	FCM_SEND_ WAIT_TIME	fcm_tq_send_wait_time - FCM tablequeue send wait time
FCM_TQ_RECV_WAIT_TIME	FCM_RECV_ WAIT_TIME	fcm_tq_recv_wait_time - FCM tablequeue recv wait time
AUDIT_FILE_WRITE _WAIT_TIME	TOTAL_WAIT_TIME	audit_file_write_wait_time - Audit file write wait time
AUDIT_SUBSYSTEM _WAIT_TIME	TOTAL_WAIT_TIME	audit_subsystem_wait_time - Audit subsystem wait time
DIAGLOG_WRITE_ WAIT_TIME	TOTAL_WAIT_TIME	diaglog_write_wait_time - Diag log write time
IDA_RECV_WAIT_TIME	TOTAL_WAIT_TIME	ida_recv_wait_time - Time spent waiting to receive data
IDA_SEND_WAIT_TIME	TOTAL_WAIT_TIME	ida_send_wait_time - Time spent waiting to send data

XML documents that contain an element of type activity_metrics are generated from the following interfaces:

MON_GET_ACTIVITY_DETAILS
MON_GET_PKG_CACHE_STMT_DETAILS
DETAILS_XML column from an ACTIVITY event monitor
METRICS column produced by EVMON_FORMAT_UE_TO_TABLES for the PACKAGE CACHE event monitor
XMLREPORT column of EVMON_FORMAT_UE_TO_XML for the PACKAGE CACHE event monitor

See Table 3 for the types of metrics and their parent metrics that are returned from the XML document in this case:

Table 3. Metric names returned by MON_FORMAT_XML_TIMES_BY_ROW for XML documents containing an activity_metrics element type
Metric Name	Parent Metric Name	Description or Monitor element
STMT_EXEC_TIME	NULL	stmt_exec_time - Statement execution time
TOTAL_ROUTINE_NON _SECT_PROC_TIME	STMT_EXEC_TIME	total_routine_non_sect_proc_time - Non-section processing time
TOTAL_ROUTINE_USER _CODE_PROC_TIME	TOTAL_ROUTINE_NON _SECT_PROC_TIME	total_routine_user_code_proc_time - Total routine user code processing time
TOTAL_SECTION_ PROC_TIME	STMT_EXEC_TIME	total_section_proc_time - Total section processing time
TOTAL_SECTION_SORT PROC_TIME	TOTAL_SECTION_ PROC_TIME	total_section_sort_proc_time - Total section sort processing time
TOTAL_ACT_WAIT_TIME	STMT_EXEC_TIME	total_act_wait_time - Total activity wait time
WLM_QUEUE_TIME_TOTAL	NULL	wlm_queue_time_total - Workload manager total queue time
POOL_READ_TIME	TOTAL_ACT_ WAIT_TIME	pool_read_time - Total buffer pool physical read time
POOL_WRITE_TIME	TOTAL_ACT_ WAIT_TIME	pool_write_time - Total buffer pool physical write time
DIRECT_READ_TIME	TOTAL_ACT_ WAIT_TIME	direct_read_time - Direct read time
DIRECT_WRITE_TIME	TOTAL_ACT_ WAIT_TIME	direct_write_time - Direct write time
LOCK_WAIT_TIME	TOTAL_ACT_ WAIT_TIME	lock_wait_time - Time waited on locks
LOG_BUFFER_WAIT_TIME	TOTAL_ACT_ WAIT_TIME	log_buffer_wait_time - Log buffer wait time
LOG_DISK_WAIT_TIME	TOTAL_ACT_ WAIT_TIME	log_disk_wait_time - Log disk wait time
AUDIT_FILE_WRITE _WAIT_TIME	TOTAL_ACT_ WAIT_TIME	audit_file_write_wait_time - Audit file write wait time
AUDIT_SUBSYSTEM _WAIT_TIME	TOTAL_ACT_ WAIT_TIME	audit_subsystem_wait_time - Audit subsystem wait time
DIAGLOG_WRITE_ WAIT_TIME	TOTAL_ACT_ WAIT_TIME	diaglog_write_wait_time - Diag log write time
FCM_SEND_WAIT_TIME	TOTAL_ACT_ WAIT_TIME	fcm_send_wait_time - FCM send wait time
FCM_RECV_WAIT_TIME	TOTAL_ACT_ WAIT_TIME	fcm_recv_wait_time - FCM recv wait time
FCM_MESSAGE_ SEND_WAIT_TIME	FCM_SEND_ WAIT_TIME	fcm_message_send_wait_time - FCM message send wait time
FCM_MESSAGE_ RECV_WAIT_TIME	FCM_RECV_ WAIT_TIME	fcm_message_recv_wait_time - FCM message recv wait time
FCM_TQ_SEND_WAIT_TIME	FCM_SEND_ WAIT_TIME	fcm_tq_send_wait_time - FCM tablequeue send wait time
FCM_TQ_RECV_WAIT_TIME	FCM_RECV_ WAIT_TIME	fcm_tq_recv_wait_time - FCM tablequeue recv wait time
IDA_RECV_WAIT_TIME	TOTAL_WAIT_TIME	ida_recv_wait_time - Time spent waiting to receive data
IDA_SEND_WAIT_TIME	TOTAL_WAIT_TIME	ida_send_wait_time - Time spent waiting to send data