Looking for the option to set the Plan Isolation Level. What should the Isolation Level be set at for FDM?
Our DBAs said that the read access of our process is set as CS (Cursor stability) but it should be UR (uncommitted Read).
DB = IBM DB2 Z/os
FDM = 4.10.510.0
DB = IBM DB2 V12 FL 509
Release : 4.10
Normally CS is the adopted level when binding plans. Doing some research regarding pros and cons, we found the following:
According to IBM, the recommended order of preference for isolation levels is: Cursor stability (CS), Uncommitted read (UR), Read stability (RS), Repeatable read (RR).
Although uncommitted read provides the lowest level of isolation, cursor stability isolation is recommended in most cases.
ISOLATION(CS) provides a high level of concurrency, without sacrificing data integrity.
*** ISOLATION(CS) typically enables Db2 to release acquired locks as soon as possible. The CURRENTDATA(NO) typically enables Db2 to acquire the fewest number of locks, for better lock avoidance.
*** The Resource Recovery Services attachment facility UR isolation acquires almost no locks on rows or pages. It is fast and causes little contention, but it reads uncommitted data. Do not use it unless you are sure that your applications and end users can accept the logical inconsistencies that can occur.
To summarize, CS is chosen specially because we will guarantee that our programs will be updated with valid data, as Uncommitted Read isolation level does not guarantee that. This way, we are guarantying data integrity when processing the data via TDM. This is, in my opinion, the most important reason for choosing CS as isolation level. If you want to use UR, will be at your own risk if there is a data inconsistency further.
To update the Isolation to UR, as TDM by default does not have that isolation level, we don't know in practical terms what could be the consequences of that, if some inconsistence would occur etc.
Also, UR creates minimum LOCK on the resources, but it may so happen that, once TDM is updating a particular row, the original value was changed in that period by another process. As there is almost no lock, TDM may pick up an outdated value and mask/transform it.
If you would still like to update the Isolation, then in the PARMS datasets, you have the BIND cards named as BGTXMSK, BGTXMSKF and BGTXMSKL. Edit these three members and replace ISOLATION(CS) for ISOLATION(UR) for all Plans inside them.