MON_FORMAT_XML_WAIT_TIMES_BY_ROW - Get formatted row-based output for wait times

Syntax

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

EXECUTE privilege on the MON_FORMAT_XML_WAIT_TIMES_BY_ROW function.

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