Hi Everyone ,

I need help with one of my production database that is causing "ADM5502W" issue very frequently during the index rebuild phase, which is captured in the db2diag.log. I checked for LOCK WAIT but there were none .The db configs params are set as below

MAXLOCKS = 10
MAXAPPLS = 500
LOCKLIST = 1024
LOCK TIME OUT = -1

Could anyone please suggest me if I need to change any of the above params. This is really urgent and I need to fix this asap. I will appreciate any help which will help me tune this db

Thanks

Sam

A "W" message is a warning and not necessarily a problem. You are likely to always get this message when you have an index rebuild, and DB2 will run faster when the lock escalation from row to table occurs. So long as no other application is having unacceptable lock waits while trying to access this table when this is happening, then there is no problem.

I typically set those parms to something like this for an OLTP application, but it probably will not eliminate the warning or the escalation during index rebuild:

MAXLOCKS = 40
LOCKLIST = 4096
LOCK TIME OUT = 30 (-1 means wait forever for a lock to be freed before issuing a -911 RC 68).

Lock escalation occurs when a transaction is holding many locks on rows and the lock heap is getting tight, i.e. it can't hold much more locks. At that point, DB2 figures that it is better to grab a lock on the table and release all the row locks. Since each lock requires some memory (about 40-80 bytes), DB2 can potentially free up a lot of memory that way in the lock heap. This process is called lock escalation.

The advantage is less pressure on the lock heap and, with fewer locks being held, the check whether a row is locked can be done faster too. The disadvantage is that the table lock is on a coarser granularity, meaning that concurrency is reduced - if lock escalation occurs for write-operations, the impact can be dramatic because the whole table is essentially blocked for all other transactions. Another issue is that lock escalation needs some time, which slows down the transaction in who's context the escalation is done. That may not be acceptable for all transactions.

Thus, you have a few options:

• increase the size of the lock heap to have more memory available in general
• use the LOCK TABLE statement in your applications to grab a table lock right away instead of waiting for the lock escalation
• commit more ofter in your application to release locks earlier and, thus, reduce the pressure on the lock heap

If the lock escalation is being done during "index rebuild phase" (of some utility like reorg or load) then it is unlikely that he needs to do any of the above things (and in the last two cases, unlikely he can do them).

The correct option is to learn that warning messages are for information only and should usually be ignored.