Contents


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

Comments

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 SYSDEFAULTSUBCLASS 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 Downloadable resources) 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 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.


Downloadable resources


Related topic


Comments

Sign in or register to add and subscribe to comments.

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