DB2 monitoring: Tracing SQL statements by using an activity event monitor
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:
- Add columns for the metrics to the TEMP_TRACE_DATA table.
- Select the new columns in the TRACE view.
- 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
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 …
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.
- 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.