List of a lock concerned stmts

[issam]

Hello,
When i execute "db2 select * from
TABLE(SYSPROC.SNAPSHOT_LOCK('Test',0)) as test_locks" , i get the
details of all the locks on db test
i want to know how to get the correspondence lock_stmts (the statements
that are concerned by each one of the locks)?

there is "db2 select * from TABLE(SYSPROC.SNAPSHOT_SQL_DYN('Test',0))
as test_qerys" but it only show the details of the stmts.

Thank you in advance

Laura

[Tomas]

Try joining it to snapshot_statment:

select
a.*,
b.stmt_text
from
table(snapshot_lock('mydb',-1)) a,
table(snapshot_statement('mydb',-1)) b
where
a.agent_id = b.agent_id;

/T
issam wrote:

Hello,
When i execute "db2 select * from
TABLE(SYSPROC.SNAPSHOT_LOCK('Test',0)) as test_locks" , i get the
details of all the locks on db test
i want to know how to get the correspondence lock_stmts (the statements
that are concerned by each one of the locks)?

there is "db2 select * from TABLE(SYSPROC.SNAPSHOT_SQL_DYN('Test',0))
as test_qerys" but it only show the details of the stmts.

Thank you in advance

Laura

[Tomas]

Try joining it to snapshot_statment:

select
a.*,
b.stmt_text
from
table(snapshot_lock('mydb',-1)) a,
table(snapshot_statement('mydb',-1)) b
where
a.agent_id = b.agent_id;

/T
issam wrote:

Hello,
When i execute "db2 select * from
TABLE(SYSPROC.SNAPSHOT_LOCK('Test',0)) as test_locks" , i get the
details of all the locks on db test
i want to know how to get the correspondence lock_stmts (the statements
that are concerned by each one of the locks)?

there is "db2 select * from TABLE(SYSPROC.SNAPSHOT_SQL_DYN('Test',0))
as test_qerys" but it only show the details of the stmts.

Thank you in advance

Laura

[issam]

Thanks for the response Tomas.

i'm sorry for the late response i was really really busy.

when i use the joining you said i always obtain a null statement_text
the locks ad the tables are right but the statements are null. I know
this can't be true as the SNAPSHOT_SQL_DYN is full of not null
statement_text.

there is the request i use:

select substr(char(b.agent_id),1,10) agent_id, substr(b.table_name,
1,30) table_name,case b.lock_object_type
when 1 then 'Table' when 2 then 'Row' when 3 then 'Internal'when 4
then 'Tablespace'
when 5 then 'EndOfTable' when 6 then 'KeyValue' else 'Internal'end as
type,
case b.lock_mode when 1 then 'IS' when 2 then 'IX'
when 3 then 'S' when 4 then 'SIX' when 5 then 'X' when 6 then 'IN'
when 7 then 'Z' when 8 then 'U' when 9 then 'NS' when 10 then 'NX'
when 11 then 'W' when 12 then 'NW'else 'Other'
end as mode ,substr(char(c.stmt_text),1,20) from
table(snapshot_lock('test',0)) b ,table(snapshot_statement('test',0))
c
where b.agent_id = c.agent_id

is there any problem in my statement ? if no is there any other way to
obtain list of locks without using snaphots?