How to find which SQL is causing the Lock

[Rahul Babbar]

Hi,

Is there any way to find the SQL that is causing the Locks.

I am using SYSPROC.SNAPSHOT_LOCK to find out the locks on the table,
but i am unable to find out which SQL statement was responsible for
the lock.

Thanks a lot

Rahul

[Otto Carl Marte]

On Nov 24, 1:25 am, Rahul Babbar <[e-mail]:

Hi,

Is there any way to find the SQL that is causing the Locks.

I am using SYSPROC.SNAPSHOT_LOCK to find out the locks on the table,
but i am unable to find out which SQL statement was responsible for
the lock.

Thanks a lot

Rahul

When you say Locks, i assume you mean database deadlocks. The db2pd
utility (DB2 9) is an excellent utility to view the locks that have
been acquired at a particular instant in time.

DB2 also by default has an event monitor (DB2DETAILDEADLOCK) that
"catches" deadlocks and records information regarding them:

The output is in a directory like:
<instance owner home>/NODE0000/SQL00001/db2event/db2detaildeadlock

And you can mine this information with the db2evmon utility:
db2evmon -path . /tmp/deadlockoutput.txt

Finding database deadlocks is a nasty business. So good luck!

[Tomas]

Rahul,

one possibility might be to join SNAPSHOT_LOCK to SNAPSHOT_STATEMENT.

/T

On Nov 23, 10:25 pm, Rahul Babbar <[e-mail]:

Hi,

Is there any way to find the SQL that is causing the Locks.

I am using SYSPROC.SNAPSHOT_LOCK to find out the locks on the table,
but i am unable to find out which SQL statement was responsible for
the lock.

Thanks a lot

Rahul