469,268 Members | 920 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,268 developers. It's quick & easy.

how to get db2 lock waits details

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
Aug 19 '08 #1
3 30242
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.
Aug 19 '08 #2
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/
Aug 22 '08 #3

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.

Aug 24 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Bruce Pullen | last post: by
1 post views Thread by news_account | last post: by
3 posts views Thread by Raj | last post: by
7 posts views Thread by situ | last post: by
reply views Thread by rattas | last post: by
3 posts views Thread by shorti | last post: by
6 posts views Thread by michael.spoden | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.