Capturing Top SQL in DB2 (9.1)

Steps to capture Top queries on 9.1:
1. Identify a time window when the load will be high
2. reset monitor all;
3. update monitor switches using statement on sort on;
4. drop event monitor evmon; (in case it exists)
5. CREATE EVENT MONITOR evmon
FOR STATEMENTS
WRITE TO TABLE
STMT (TABLE EVMON.STMT_EVMON);
6. commit;
7. set event monitor evmon state=1;
8. Run the application that we are trying to monitor for, say 15 mins or so.
The STMT_EVMON table grows really fast so keep issuing a count(*) with UR on it and once you feel there
are enough records (say, 200000 or so), stop the monitoring as follows.
8a. At the same time when monitoring is on, also capture the system CPU performance by issuing: vmstat -t 3 > /tmp/cpustats.out
This will be useful for comparisons later on.
9. set event monitor evmon state=0; (to stop monitoring)
10. runstats on table evmon.stmt_evmon;
11. commit;
12. Export the stmt_evmon table and import into the UAT system for analysis. While importing, call it stmt_evmon_jul13_pass1, for example.
13. Drop table evmon.stmt_evmon on production
14. update monitor switches using statement off sort off;

The table will have detailed info about each SQL. To focus on the offenders, we can look at "Top" sql (queries which are both frequent and run slow) and then at frequent SQL and then slow SQL. Views such as the following will make the analyis easier.

CREATE VIEW EVMON.TOP_SQL AS
SELECT *
FROM
(SELECT *
FROM
(SELECT
avg((DAYS(stop_time) - DAYS(start_time)) * 86400
+ (MIDNIGHT_SECONDS(stop_time) -MIDNIGHT_SECONDS(start_time))) as elapsed_time,
count(*) as executions,
avg(user_cpu_time) as user_cpu_time,
avg(rows_read) as rows_read,
avg(total_sorts) as total_sorts,
avg(pool_data_l_reads) as logical_io,
avg(pool_data_p_reads) as physical_io,
avg(sort_overflows) as sort_overflows,
cast(substr(stmt_text, 1, 600) as varchar(600)) as stmt_text
FROM evmon.stmt_evmon
GROUP BY
cast(substr(stmt_text, 1, 600) as varchar(600))
) X
ORDER BY EXECUTIONS*ELAPSED_TIME DESC
) Y


CREATE VIEW EVMON.SLOW_SQL AS
SELECT user_cpu_time,rows_read,rows_substr(stmt_text, 1, 600)
FROM evmon.stmt_evmon
ORDER BY user_cpu_time desc

CREATE VIEW EVMON.FREQUENT_SQL AS
SELECT stmt_text, count(*) FROM
(select cast(substr(stmt_text, 1, 600) as varchar(600)) as stmt_text
from administrator.stmt_evmon) X
group by stmt_text
order by count(*) desc

15. Issue queries such as select * from evmon.top_sql fetch first 25 rows only to get the slowest queries. Capture the output in Excel for ease of use.
16. In a document, note each of the top queries, its access plan, time it took to run, number of sorts, sort overflows, rows read, origin, etc.
17. Tune and analyze each of the top queries:-) Go back to step 1 when done:-)

Lock timoeut monitoring

If the client is on 9.1 FP4 or later, then lock timeout monitoring is really simple and just needs one config param setting. If they are on an earlier release, it is a bit more involved but can still be done as explained in this:

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0707fechner/?S_TACT=105AGX01&S_CMP=LP


http://www.ibm.com/developerworks/data/library/techarticle/dm-0804fechner/index.html