Capture DB2 long running SQL

DB2 (LUW) V8 and higher has a couple of things that annoy me when it comes to performance tuning. The first is the obtrusive nature of the event monitor. To guarantee you capture all the sql statements running in an app you have to run a blocked statement event monitor. What blocked refers to is when your buffer for statements is full SQL will start queuing up waiting to execute until the event monitor has logged its statements. Had another dba do this on a busy production system. All the server and db2 stats looked great… but user reposnse time was non-existent everyone was waiting. If your going to use an event monitor , use non-blocked for your own safety:) or better yet schedule the following select statement to insert into a table every 5 seconds or so to capture long running sql:

SELECT s1.STMT_TEXT,
(s1.STMT_USR_CPU_TIME_S + s1.STMT_SYS_CPU_TIME_S)*1000 + (s1.STMT_USR_CPU_TIME_MS + s1.STMT_SYS_CPU_TIME_MS)/1000 AS TOTAL_CPU_TIME,
s1.STMT_USR_CPU_TIME_S*1000000 + s1.STMT_USR_CPU_TIME_MS AS STMT_USR_CPU_TIME,
s1.STMT_SYS_CPU_TIME_S*1000000 + s1.STMT_SYS_CPU_TIME_MS AS STMT_SYS_CPU_TIME,
s1.STMT_START, s1.STMT_STOP, s1.STMT_SORTS, s1.TOTAL_SORT_TIME, s1.SORT_OVERFLOWS,
s1.ROWS_READ, s1.ROWS_WRITTEN, s1.INT_ROWS_INSERTED, s1.INT_ROWS_UPDATED, s1.INT_ROWS_DELETED,
s1.FETCH_COUNT, s1.AGENTS_TOP, s1.SECTION_NUMBER, s1.BLOCKING_CURSOR, s1.CURSOR_NAME, s1.STMT_OPERATION,
s1.STMT_TYPE, s1.PACKAGE_NAME, s2.APPL_NAME, s1.AGENT_ID, s2.AUTH_ID FROM TABLE(SNAPSHOT_STATEMENT('', -2)) AS s1,
TABLE(SNAPSHOT_APPL_INFO('', -2)) AS s2
WHERE s1.AGENT_ID = s2.AGENT_ID
and
((s1.STMT_USR_CPU_TIME_S + s1.STMT_SYS_CPU_TIME_S)*1000 + (s1.STMT_USR_CPU_TIME_MS + s1.STMT_SYS_CPU_TIME_MS)/1000) > 500

This entry was posted in db2, Other dbs, performance, script. Bookmark the permalink.