Behaviour of a COBOL-DB2 program when number of locks exceed the limit

Problem: How will the COBOL-DB2 program behave once the number of locks placed on the table space exceeds the defined limit?

Solution

The number of locks which an application can place on a DB2 resource such as page, table row, etc., is defined in DSNZPARM. Once the number of page and row level locks in any table exceeds the permissible limit, then the lock escalation takes place.

In lock escalation, DB2 releases the page or row level lock which it has held and attempts to acquire a tablespace level or higher lock. In this case, the application now has wider access/scope to DB2 resources as the entire tablespace is locked for its processing.

For example, if we have below DB2 ORDERS table.

If a COBOL-DB2 program is updating this table for the predicates ORDER_TOTAL > 1000 and ORDER_DATE > 15-10-2020. As a result of this the COBOL DB2 program has placed locks in multiple rows which exceeds the maximum limit defined in DSNZPARM, then the lock escalation will take place.

In this case, DB2 will release the locks held in the multiple rows and place a lock in the entire ORDERS table. Due to any reason if DB2 is not successful in acquiring table lock in ORDERS table, then a rollback takes place and DB2 restores all the changes made in the table.