MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics

Syntax

>>-MON_FORMAT_XML_METRICS_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_METRICS_BY_ROW function.

Example

This example shows how to call the MON_FORMAT_XML_METRICS _BY_ROW table function to return row-based formatted information from the XML document produced by the MON_GET_WORKLOAD_DETAILS table function.

SELECT SUBSTR(TFXML.WORKLOAD_NAME, 1, 13) AS WORKLOAD_NAME,
       SUBSTR(METRICS.METRIC_NAME, 1, 25) AS METRIC_NAME,
       METRICS.VALUE
   FROM 
     TABLE( MON_GET_WORKLOAD_DETAILS( NULL, -2 ) ) AS TFXML,
     TABLE( MON_FORMAT_XML_METRICS_BY_ROW( TFXML.DETAILS )) AS METRICS 
ORDER BY METRICS.VALUE DESC

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

WORKLOAD_NAME METRIC_NAME               VALUE
------------- ------------------------- ---------------
PAYROLL       ACT_COMPLETED_TOTAL                    15
FINANCE       ACT_COMPLETED_TOTAL                    12
PAYROLL       LOCK_WAITS                              8
FINANCE       LOCK_WAITS                              5
FINANCE       DEADLOCKS                               3
PAYROLL       DEADLOCKS                               0

Information returned