Finding Lock waits in DB2
DB2 is  a little tough on its locking mechanism.  IBM says  DB2 V9.7 has improved a lot on this area, I haven’t had a chance to test it rigorously , so I have to take the word for it.
With DB2 V9.5 and lower, I have experienced lot of  application on lock wait on NS and S locks. This has been frustrating when there are application that just wont release the locks from a reads and other application would require an X lock.  I wrote a script that will take a snapshot on lock waits everytime is run and also will notify (email or page) when an application is on lock wait for more than 5 minutes.
You need to create a table to store the snapshot information that can be used for investigative purpose. The DDL for the table is as follows ;
create table dba.snap_lockwait
(
SNAPSHOT_TIMESTAMPÂ TIMESTAMP,
DB_NAMEÂ VARCHAR (128) ,
AGENT_ID bigint,
APPL_NAME varchar(256)Â ,
AUTHID VARCHAR (128),
TBSP_NAME VARCHAR (128),
TABSCHEMAÂ Â VARCHAR (128),
TABNAMEÂ Â Â Â Â VARCHAR (128),
SUBSECTION_NUMBERÂ Â bigint,
LOCK_OBJECT_TYPE varchar(18),
LOCK_WAIT_START_TIMEÂ Â TIMESTAMP ,
LOCK_NAME varchar(32),
LOCK_MODEÂ Â Â Â Â varchar(10),
LOCK_MODE_REQUESTEDÂ varchar(10),
AGENT_ID_HOLDING_LK bigint,
APPL_ID_HOLDING_LKÂ VARCHAR (128),
LOCK_ESCALATIONÂ SMALLINT,
DBPARTITIONNUMÂ Â Â SMALLINT
)
The script that does the job is  find_lockwait.txt .  I have it running on a crontab every 2 mins.
Like I always say, ensure that the script is safe to run on your environment and use due dilegence.  If you have any comments or questions regarding this please let me know.