I am reposting a question from our old dW forum here. Rizwan has the question below.Hi Gurus,
AIX 6.1, DB2 10.5.0.8
I can see locking in my db2 database of around 50k to 60k in peak hours. i have been searching around, found DB2PD, DB2TOP etc but not able to fetch the statements holding the locks.
I am looking for something like Oracle ADDM or AWR report to identify the Root Cause and something like Oracle v$locks Dynamic view to fetch the currently locking statements/ids to resolve the deadlocks.
Please help me in this.
And there are the higher level MON_LOCKWAITS administrative views and LOCKING event monitor. You may get what you want from the first one....Just be aware that things can get more complicated if you have multiple SQL statements in a transaction and the transaction is using a higher isolation level or is issuing writes to the database or DDL, then the transaction can still hold locks even after the initiating statement is no longer executing.
E.g. If I issue an INSERT followed by a SELECT in the same transaction, then the transaction would still have the X locks from the INSERT even though it is currently executing a SELECT...Also, it is not clear what you mean by "locking in my db2 database of around 50k to 60k in peak hours", you may expect to see lots of locks if you are doing lots of things. Locks themselves are not an issue, lock waits and lock escalations are. You can see if they are occurring by looking at the DEADLOCKS, LOCK_ESCALS, and LOCK_TIMEOUT metrics in MON_GET_DATABASE() or any of the other levels of MON_GET_* functions.
If you have deadlocks occurring, then the best tool to use is the locking event monitor which will get you all the parties involved in the deadlock.
You could also consider using the Data Server Manager tool that ships with Db2 to help you capture and look at the locking information.Hope this helps,