How to implement isolation levels CS, RR, UR and RS in a DB2 program?

DB2DatabaseBig Data Analytics

The isolation level defines the degree to which the DB2 data which is being accessed in the COBOL-DB2 program is isolated with another parallelly executing COBOL- DB2 program. There are 4 main types of Isolation levels in DB2.

Cursor stability (CS) - The cursor stability isolation level locks only the current row which the program is accessing. As soon as the program shifts to the next row, the lock in the previous row gets released. The cursor stability fetches only committed rows for the program to access. This is a default isolation level.

Read stability (RS) - This isolation level places a lock on all the rows which qualifies the SQL statement’s predicate (eg: WHERE clause). The lock is retained until the entire processing is completed.

Uncommitted read (UR) - The uncommitted read isolation level is used in the SQL statements meant for read-only purpose. There is no lock placed on a row/record and it fetches the committed as well as uncommitted rows (from the other programs/transactions).

Repeatable read (RR) - This isolation level is used when we need to retain the locks until a commit.

To define an isolation level for a COBOL-DB2 program, we need to use the corresponding parameter in the ISOLATION option of the BIND step. Below is the JCL step using which isolation level can be defined.

//BIND EXEC PGM=IKJEFT01
//STEPLIB DD DSN=DIS.TEST.LOADLIB,DISP=SHR
//SYSOUT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(TB3)
BIND PLAN(PLANA) -
PKLIST(PACKA) -
ACQUIRE(ALLOCATE) -
ISOLATION (RS)
/*

In the above step, we have used the ISOLATION option with RS parameter, which corresponds to read stability. Similarly, we have parameter CS for cursor stability, RR for repeatable read and UR for uncommitted read.

raja
Updated on 11-Sep-2020 12:39:53

Advertisements