Find Slow SQL Running in DB2

At work we have serval web applications pointing to the same database and a lot of the SQL isn't written optimally. So I wrote a simple scheduler task in Java to check the DB for slow SQL every 15 seconds and then email me the results.

Originally I was using the following SQL...

select ELAPSED_TIME_MIN, AGENT_ID, APPL_NAME, 
APPL_STATUS, AUTHID, INBOUND_COMM_ADDRESS,STMT_TEXT 
from SYSIBMADM.LONG_RUNNING_SQL 
where STMT_TEXT is not null and ELAPSED_TIME_MIN >= 1

This seemed to work well at first but I found it was reporting some SQL as running for more than a minute but it was actually taking as little as 8 seconds!

I couldn't find any information about how the ELAPSED_TIME_MIN value was calculated in the LONG_RUNNING_SQL view, so I had to come up with a better alternative and I ended up with the following SQL statement...

select 
timestampdiff (2, char(coalesce(s.STMT_STOP,s.SNAPSHOT_TIMESTAMP)-s.STMT_START)) as STMNT_SEC, 
timestampdiff (2, char(coalesce(a.UOW_STOP_TIME,s.SNAPSHOT_TIMESTAMP)-a.UOW_START_TIME)) as UOW_SEC, 
a.LOCK_WAIT_TIME, s.AGENT_ID, s.SECTION_NUMBER, 
ai.APPL_NAME, ai.APPL_STATUS, ai.PRIMARY_AUTH_ID, 
s.QUERY_COST_ESTIMATE, s.STMT_TEXT
from  SYSIBMADM.SNAPSTMT s,SYSIBMADM.SNAPAPPL a, SYSIBMADM.SNAPAPPL_INFO ai
where a.agent_id=s.agent_id and  a.agent_id=ai.agent_id
and s.STMT_TEXT is not null 
and timestampdiff (2, char(coalesce(s.STMT_STOP,s.SNAPSHOT_TIMESTAMP)- s.STMT_START)) >= 45 
and ai.APPL_STATUS not in ('UOWWAIT')
order by a.AGENT_ID, s.SECTION_NUMBER

This gives a much more accurate picture of long running SQL and SNAPSTMT and SNAPAPPL have lots of useful information that you could get out if you needed to.

Here is a typical email i would receive...