how to get db2 lock waits details

[Marco]

Hi all,
with db2 snapshot I know the lock waits number but I can not see the
tables with locks an I can not see queries that produces locks.

How can I know those details?

Thanks

Marco Strullato

[Ian]

Marco wrote:

Hi all,
with db2 snapshot I know the lock waits number but I can not see the
tables with locks an I can not see queries that produces locks.

How can I know those details?

You have to look at a lock snapshot, not a database snapshot. You
can do this with:

get snapshot for locks on <dbname>

Or, as someone else suggested, using the SNAPSHOT_LOCK table function.

[RZ]

Marco wrote:

Hi all,
with db2 snapshot I know the lock waits number but I can not see the
tables with locks an I can not see queries that produces locks.

How can I know those details?

Try this:
http://chuzhoi_files.tripod.com/

[Richard]

tells which application id is locking,

db2 get snapshot for locks on $db_name | grep -i 'agent holding
lock'
tells which application ids are locked,

db2 list applications $db_string show detail .whos.out00
sed -n '1,4p' .whos.out00
sed -n '5,$p' .whos.out00 | grep -i 'lock.*wait'

once you get the locked app-id's, simple " db2 get snapshot for
application agentid <app-id" will show something like,

ID of agent holding lock = 1058
Application ID holding lock =
NFA8066B.M610.01B446165920
Lock name =
0x002B0009000000000000000054
Lock attributes = 0x00000000
Release flags = 0x00000001
Lock object type = Table
Lock mode = Exclusive Lock (X)
Lock mode requested = Intention Share Lock (IS)
Name of tablespace holding lock = TBSP_4K_DATA1
Schema of table holding lock = SL
Name of table holding lock = ADJUSTREASONXREF
Lock wait start timestamp = 03/16/2007
14:38:56.396577

Beware locking agent could itslef be locked by someone else. Best
thing is to jott down locking ID ('s) and locked ID ('s)
under 2 column, and draw arrow --pointing to whose locking whom.
Then its easy to see what's going on.

Also db2pd gives a little of info. But navigating db2pd canbe
overwhelming.