IBM db2 lock wait analysis

Objective: When db2 lock wait happens anytime,  get the name of locked tables and save the statements
Environment: CentOS 5 and DB2 V9.1


Scripts and cronjobs:

# check lock wait process
*/5 * * * * /db2log/scripts/checklockwait.sh > /dev/null 2>&1

# more /db2log/scripts/checklockwait.sh
#!/bin/sh
DATE=`date +%Y-%m-%d`
export PATH=/db2/db2inst1/sqllib/bin:/usr/bin:/bin
. /db2/db2inst1/sqllib/db2profile

rm -f /tmp/lock-wait /tmp/lockid /tmp/lockdetails /tmp/lockemail /tmp/statements.txt /tmp/statements.zip
sync
db2 list application show detail | grep -i lock-wait > /tmp/lock-wait
sleep 15
db2 list application show detail | grep -i lock-wait > /tmp/lock-wait
note: if the lock wait remains after 15 seconds, consider it as real lock wait as sometimes the application will hold on tables for a while.

if [ -s /tmp/lock-wait ];then
 cat /tmp/lock-wait | awk '{print $3}' > /tmp/lockid
 while read line
     do
      db2 get snapshot for locks for application agentid $line >> /tmp/lockdetails
      DBNAME=`db2 list application | awk  -v APPHL="$line" '$3==APPHL {print $5}'`
      db2pd -db $DBNAME -locks wait showlocks -transactions -agents -applications  >> /tmp/lockdetails
      db2pd -db $DBNAME -locks wait showlocks -transactions -agents -applications -dynamic -repeat 20 1 > /tmp/statements.txt
     done < /tmp/lockid

 cat /tmp/lock-wait /tmp/lockdetails > /tmp/lockemail
 zip /tmp/statements.zip /tmp/statements.txt
 mutt -s "Lock-wait found on database server,please take action" -a /tmp/statements.zip jephe.wu@domain.com < /tmp/lockemail
fi


Notes:
1. found out while tablespace and tables according to their IDs
SELECT TABSCHEMA, TABNAME
FROM SYSCAT.TABLES
WHERE TBSPACEID = 2 AND TABLEID = 6


Reference:
1. Analyzing lockwait situations in DB2 for Linux, UNIX, and Windows -
http://www.ibm.com/developerworks/data/library/techarticle/dm-0707fechner/index.html
note: please refer to above URL to get the exact statements when lock wait happens. Which statement is blocking.