Hi all,

I have two queries:

1)I want to know all the locks that are held on a database at any point of
time. Can any one please help me on that.


2) Please correct me if I am wrong.
I want to CHECK and then CHANGE the lock level in my database from
table level to row level how do I do that?

TIA
Ayusman

Answer to Q1> You can see the db2 reference material for a detailed theory on Lock. Anyway its Exclusive, Shared , Intent to Update.

Answer to Q2> You cannot change from table level locking to row level locking. on your table if INDEX es are created then row level locking can be expected. Otherwise you can expect a table level locking.
Default Isolation Level is Cursor Stability. If you want to change that, then before you connect to the database execute the following: db2 "change isolation level to UR" for uncomtted read. CS for cursor stability and so on..

There were some very good discussions on Locking available in this forum itself. You can search the same for better clarification.

GET SNAPSHOT FOR LOCKS ON <database>

Normally DB2 takes row-level locks. Only if the lock list space is exhausted the locks are escalated to the table level. You can only control that indirectly, by changing MAXLOCKS and LOCKLIST parameters, changing the transaction isolation level, and optimizing queries.

You can force a table-leve lock by issuing the LOCK TABLE statement.

Theoritically true (as default locking is row-level), but practically have seen many instances where the table is locked where a row-level locking was expected (even when MAXLOCK and LOCKLIST parameters werenot exhausted).

To get the snapshot you need to turn your monitor for LOCK ON. If you wanna see the applications which have taken locks just try :
db2 "list applications show detail" | grep Lock
It also works.

If you're on z/OS, you may use
ALTER TABLESPACE name LOCKSIZE ROW.
Add "LOCKMAX 0" if you want to avoid lock escalation (but be careful with this!)