Monitoring in DB2 9.7, Part 2: Relational access to XML event monitor data in DB2 9.7

Learn how you can get relational access to the subset of monitor data that is captured in the form of an XML document by an activity or statistics event monitor in IBM® DB2® for Linux®, UNIX®, and Windows® Version 9.7 (DB2).

Share:

Scott Walkty (swalkty@ca.ibm.com), Software Developer, IBM

Scott WalktyScott 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.



24 November 2010

Also available in Chinese

Background

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, or MON_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 the MON_FORMAT_XML_WAIT_TIMES_BY_ROW function 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 XMLTABLE function, 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_RELATIONAL
  • ACT_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.


Examples

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.


Conclusion

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.


Acknowledgement

I would like to acknowledge and thank Paul Bird for reviewing this article.


Download

DescriptionNameSize
Sample DB2 scripts for this articlegenmon.db210KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=588114
ArticleTitle=Monitoring in DB2 9.7, Part 2: Relational access to XML event monitor data in DB2 9.7
publish-date=11242010