DB2 monitoring: Tracing SQL statements by using an activity event monitor

This article describes a technique to easily trace (capture) the SQL statements that a client application executes. This technique uses monitoring features in the IBM DB2® for Linux®, UNIX®, and Windows® software, Version 9.7 Fix Pack 4 and higher.

Share:

Scott Walkty (swalkty@ca.ibm.com), DB2 software development, IBM

Scott WalktyScott Walkty is a software developer on the DB2 Monitoring and Workload Manager team. 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 master's degree in computer science from the University of Manitoba.



Mike Springgay (springga@ca.ibm.com), Senior Architect, DB2 development, IBM  

Mike SpringgayMike Springgay is a Senior Architect within the DB2 for Linux, UNIX, and Windows development team. He joined the DB2 development team 16 years ago and is currently responsible for client server connectivity, routine infrastructure, monitor, and SQL Compatibility areas.



23 January 2014

Also available in Russian

Overview

DB2 Version 9.7 includes two features that facilitate tracing SQL statements:

  • Activity event monitors
  • The WLM_SET_CONN_ENV procedure

DB2 Version 9.5 introduced activity event monitors. You can use an activity event monitor to capture information about SQL statements that were executed on the database server. Information about statements is written to the activity event monitor when the statements complete execution and includes information such as the statement text, execution time, and compilation environment.

In a partitioned database environment, the table spaces containing the event monitor tables must exist on all partitions to capture information about the statements on each partition where they execute.

Before DB2 Version 9.7 Fix Pack 2, you could enable or disable the capture of statement information by an activity event monitor only by using the COLLECT ACTIVITY DATA clause on the CREATE or ALTER statements for a DB2 Workload Manager (WLM) object. For example, to capture information, including statement text, about all statements that execute in the SYSDEFAULTUSERCLASS service class, you might have issued the following statement:

ALTER SERVICE CLASS SYSDFAULTSUBCLASS UNDER SYSDEFAULTUSERCLASS COLLECT 
ACTIVITY DATA ON COORDINATOR WITH DETAILS

In DB2 Version 9.7 Fix Pack 2, the WLM_SET_CONN_ENV stored procedure was introduced as an alternative way to enable or disable the collection of activity information for a specific application. The procedure takes two arguments:

  • The application handle, which identifies the application for which to capture statement information. If you specify NULL, statement information is captured for the connection on which you executed the procedure.
  • Collection settings, which you specify as a set of name/value pairs by using XML tags.

For example, to enable the collection of information about SQL statements that are executed by an application that has application handle 52, you might issue the following statement:

CALL WLM_SET_CONN_ENV( 52, ‘<collectactdata>WITH DETAILS</collectactdata>’)

Similarly, to disable the collection of information about SQL statements that are executed by the application, you might issue the following statement:

CALL WLM_SET_CONN_ENV( 52, ‘<collectactdata>NONE</collectactdata>’)

You can use the WLM_SET_CONN_ENV procedure without a WLM license.

For more details on activity event monitors and the WLM_SET_CONN_ENV procedure, see the DB2 Information Center.


Tracing execution of SQL statements by using an activity event monitor

You can use an activity event monitor and the WLM_SET_CONN_ENV procedure to trace the execution of SQL statements. Create an activity event monitor, and enable or disable collection of statement information for any connection of interest by using the WLM_SET_CONN_ENV procedure.

The sqltrace.db2 script (see Downloads) that accompanies this article provides a sample implementation. To run the sqltrace.db2 script, issue the following command:

db2 –td@ -f sqltrace.db2

This script creates the following objects:

  • An activity event monitor (TRACE_EVMON)
  • A temporary table for traced data (TEMP_TRACE_DATA)
  • Trace capture procedures (TRACE_ON and TRACE_OFF)
  • A view on traced data (TRACE)

Objects are created in the SQLTRACE schema.

The TRACE_ON procedure takes an application handle as an optional input argument. This procedure turns on the TRACE_EVMON activity event monitor if it is not already turned on. This procedure also enables collection of statement information for the identified application (or the current connection if you do not provide an application handle) by using the WLM_SET_CONN_ENV procedure.

The TRACE_OFF procedure takes an application handle as an optional input argument. This procedure disables the collection of statement information for the identified application (or the current connection if you do not provide an application handle) by using the WLM_SET_CONN_ENV procedure. The TRACE_OFF procedure then extracts metrics and statement text from the activity event monitor and stores this information in the TEMP_TRACE_DATA temporary table. A temporary table is used to ensure that different users taking traces have their own views of traced data. That is, they see only the statements for the connections that they traced.

The extraction statement that the TRACE_OFF procedure issues uses recursive SQL to aggregate metrics for all child statements to the parent statement. As a result, the trace output can provide two types of metrics:

  • Per-statement metrics: For example, the amount of CPU time that the statement directly uses.
  • Aggregate metrics: For example, the amount of CPU time that the statement and all child statements use, which is relevant for SQL procedures.

The extraction statement also ensures that only statements that are issued by the application being traced are gathered from the event monitor tables. Activity event monitors capture any statement that is identified for collection. If two users enable traces for their connections at the same time, the event monitor captures information for both connections. Therefore, the extraction statement uses the application handle to filter data to ensure that the trace output table contains only the statements for the relevant application.

The TRACE view is a simple view that displays the extracted data that is stored in the TEMP_TRACE_DATA table. The view lists the traced statements in the order in which they were executed.

The extraction process (moving data from the event monitor tables to the TEMP_TRACE_DATA table) is not required. You can directly use the data in the event monitor tables. The script performs extraction to do some post-processing (in particular, metrics aggregation) and to filter out any extra data that was captured, such as from other applications.

The sqltrace.db2 script is intended as a simple demonstration of a method for tracing SQL statements by using an activity event monitor and the WLM_SET_CONN_ENV procedure. As such, it extracts and aggregates only a handful of the monitor metrics that are captured by an activity event monitor. To extract and aggregate additional metrics, perform these steps:

  1. Add columns for the metrics to the TEMP_TRACE_DATA table.
  2. Select the new columns in the TRACE view.
  3. Modify the INSERT statement in the TRACE_OFF procedure to extract the new metrics. Use the TOTAL_CPU_TIME metric as an example.

Also, statement text is severely truncated to permit easier display of results. To change the size of the statement text that the trace procedure captures, change these two lines by replacing the number 50 with your preferred capture size:

STMT_TEXT      VARCHAR(50), 
SUBSTR(C.STMT_TEXT,1,50)),1,50) AS TEXT,

You can use the sqltrace_cleanup.db2 script to drop all objects that the sqltrace.db2 script created. For example, run the sqltrace_cleanup.db2 script by issuing the following command:

db2 –td@ -f sqltrace_cleanup.db2

Examples

Example 1: Trace the execution of a procedure executed over the current connection

Suppose that you created a table and procedures as follows:

CREATE TABLE T1 (ONE INT)
                
CREATE PROCEDURE TEST.P4()
LANGUAGE SQL
BEGIN
   INSERT INTO T1 VALUES(5); 
   INSERT INTO T1 VALUES(6); 
   INSERT INTO T1 VALUES(7);
END
                
CREATE PROCEDURE TEST.P3()
LANGUAGE SQL
BEGIN
   DECLARE V INTEGER;  
                
   INSERT INTO T1 VALUES(1); 
   CALL TEST.P4(); 
   SELECT COUNT(*) INTO V FROM T1; 
END
                
CREATE PROCEDURE TEST.P2()
LANGUAGE SQL
BEGIN
   INSERT INTO T1 VALUES(2); 
   INSERT INTO T1 VALUES(3); 
END
                
CREATE PROCEDURE TEST.P1()
LANGUAGE SQL
BEGIN
   CALL TEST.P3();
   CALL TEST.P2(); 
   INSERT INTO T1 VALUES(4); 
END

You notice that the TEST.P1 procedure uses a large amount of CPU time and want to understand which statements the procedure executes and which statements are the most expensive. You decide to trace the execution of the procedure over the current connection by issuing the following statements:

CALL SQLTRACE.TRACE_ON()
CALL TEST.P1()
CALL SQLTRACE.TRACE_OFF()

Now, you select from the SQLTRACE.TRACE view to see which statements were executed (in their order of execution), along with the per-statement and aggregate CPU usage:

SELECT * FROM SQLTRACE.TRACE 
                
STMT_TEXT                STMT_CPU  STMT_ROWS_READ AGG_CPU AGG_ROWS_READ
----------------------   --------- -------------- ------- -------------
CALL test.p1()                3996              8   11974            29
  CALL TEST.P3()              3475              8    5100            15
    INSERT INTO T1 VALUES(1)   212              0     212             0
    CALL TEST.P4()             554              0    1119             0
      INSERT INTO T1 VALUES(5) 307              0     307             0
      INSERT INTO T1 VALUES(6) 135              0     135             0
      INSERT INTO T1 VALUES(7) 123              0     123             0
    SELECT COUNT(*) INTO :HV00 294              7     294             7
  CALL TEST.P2()              2594              6    2784             6
    INSERT INTO T1 VALUES(2)   107              0     107             0
    INSERT INTO T1 VALUES(3)    83              0      83             0
  INSERT INTO T1 VALUES(4)      94              0      94             0
VALUES CURRENT SQLID INTO      112              0     112             0
                
13 record(s) selected.

This example is somewhat contrived. There are other ways to monitor stored procedure performance. DB2 Version 10 Fix Pack 2 introduced comprehensive support for routine monitoring. For further details, see the DB2 Information Center.

Example 2: Trace the SQL statements that a specific application executes

Suppose that you use the MON_GET_CONNECTION table function to identify an application that appears to be executing a large number of SQL statements:

SELECT APPLICATION_HANDLE, ACT_COMPLETED_TOTAL 
       FROM TABLE(MON_GET_CONNECTION(NULL,-2)) AS T 
       ORDER BY ACT_COMPLETED_TOTAL DESC
                
 APPLICATION_HANDLE   ACT_COMPLETED_TOTAL 
-------------------- --------------------
                  56                10135
                  39                  873
                  41                  321
..
                  29                  125
                  22                  124
                
                
42 record(s) selected.

You are interested in what this application is doing and decide to trace the SQL statements that the application executes over the next two hours. You enable and then disable tracing by using the following statements:

CALL SQLTRACE.TRACE_ON( 56 )
… wait 2 hours (can disconnect or remain connected) …
CALL SQLTRACE TRACE_OFF( 56 )

Next, you examine the SQLTRACE.TRACE view to determine what statements the application executed over the last two hours:

 SELECT * FROM SQLTRACE.TRACE
            
 STMT_TEXT                        STMT_CPU STMT_ROWS_READ AGG_CPU  AGG_ROWS_READ      
 -----------------------          -------- -------------- -------- --------------
 select count(*) from syscat.tables  19379              3    19379              3
 select * from syscat.indexes        38710            363    38710            363
 …

Conclusion

This article described a technique for tracing the execution of SQL statements in DB2 Version 9.7 Fix Pack 4 and higher by using an activity event monitor and the WLM_SET_CONN_ENV procedure. Tracing SQL statements can be useful when you are attempting to understand what statements a particular application is executing or where a particular procedure is spending its time.


Download

DescriptionNameSize
Sample DB2 scripts for this articlesqltrace_scripts.zip5KB

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

  • Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.

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=960927
ArticleTitle=DB2 monitoring: Tracing SQL statements by using an activity event monitor
publish-date=01232014