![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
In IBM DB2 9.7, both the activity and statistics event monitors have a DETAILS_XML monitor element that stores an XML document which contains a wealth of activity and system metrics.
For the activity event monitor, the DETAILS_XML element is collected in the
activity logical data group (event_activity).
For the statistics event monitor, the DETAILS_XML element is collected in
the scstats and
wlstats logical data groups
(event_wlstats and
event_scstats). The metrics contained in the
XML document include the new time spent metrics introduced in DB2 9.7,
which provide a breakdown of where DB2 is spending its time, as well as a
number of counters, and other types of monitor elements.
The structure of the XML document reported in the DETAILS_XML element is
defined in the DB2MonCommon.xsd schema under
the sqllib/misc directory. The DETAILS_XML element in the activity event
monitor contains a document where the root element (activity_metrics) is
of type activity_level_metrics, while the
DETAILS_XML element in the statistics event monitor contains a document
where the root element (system_metrics) is of
type system_level_metrics.
Refer to the schema file or the DB2 Information Center for details on the monitor elements that are available in these documents.
Capturing the monitor elements as an XML document provides you with maximum flexibility in exploiting the metrics. With the XML document, you can perform any number of operations, including the following:
- Generate reports easily by applying a style sheet to extract the metrics of interest in your format of choice, like text, html, and so on.
- Extract metrics in a row-based format that allows grouping and ranking
of related metrics. You can do this using one the following
procedures:
MON_FORMAT_XML_WAIT_TIME_BY_ROW,MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW,MON_FORMAT_XML_TIMES_BY_ROW, orMON_FROMAT_XML_METRICS_BY_ROW. For example, for a given statement, you could find the wait time that impacted the statement the most by using an SQL statement based on theMON_FORMAT_XML_WAIT_TIMES_BY_ROWfunction to list all the wait times that the statement encountered, ordered from highest to lowest in order. - Extract metrics in a column-based (relational) format using the
XMLTABLEfunction, which is useful when ranking across objects, for example, to find the top 10 CPU consuming statements or service classes.
Additionally, new monitor elements can be introduced in future releases of DB2 without requiring migration and/or updates of the event monitor tables, which reduces maintenance requirements for event monitors. As releases or fix packs introduce new monitoring elements, these elements will be present in the captured XML documents, even if they are provided elsewhere as well. Consumers of the documents can choose whether or not to expose the new elements.
One disadvantage of the XML representation is that it requires an
understanding of both the content of the XML document, and DB2 XML
formatting functions in order to effectively extract and make use of the
monitoring data. Although DB2 9.7 ships with built-in functions to extract
monitor elements from these XML documents in a row-based format, there are
no corresponding built-in functions to extract the monitoring information
in a relational (column-based) format. In cases where monitor information
is only available in an XML document, such as the statistics event
monitor, you are required to use the XMLTABLE
function to manually extract the monitoring data if you want to access it
in a relational format.
Simplifying relational access to XML event monitor data
A stored procedure called GENERATE_MON_UDFS is
provided with this article. It creates the following two new table
functions:
SYS_METRICS_TO_RELATIONALACT_METRICS_TO_RELATIONAL
These table functions take an XML document as input, and then output one
column for each metric that is described in the
DB2MonCommon.xsd file. Under the covers, they
are extracting the monitor elements from the input document using the
XMLTABLE functions. The
GENERATE_MON_UDFS stored procedure uses an XSLT
transform to generate the UDF definitions directly from the
DB2MonCommon.xsd file, ensuring that any
metrics present in the XML schema are represented as a column in the
corresponding table functions.
The GENERATE_MON_UDFS procedure is defined in
the genmon.db2 script accompanying this
article. To create the GENERATE_MON_UDFS
procedure, run the genmon.db2 script using the
following statement:
db2 -td@ -f genmon.db2
|
Create the relational access functions by invoking the procedure as follows, including passing as input the full pathname to the sqllib/misc directory as the single input argument:
CALL GENERATE_MON_UDFS( '/home/dbuser/sqllib/misc' )
|
The new table functions are created after the procedure executes. To list the output columns for the table functions, execute the following statements:
DESCRIBE SELECT * FROM TABLE(ACT_METRICS_TO_RELATIONAL(NULL)) AS T
DESCRIBE SELECT * FROM TABLE(SYS_METRICS_TO_RELATIONAL(NULL)) AS T
|
As described in the background section, new monitor elements may be added
on fix pack or release boundaries. The generated table functions only
report the monitor elements that were present in the
DB2MonCommon.xsd file at the time that the
GENERATE_MON_UDFS procedure was invoked. To
have any new monitor elements be handled by these table functions, simply
re-execute the GENERATE_MON_UDFS procedure. The
procedure will replace the existing
ACT_METRICS_TO_RELATIONAL and
SYS_METRICS_TO_RELATIONAL table functions with
new versions containing any new monitor elements that were introduced.
The following examples show how easy it is to use the table functions to
access monitor data in the DETAILS_XML event monitor element. The examples
assume that the you have created an activity event monitor called
A, and a statistics event monitor called
S, using statements like the following:
CREATE EVENT MONITOR A FOR ACTIVITIES WRITE TO TABLE
CREATE EVENT MONITOR S FOR STATISTICS WRITE TO TABLE
|
Example 1: Using the historical service class information
captured by the statistics event monitor, you will sort the service
classes by CPU consumption. You can find the CPU consumption for a service
class by looking at the total_cpu_time monitor
element in the DETAILS_XML document, captured in the
SCSTATS logical data group.
SELECT S.SERVICE_SUPERCLASS_NAME,
S.SERVICE_SUBCLASS_NAME,
SUM(T.TOTAL_CPU_TIME) TOT_CPU
FROM SCSTATS_S AS S, TABLE(SYS_METRICS_TO_RELATIONAL(S.DETAILS_XML)) AS T
GROUP BY S.SERVICE_SUPERCLASS_NAME, S.SERVICE_SUBCLASS_NAME ORDER BY TOT_CPU DESC
|
Example 2: You want to list the statement text for the top
10 CPU consuming activities captured by the activity event monitor. You
can find the CPU consumption for an activity by looking at the
total_cpu_time monitor element in the
DETAILS_XML document, captured in the ACTIVITY
logical data group.
WITH ACTIVITY_CPU AS (
SELECT A.APPL_ID,
A.UOW_ID,
A.ACTIVITY_ID,
SUM(T.TOTAL_CPU_TIME) AS TOTAL_CPU_TIME
FROM ACTIVITY_A AS A, TABLE(ACT_METRICS_TO_RELATIONAL(A.DETAILS_XML)) AS T
GROUP BY APPL_ID, UOW_ID, ACTIVITY_ID)
SELECT A.TOTAL_CPU_TIME,
A.APPL_ID,
A.UOW_ID,
A.ACTIVITY_ID,
S.STMT_TEXT
FROM ACTIVITY_CPU A, ACTIVITYSTMT_A AS S
WHERE A.APPL_ID = S.APPL_ID AND
A.UOW_ID = S.UOW_ID AND
A.ACTIVITY_ID = S.ACTIVITY_ID
ORDER BY A.TOTAL_CPU_TIME DESC
FETCH FIRST 10 ROWS ONLY
|
Example 3: You want to extract the metrics captured for each service class in the SCSTATS logical group into a relational table. This allows the metrics to be accessed repeatedly in relational format without having to parse the XML document on each access.
First create a table to store the metrics:
CREATE TABLE SCMETRICS_S AS
(SELECT S.STATISTICS_TIMESTAMP,
S.PARTITION_NUMBER,
S.SERVICE_SUPERCLASS_NAME,
S.SERVICE_SUBCLASS_NAME, S.SERVICE_CLASS_ID,
T.*
FROM SCSTATS_S AS S, TABLE(SYS_METRICS_TO_RELATIONAL(S.DETAILS_XML)) AS T)
WITH NO DATA
|
Then use the SYS_METRICS_TO_RELATIONAL table
function to extract the metrics from the DETAILS_XML element in the
SCSTATS logical group and to populate the table
that was created:
INSERT INTO SCMETRICS_S
(SELECT S.STATISTICS_TIMESTAMP,
S.PARTITION_NUMBER,
S.SERVICE_SUPERCLASS_NAME,
S.SERVICE_SUBCLASS_NAME,
S.SERVICE_CLASS_ID,
T.*
FROM SCSTATS_S AS S, TABLE(SYS_METRICS_TO_RELATIONAL(S.DETAILS_XML)) AS T)
|
As these examples demonstrate, you can access the monitor elements without knowing or caring that the elements were captured as an XML document. You don't need any understanding of the structure of the underlying XML documents. Access is strictly through relational interfaces.
This article has shown a procedure that you can use to automatically generate table functions that report event monitor elements captured as an XML document in a relational format. The generated table functions hide the details and structure of the underlying XML documents from the caller, and simplify the usage of the event monitor data for those who wish to access the monitor elements as a relational table. You can use these table functions to easily integrate the contents of the XML documents into SQL statements, or to move the contents of the XML documents to pure relational form by extracting them into a separate table.
I would like to acknowledge and thank Paul Bird for reviewing this article.
| Description | Name | Size | Download method |
|---|---|---|---|
| Sample DB2 scripts for this article | genmon.db2 | 10KB | HTTP |
Information about download methods
Learn
- Learn more in the DB2 for Linux, UNIX, and Windows Information Center.
- In the DB2 for Linux, UNIX, and Windows area on developerWorks, get the
resources you need to advance your DB2 skills.
- Stay current with developerWorks technical events
and webcasts focused on a variety of IBM products and IT industry
topics.
- Follow developerWorks on
Twitter.
Get products and technologies
- Get an evaluation
copy of DB2 for Linux, UNIX, and Windows.
Discuss
- Participate in the discussion forum.
- Get involved in the My developerWorks community.
Connect with other developerWorks users while exploring the
developer-driven blogs, forums, groups, and wikis.
Scott Walkty is a Software Developer on the DB2 Monitoring and Workload Manager teams. He is one of the original developers on the Workload Manager solution and has spent the last few years working on various monitor enhancements for DB2. He previously worked for five years on the DB2 Tools Team. Scott holds a Masters degree in Computer Science from the University of Manitoba.
