ADM5500W DB2 is performing lock escalation. The total number of locks
currently held is "1034", and the target number of locks to hold is "517".

whats meaing of this???

how i can solve it???

Increase your locklist size to 4096 pages. If you still are getting these errors frequently, keep on doubling the locklist size (8192 next time).

db2 connect to <database-name>;
db2 update db cfg using locklist 4096;
db2 update db cfg using maxlocks 60; (this is optional and determines what percent of the locklist can be used by any one application)

When DB2 runs out of space for holding locks in the locklist, the it escalates locks to the table level.

A little bit of more detailed background: Locks are data structures that indicate which part of the system (row, page, table, tablespace, database, ...) is protected in some way. Since DB2 has to remember this information, it has to store it in memory. Ideally, you would like to have very fine-grained locking in order to allow maximum concurrency, i.e. blocking the whole database all the time an application accesses a small part of it doesn't make much sense. So if you have an application that deals with a lot of data, it potentially has to have many locks. Those locks accumulate and need space in memory. The lock list is this in-memory space.

It would be bad if DB2 cannot handle the situation where the free space in the lock list is nearly exhausted because - overall - too many locks did accumulate. So DB2 does a "lock escalation". That means, many row-level locks held by one transaction on a table are escalated to a single table-level lock, for example. Instead of having 1000s or millions of locks (per row), there is just one, relieving the pressure on the lock list.

Of course, such an escalation has a few drawbacks: it reduces concurrency, it may not be possible (due to locks of other SQL sessions), and it impacts performance negatively. The performance impact is due to the fact that the 1000s or millions of locks have to be released, which just takes time.

These are DB2 for z/OS lock levels. For DB2 Linux, UNIX, Windows, the lock levels are row or table.

You're sure? DB2 LUW must also have mechanisms to block access to tablespaces and databases.

Block access, yes. But that is not same as a lock or lock escalation.

In DB2 LUW lock escalation only happens from ROW to TABLE. You can explicitly lock a table, but that is not lock escalation. You can also effectively block access to tablespaces and databases, but that is not lock escalation either.

Yes, you're right. There is no escalation on tablespace and database levels. I was just saying that locks [i]exists[i] on those levels. Sorry for the confusion.

They are no locks at the tablespace or database level. There are things like the QUIESCE command, or setting a tablespace in some status other than normal (not available, copy pending, etc), but there are no locks at those levels.

If your SELECT query is accessing a substantial part of the table (say, more than 20%),
consider issueing a "'LOCK TABLE my_table IN SHARE MODE" statement before the SELECT, or consider using "WITH UR".

If on the other hand it's an INSERT/UPDATE/DELETE statement, consider issueing a "'LOCK TABLE my_table IN EXCLUSIVE MODE" statement before that statement.

Marcus, what would you call this blocking mechanism (via tablespace/database status) if not "lock"? Sure, it is not maintained in the LOCKLIST, but it is used to control access to the respective object and provides locking functionality.

Marcus, what would you call this blocking mechanism (via tablespace/database status) if not "lock"? Sure, it is not maintained in the LOCKLIST, but it is used to control access to the respective object and provides locking functionality. I guess we are just talking on two different levels: concepts vs. specific implementation in DB2.

I would call it QUIESCE. Anyway, it has nothing to do with lock escalation.