DB2 10.5 for Linux, UNIX, and Windows

MON_FORMAT_XML_WAIT_TIMES_BY_ROW - Get formatted row-based output for wait times

The MON_FORMAT_XML_WAIT_TIMES_BY_ROW table function returns formatted row-based output for the wait times contained in an XML metrics document.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-MON_FORMAT_XML_WAIT_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

None

Example

This example shows how to call the MON_FORMAT_XML_WAIT_TIMES_BY_ROW table function to return formatted row-based output from the XML document produced by the MON_GET_WORKLOAD_DETAILS table function. The output shows the metrics and their values for each workload.

SELECT SUBSTR(TFXML.WORKLOAD_NAME, 1, 13) AS WORKLOAD_NAME,
       SUBSTR(WAITS.METRIC_NAME, 1, 25) AS METRIC_NAME,
       WAITS.TOTAL_TIME_VALUE,
       WAITS.COUNT
   FROM 
     TABLE( MON_GET_WORKLOAD_DETAILS( NULL, -2 ) ) AS TFXML,
     TABLE( MON_FORMAT_XML_WAIT_TIMES_BY_ROW(                                  
                                             TFXML.DETAILS
                                            )) AS WAITS
   ORDER BY WAITS.TOTAL_TIME_VALUE DESC

The following is a partial listing of the output of this query.

WORKLOAD_NAME METRIC_NAME               TOTAL_TIME_VALUE    COUNT
------------- ------------------------- ------------------- ------
PAYROLL       CLIENT_IDLE_WAIT_TIME                 2193672    174
FINANCE       CLIENT_IDLE_WAIT_TIME                  738290     16
PAYROLL       DIRECT_READ_TIME                        67892     81
FINANCE       DIRECT_READ_TIME                        32343      8
FINANCE       LOCK_WAIT_TIME                           8463      3
PAYROLL       LOCK_WAIT_TIME                             55      1

Information returned

Table 1. Information returned for MON_FORMAT_XML_WAIT_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_WAIT_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
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_WAIT_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
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.