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

[John Hopfield]

I'm not an expert.
But i get locks details with this sql-query:

select
CASE SS.LOCK_MODE
WHEN 0 THEN 'No lock'
WHEN 1 THEN 'Intention Share Lock'
WHEN 2 THEN 'Intention Exclusive Lock'
WHEN 3 THEN 'Share Lock'
WHEN 4 THEN 'Share with Intention Exclusive Lock'
WHEN 5 THEN 'Exclusive Lock'
WHEN 6 THEN 'Intent None (For Dirty Read)'
WHEN 7 THEN 'Super Exclusive Lock'
WHEN 8 THEN 'Update Lock'
WHEN 9 THEN 'Next-key Share Lock'
WHEN 10 THEN 'Next-key Exclusive Lock'
WHEN 11 THEN 'Weak Exclusive Lock'
WHEN 12 THEN 'Next-key Weak Exclusive Lock'
END AS TipoLock,

SS.TABLE_SCHEMA,
SS.TABLE_NAME,
SS.AGENT_ID,
SA.APPL_NAME,
SA.APPL_ID,
SA.AUTH_ID,
SA.CLIENT_NNAME,
SA.INPUT_DB_ALIAS,
SA.CLIENT_DB_ALIAS

from table(snapshot_lock('', -2)) ss
LEFT OUTER JOIN TABLE ( SNAPSHOT_APPL_INFO('', -2) ) SA
ON SA.AGENT_ID = SS.AGENT_ID

where ss.table_name is not null
order by TipoLock
WITH ur

[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.

[4.s...@mail.ru]

Hi,

try this:

select
w.AGENT_ID_HOLDING_LK, w.AGENT_ID, w.TABLE_SCHEMA, w.TABLE_NAME
, case s.AGENT_ID when w.AGENT_ID_HOLDING_LK then 'OWNER' else
'WAITER' end type
, substr(s.stmt_text, 1, 4000) stmt_text
from table(snapshot_lockwait('', -2)) w
join table(snapshot_statement('', -2)) s on s.AGENT_ID in
(w.AGENT_ID_HOLDING_LK, w.AGENT_ID)
order by 1,5

[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.