30 August,2016 by Jack Vamvas
A customer called me yesterday and complained about slow response time on a DB2 database. I asked a few questions and they mentioned a few ad-hoc queries were executing. If I’m doing production troubleshooting – running a sql trace is a powerful method to retrieve detailed information.
Prior to getting the detailed information , I’ll use the SYSIBMADM.LONG_RUNNING_SQL view . This is a very useful DB2 administrative view , presenting long – running queries.
Under the hood , the LONG_RUNNING_SQL view joins some system snapshots.
SELECT APPL_NAME,AUTHID,INBOUND_COMM_ADDRESS, STMT_TEXT, AGENT_ID, ELAPSED_TIME_MIN, APPL_STATUS, DBPARTITIONNUM FROM SYSIBMADM.LONG_RUNNING_SQL ORDER BY APPL_NAME
Check the APPL_STATUS value on the query. Some troubleshooting scenarios which I use are:
a) Finding LOCKWAIT – Use the lock snapshots to dig deeper into the source of the issue
b) Find UOWAIT – check the requesting application