DB2 is \u00c2\u00a0a little tough on its locking mechanism. \u00c2\u00a0IBM says \u00c2\u00a0DB2 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.<\/p>\n
With DB2 V9.5 and lower,\u00c2\u00a0I have experienced lot of \u00c2\u00a0application 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.\u00c2\u00a0\u00c2\u00a0 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.<\/p>\n
You need to create a table to store the snapshot information\u00c2\u00a0 that can be used for investigative purpose.\u00c2\u00a0 The DDL for the table is as follows ;<\/p>\n
create table dba.snap_lockwait
\n(
\nSNAPSHOT_TIMESTAMP\u00c2\u00a0 TIMESTAMP,
\nDB_NAME\u00c2\u00a0 VARCHAR (128) ,
\nAGENT_ID bigint,
\nAPPL_NAME varchar(256)\u00c2\u00a0 ,
\nAUTHID VARCHAR (128),
\nTBSP_NAME VARCHAR (128),
\nTABSCHEMA\u00c2\u00a0\u00c2\u00a0 VARCHAR (128),
\nTABNAME\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 VARCHAR (128),
\nSUBSECTION_NUMBER\u00c2\u00a0\u00c2\u00a0 bigint,
\nLOCK_OBJECT_TYPE varchar(18),
\nLOCK_WAIT_START_TIME\u00c2\u00a0\u00c2\u00a0 TIMESTAMP ,
\nLOCK_NAME varchar(32),
\nLOCK_MODE\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 varchar(10),
\nLOCK_MODE_REQUESTED\u00c2\u00a0 varchar(10),
\nAGENT_ID_HOLDING_LK bigint,
\nAPPL_ID_HOLDING_LK\u00c2\u00a0 VARCHAR (128),
\nLOCK_ESCALATION\u00c2\u00a0 SMALLINT,
\nDBPARTITIONNUM\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 SMALLINT
\n)<\/p>\n