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

DB2DatabaseBig Data AnalyticsCOBOL

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.

ORDER_ID
ORDER_TOTAL
ORDER_DATE
A22345
1867
22-10-2020
A62998
5634
11-11-2020
A56902
7615
14-10-2020
A56911
87960
30-10-2020
A56915
132
09-10-2020
A56918
80363
04-10-2020

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.

raja
Published on 30-Nov-2020 14:01:14
Advertisements