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.

Authorization

One of the following authorities is required to run the routine:
  • EXECUTE privilege on the routine
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

Default PUBLIC privilege

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

Syntax

Read syntax diagramSkip visual syntax diagramMON_FORMAT_XML_TIMES_BY_ROW(xmldoc )

The schema is SYSPROC.

Routine 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.

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
AGENT_WAIT_TIME TOTAL_WAIT_TIME agent_wait_time - Agent 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
CF_WAIT_TIME TOTAL_WAIT_TIME cf_wait_time - cluster caching facility wait time
CLIENT_IDLE_WAIT_TIME NULL client_idle_wait_time - Client idle wait time
COMM_EXIT_WAIT_TIME TOTAL_WAIT_TIME comm_exit_wait_time - Communication buffer exit wait time monitor element
DIAGLOG_WRITE_WAIT_TIME TOTAL_WAIT_TIME diaglog_write_wait_time - Diagnostic log file write wait 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
EVMON_WAIT_TIME TOTAL_WAIT_TIME evmon_wait_time - Event monitor wait time
FED_WAIT_TIME TOTAL_WAIT_TIME fed_wait_time - Time spent by a federation server monitor element
FCM_MESSAGE_RECV_WAIT_TIME FCM_RECV_WAIT_TIME fcm_message_recv_wait_time - FCM message received wait time
FCM_MESSAGE_SEND_WAIT_TIME FCM_SEND_WAIT_TIME fcm_message_send_wait_time - FCM message send wait time
FCM_RECV_WAIT_TIME TOTAL_WAIT_TIME fcm_recv_wait_time - FCM received wait time
FCM_SEND_WAIT_TIME TOTAL_WAIT_TIME fcm_send_wait_time - FCM send wait time
FCM_TQ_RECV_WAIT_TIME FCM_RECV_WAIT_TIME fcm_tq_recv_wait_time - FCM table queue received wait time
FCM_TQ_SEND_WAIT_TIME FCM_SEND_WAIT_TIME fcm_tq_send_wait_time - FCM table queue send wait time
IDA_RECV_WAIT_TIME TOTAL_WAIT_TIME The total amount of time spent waiting to receive data from an in-database analytics process.
IDA_SEND_WAIT_TIME TOTAL_WAIT_TIME The total amount of time spent waiting to send data to an in-database analytics process.
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
LOCK_WAIT_TIME_GLOBAL LOCK_WAIT_TIME lock_wait_time_global - Lock wait time global
LOCK_WAIT_TIME TOTAL_WAIT_TIME lock_wait_time - Time waited on locks
LOB_PREFETCH_WAIT_TIME TOTAL_WAIT_TIME lob_prefetch_wait_time - Wait time for prefetcher for LOB read or write monitor element
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
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
PREFETCH_WAIT_TIME TOTAL_WAIT_TIME prefetch_wait_time - Time waited for prefetch
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
TCPIP_RECV_WAIT_TIME TOTAL_WAIT_TIME tcpip_recv_wait_time - TCP/IP received wait time
TCPIP_SEND_WAIT_TIME TOTAL_WAIT_TIME tcpip_send_wait_time - TCP/IP send wait time
TOTAL_BACKUP_PROC_TIME TOTAL_RQST_TIME total_backup_proc_time monitor element
TOTAL_COL_PROC_TIME TOTAL_SECTION_PROC_TIME total_col_proc_time - Total column-oriented processing time
TOTAL_COL_SYNOPSIS_PROC_TIME TOTAL_COL_PROC_TIME total_col_synopsis_proc_time - Total column-organized synopsis table processing time
TOTAL_COMMIT_PROC_TIME TOTAL_RQST_TIME total_commit_proc_time - Total commits processing time
TOTAL_COMPILE_PROC_TIME TOTAL_RQST_TIME total_compile_proc_time - Total compile processing time
TOTAL_CONNECT_AUTHENTICATION_PROC_TIME TOTAL_CONNECT_REQUEST_PROC_TIME total_connect_authentication_proc_time - Total connection authentication processing time
TOTAL_CONNECT_REQUEST_PROC_TIME TOTAL_RQST_TIME total_connect_request_proc_time - Total connection or switch user request processing time
TOTAL_EXTENDED_LATCH_WAIT_TIME TOTAL_WAIT_TIME total_extended_latch_wait_time - Total extended latch wait time
TOTAL_IMPLICIT_COMPILE_PROC_TIME TOTAL_RQST_TIME total_implicit_compile_proc_time - Total implicit compile processing time
TOTAL_INDEX_BUILD_PROC_TIME TOTAL_SECTION_PROC_TIME total_index_build_proc_time monitor element
TOTAL_LOAD_PROC_TIME TOTAL_RQST_TIME total_load_proc_time - Total load processing time
TOTAL_REORG_PROC_TIME TOTAL_RQST_TIME total_reorg_proc_time - Total reorganization 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_RQST_TIME NULL total_rqst_time - Total request time
TOTAL_RUNSTATS_PROC_TIME TOTAL_RQST_TIME total_runstats_proc_time - Total runtime statistics processing time
TOTAL_SECTION_PROC_TIME TOTAL_RQST_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_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_WAIT_TIME TOTAL_RQST_TIME total_wait_time - Total wait time
WLM_QUEUE_TIME_TOTAL TOTAL_WAIT_TIME wlm_queue_time_total - Workload manager total queue time
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
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
CF_WAIT_TIME TOTAL_ACT_WAIT_TIME cf_wait_time - cluster caching facility wait time
DIAGLOG_WRITE_WAIT_TIME TOTAL_ACT_WAIT_TIME diaglog_write_wait_time - Diagnostic log file write wait 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
EVMON_WAIT_TIME TOTAL_ACT_WAIT_TIME evmon_wait_time - Event monitor wait time
FED_WAIT_TIME TOTAL_ACT_ WAIT_TIME fed_wait_time - Time spent by a federation server monitor element
FCM_MESSAGE_RECV_WAIT_TIME FCM_RECV_WAIT_TIME fcm_message_recv_wait_time - FCM message received wait time
FCM_MESSAGE_SEND_WAIT_TIME FCM_SEND_WAIT_TIME fcm_message_send_wait_time - FCM message send wait time
FCM_RECV_WAIT_TIME TOTAL_ACT_WAIT_TIME fcm_recv_wait_time - FCM received wait time
FCM_SEND_WAIT_TIME TOTAL_ACT_WAIT_TIME fcm_send_wait_time - FCM send wait time
FCM_TQ_RECV_WAIT_TIME FCM_RECV_WAIT_TIME fcm_tq_recv_wait_time - FCM table queue received wait time
FCM_TQ_SEND_WAIT_TIME FCM_SEND_WAIT_TIME fcm_tq_send_wait_time - FCM table queue send wait time
IDA_RECV_WAIT_TIME TOTAL_WAIT_TIME The total amount of time spent waiting to receive data from an in-database analytics process.
IDA_SEND_WAIT_TIME TOTAL_WAIT_TIME The total amount of time spent waiting to send data to an in-database analytics process.
LOCK_WAIT_TIME_GLOBAL LOCK_WAIT_TIME lock_wait_time_global - Lock wait time global
LOCK_WAIT_TIME TOTAL_ACT_WAIT_TIME lock_wait_time - Time waited on locks
LOB_PREFETCH_WAIT_TIME TOTAL_ACT_WAIT_TIME lob_prefetch_wait_time - Wait time for prefetcher for LOB read or write monitor element
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_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
PREFETCH_WAIT_TIME TOTAL_ACT_WAIT_TIME prefetch_wait_time - Time waited for prefetch
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
STMT_EXEC_TIME NULL stmt_exec_time - Statement execution time
TOTAL_ACT_WAIT_TIME STMT_EXEC_TIME total_act_wait_time - Total activity wait time
TOTAL_COL_PROC_TIME TOTAL_SECTION_PROC_TIME total_col_proc_time - Total column-oriented processing time
TOTAL_EXTENDED_LATCH_WAIT_TIME TOTAL_ACT_WAIT_TIME total_extended_latch_wait_time - Total extended latch wait time
TOTAL_INDEX_BUILD_PROC_TIME TOTAL_SECTION_PROC_TIME total_index_build_proc_time monitor element
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
WLM_QUEUE_TIME_TOTAL NULL wlm_queue_time_total - Workload manager total queue time

Example

To determine where time is spent by your application, within the database manager, 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 output is an example result 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.