Tracing SQL Activity in DB2

The best way to trace SQL activity in DB2 to find bad queries, high resource usage, etc is to use statement monitors. Here is a good introduction to event monitors. To summarize:

The DB2 event monitor is a built-in utility in DB2 that writes out information to a file or pipe whenever a user-specified event occurs. The event could be, for example, the execution of an SQL statement or a commit or a sort or a lock being taken or whatever.

To see what events can be traced, issue the following command from db2 clp:
get monitor switches

You will see output like:
Monitor Recording Switches
Switch list for db partition number 0
Buffer Pool Activity Information (BUFFERPOOL) = OFF
Lock Information (LOCK) = OFF
Sorting Information (SORT) = ON 12/31/2007 14:42:44.078048
SQL Statement Information (STATEMENT) = ON 12/31/2007 14:42:44.078048
Table Activity Information (TABLE) = OFF
Take Timestamp Information (TIMESTAMP) = ON 12/31/2007 14:42:44.078048
Unit of Work Information (UOW) = OFF

To turn on/off tracing specific events, issue:
update monitor switches using sort off uow on
create event monitor mon_t for transactions write to file 'c:\temp'
set event monitor mon_t state=1
(if you get an error SQLSTATE=51026 saying the path is in use by another monitor, check in SYSCAT.EVENTMONITORS to verify)
Now, run the application/perform activities that you want to trace. When finished, issue:
set event monitor mon_t state=0
terminate

And then issue,
db2evmon -path c:\temp > sqltrace.txt

This will write out all the information about the events into the file sqltrace.txt. This file can get quite big and will contain a separate line for each event and is difficult to analyze directly. This is where the following program DB2Trace.java can come in handy (it has been sourced from the IBM website referred to above and modified slightly to handle the new version of DB2). The program parses and loads the information from sqltrace.txt into a table so that it becomes easier to analyze the data using regular SQL.

The code for the modified DB2Trace.java is in a separate post above. See above IBM post for details on how to run it.

After running DB2Trace and capturing event information into the database, the following queries can be used to identify bad SQL:

-- Slow SQL
SELECT /* TOP SQL ON ELAPSED TIME */
SQLTXT, EXECTIME
FROM DB2TRACE
WHERE RUNID = 'sqltrace.txt'
ORDER BY EXECTIME DESC FETCH FIRST 10 ROWS ONLY

-- Frequent SQL
SELECT /* Frequent SQL */ count(*) as Num_Executions, sqltxt
FROM DB2TRACE
WHERE RUNID = 'sqltrace.txt'
GROUP BY sqltxt
ORDER BY count(*) desc
FETCH FIRST 10 ROWS ONLY

-- CPU hoggers
SELECT /* Top CPU Using SQL */
sum(cast(usrcpu as decimal(15,6))) as CPU_SECS,
sum(cast(totsorttime as decimal(15,6))) as SORT_SECS,
sqltxt
FROM DB2TRACE
WHERE RUNID = 'sqltrace.txt'
GROUP BY sqltxt
ORDER BY sum(cast(usrcpu as decimal(15,6))) + sum(cast(totsorttime as decimal(15,6))) desc
FETCH FIRST 10 ROWS ONLY