Troubleshoot Long running sql statements with LONG_RUNNING_SQL view

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

Read More

How to read db2detaileventlock event monitor trace file (DBA DB2)

Database Tuning for complex sql queries (DBA DB2)

Database Tuning – Five Basic Principles according to Shasha (DBA ...