DB2 Version 10.1 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.

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

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Example

To determine where time is being spent by your application within the DB2® database manager, 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 is an example of 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
WLM_QUEUE_TIME_TOTAL	TOTAL_WAIT_TIME	wlm_queue_time_total - Workload manager total queue time
FCM_TQ_RECV_WAIT_TIME	FCM_RECV_WAIT_TIME	fcm_tq_recv_wait_time - FCM table queue received wait time
FCM_MESSAGE_RECV_WAIT_TIME	FCM_RECV_WAIT_TIME	fcm_message_recv_wait_time - FCM message received wait time
FCM_TQ_SEND_WAIT_TIME	FCM_SEND_WAIT_TIME	fcm_tq_send_wait_time - FCM table queue send wait time
FCM_MESSAGE_SEND_WAIT_TIME	FCM_SEND_WAIT_TIME	fcm_message_send_wait_time - FCM message send wait time
AGENT_WAIT_TIME	TOTAL_WAIT_TIME	agent_wait_time - Agent wait time
LOCK_WAIT_TIME	TOTAL_WAIT_TIME	lock_wait_time - Time waited on locks
DIRECT_READ_TIME	TOTAL_WAIT_TIME	direct_read_time - Direct read time
DIRECT_WRITE_TIME	TOTAL_WAIT_TIME	direct_write_time - Direct write 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
TCPIP_RECV_WAIT_TIME	TOTAL_WAIT_TIME	tcpip_recv_wait_time - TCP/IP received wait time
CLIENT_IDLE_WAIT_TIME	NULL	client_idle_wait_time - Client idle wait time
IPC_RECV_WAIT_TIME	TOTAL_WAIT_TIME	ipc_recv_wait_time - Interprocess communication received wait time
IPC_SEND_WAIT_TIME	TOTAL_WAIT_TIME	ipc_send_wait_time - Interprocess communication send wait time
TCPIP_SEND_WAIT_TIME	TOTAL_WAIT_TIME	tcpip_send_wait_time - TCP/IP send wait time
POOL_WRITE_TIME	TOTAL_WAIT_TIME	pool_write_time - Total buffer pool physical write time
POOL_READ_TIME	TOTAL_WAIT_TIME	pool_read_time - Total buffer pool physical read 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 - Diagnostic log file write wait 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 received wait time
TOTAL_WAIT_TIME	TOTAL_RQST_TIME	total_wait_time - Total wait time
LOCK_WAIT_TIME_GLOBAL	LOCK_WAIT_TIME	lock_wait_time_global - Lock wait time global
RECLAIM_WAIT_TIME	TOTAL_WAIT_TIME	reclaim_wait_time - Reclaim wait time
SPACEMAPPAGE_RECLAIM_WAIT_TIME	TOTAL_WAIT_TIME	spacemappage_reclaim_wait_time - Space map page reclaim wait time
CF_WAIT_TIME	TOTAL_WAIT_TIME	cf_wait_time - cluster caching facility wait time
EVMON_WAIT_TIME	TOTAL_WAIT_TIME	evmon_wait_time - Event monitor wait time
TOTAL_EXTENDED_LATCH_WAIT_TIME	TOTAL_WAIT_TIME	total_extended_latch_wait_time - Total extended latch wait time
PREFETCH_WAIT_TIME	TOTAL_WAIT_TIME	prefetch_wait_time - Time waited for prefetch
COMM_EXIT_WAIT_TIME	TOTAL_WAIT_TIME	comm_exit_wait_time - Communication buffer exit wait time monitor element
TOTAL_SECTION_SORT_PROC_TIME	TOTAL_SECTION_PROC_TIME	total_section_sort_proc_time - Total section sort processing 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_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_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_STATS_FABRICATION_PROC_TIME	TOTAL_COMPILE_PROC_TIME	total_stats_fabrication_proc_time - Total statistics fabrication processing time
TOTAL_SYNC_RUNSTATS_PROC_TIME	TOTAL_COMPILE_PROC_TIME	total_sync_runstats_proc_time - Total synchronous RUNSTATS processing time
TOTAL_CONNECT_REQUEST_PROC_TIME	TOTAL_RQST_TIME	total_connect_request_proc_time - Total connection or switch user request processing time
TOTAL_CONNECT_AUTHENTICATION_PROC_TIME	TOTAL_CONNECT_REQUEST_PROC_TIME	total_connect_authentication_proc_time - Total connection authentication processing time
TOTAL_RQST_TIME	NULL	total_rqst_time - Total request time
IDA_SEND_WAIT_TIME	TOTAL_WAIT_TIME	The total amount of time spent waiting to send data to an in-database analytics process.
IDA_RECV_WAIT_TIME	TOTAL_WAIT_TIME	The total amount of time spent waiting to receive data from an in-database analytics process.

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
FCM_TQ_RECV_WAIT_TIME	FCM_RECV_WAIT_TIME	fcm_tq_recv_wait_time - FCM table queue received wait time
FCM_MESSAGE_RECV_WAIT_TIME	FCM_RECV_WAIT_TIME	fcm_message_recv_wait_time - FCM message received wait time
FCM_TQ_SEND_WAIT_TIME	FCM_SEND_WAIT_TIME	fcm_tq_send_wait_time - FCM table queue send wait time
FCM_MESSAGE_SEND_WAIT_TIME	FCM_SEND_WAIT_TIME	fcm_message_send_wait_time - FCM message send wait time
LOCK_WAIT_TIME	TOTAL_ACT_WAIT_TIME	lock_wait_time - Time waited on locks
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
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
POOL_WRITE_TIME	TOTAL_ACT_WAIT_TIME	pool_write_time - Total buffer pool physical write time
POOL_READ_TIME	TOTAL_ACT_WAIT_TIME	pool_read_time - Total buffer pool physical read 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 - Diagnostic log file write wait 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 received wait time
TOTAL_ACT_WAIT_TIME	STMT_EXEC_TIME	total_act_wait_time - Total activity wait time
LOCK_WAIT_TIME_GLOBAL	LOCK_WAIT_TIME	lock_wait_time_global - Lock wait time global
RECLAIM_WAIT_TIME	TOTAL_ACT_WAIT_TIME	reclaim_wait_time - Reclaim wait time
SPACEMAPPAGE_RECLAIM_WAIT_TIME	TOTAL_ACT_WAIT_TIME	spacemappage_reclaim_wait_time - Space map page reclaim wait time
CF_WAIT_TIME	TOTAL_ACT_WAIT_TIME	cf_wait_time - cluster caching facility wait time
EVMON_WAIT_TIME	TOTAL_ACT_WAIT_TIME	evmon_wait_time - Event monitor wait time
TOTAL_EXTENDED_LATCH_WAIT_TIME	TOTAL_ACT_WAIT_TIME	total_extended_latch_wait_time - Total extended latch wait time
PREFETCH_WAIT_TIME	TOTAL_ACT_WAIT_TIME	prefetch_wait_time - Time waited for prefetch
WLM_QUEUE_TIME_TOTAL	NULL	wlm_queue_time_total - Workload manager total queue time
TOTAL_SECTION_SORT_PROC_TIME	TOTAL_SECTION_PROC_TIME	total_section_sort_proc_time - Total section sort processing time
TOTAL_ROUTINE_NON_SECT_PROC_TIME	STMT_EXEC_TIME	total_routine_non_sect_proc_time - Non-section processing time
TOTAL_SECTION_PROC_TIME	STMT_EXEC_TIME	total_section_proc_time - Total 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
STMT_EXEC_TIME	NULL	stmt_exec_time - Statement execution time
IDA_SEND_WAIT_TIME	TOTAL_WAIT_TIME	The total amount of time spent waiting to send data to an in-database analytics process.
IDA_RECV_WAIT_TIME	TOTAL_WAIT_TIME	The total amount of time spent waiting to receive data from an in-database analytics process.