Hi All !

Is it possible to determine the exact SQL that's waiting for locks (db2 ver 8 on AIX) ?
Using the list application, I managed to get the appl id waiting for locks.
I can determine the locks held using the get snapshot.
but i was wondering if there was a way to determine the exact sql (sql is in a java code using sqlj) that's waiting for locks ?

thanks

Anil

Doing a lock snapshot you can find out about the objects being locked, that might help you getting to the SQL using the SQL snapshot ...

To capture the SQL, you will either need to have the DFT_MON_STMT parameter set to ON, or else have a session running with the statement monitor switch on (db2 update monitor switches using statement on).

You can then either use the snapshot output, or the db2pd command to determine the application ID holding the lock (e.g. db2 get snapshot for locks on $DBNAME).

Once you know the ID, you can run a:

db2 get snapshot for application agentid XXX

which should detail the SQL text.

You can also do this via the Control Centre GUI - right click database name -> Applications -> Show Lock Chains.

Graham Martin
http://www.ibm.com/software/data/db2/migration/