Troubleshooting Db2 application hangs

You can use the db2_hang_analyze script to detect and analyze possible application hangs in a Db2 database. It is a Perl script located in the sqllib/samples/pd/ directory.

About this task

If an application is not responding or is not finishing as quickly as you expect, the application might be hanging. For example, you might try to run a query against the database, but the query does not respond. You might suspect that a hang occurred. However, the query might be taking a long time to run, as is the case with queries that involve Cartesian products on large tables.

Root user authority required.

Procedure

To troubleshoot a possible application hang, issue the db2_hang_analyze script. An example follows: 
$HOME/sqllib/samples/pd/db2_hang_analyze -db dbname -log

Unless you terminate the script by pressing Ctrl-C or Ctrl-Z, the script runs until it detects a possible hang. If you specify the -log parameter, notifications, warnings, and errors are printed to a log file. If a hang is detected, a list of hanging applications is written to a report file. An example of the report file follows:

cat /home/hotel32/shenli/sqllib/db2dump/db2_hang_analyze.20130125.14.41.38.10297.report
APPLICATION HANG DETECTION: Started on Fri Jan 25 14:41:38 EST 2013
Sleeptime                :  60 seconds
Timer Limit              :  300 seconds
Node Member              :  default
Retry Limit              :  3
Log                      :  yes
SQL                      :  no
Event Metrics Available  :  yes
Logfile                  :  db2_hang_analyze.20130125.14.41.38.10297.log
Script PID               :  10297
CPU Threshold            :  0.1%
Post Detection Script    :  none
Path                     :  /home/hotel32/shenli/sqllib/db2dump/

POTENTIAL APPLICATIONS HANGING: 3 application(s).
Apphdl              :  7
Status              :  CommitActive
AgentEDUID          :  16

Apphdl              :  9
Status              :  CommitActive
AgentEDUID          :  28

Apphdl              :  19
Status              :  CommitActive
AgentEDUID          :  37


APPLICATION HANG DETECTION: Ended on Fri Jan 25 14:46:47 EST 2013

The report file shows that three applications are hanging.

What to do next

Using the report from the db2_hang_analyze script, you can make an informed decision on the best course of action to take with the hanging applications. For example, you can retrieve a stacktrace of the hanging application three times at 60-second intervals, by issuing the following command:
db2pd -stack eduid -repeat 60 3
where eduid is the AgentEDUID number from the report file produced when you run the db2_hang_analyze script.