MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics

Authorization

Default PUBLIC privilege

None

Syntax

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

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