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...

ADMIN_MOVE_TABLE and it's limitations

At work I upgraded our DB2 installs to 9.7 last year and 1 of the new features in 9.7 that looked interesting was the ADMIN_MOVE_TABLE procedure.

This procedure claimed to move tables online. Although it does do this, it has some limitations that you need to be aware of...

Referential Constraints
The major one is that it doesn't handle referential constraints. So you need to drop them on the table you want to move and also any FK constraints on other tables that link to the PK of the table you want move. Then when the move is finished you would need to manually create them again. This limitation can be an issue if you rely on the constraints to CASCADE a delete or update.

Comments
I also found that the comments on a table weren't copied to the new table, including column comments.

Privileges
The user that you use to run the procedure needs to have SECADM rights otherwise it won't copy the GRANTS on the table. I know this isn't a limitation but i thought it was worth mentioning.