RC/Extract for DB2 for Z/OS : How to set ISOLEVEL in RC/Extract?
Article ID: 14464
Updated On:
Products
Issue/Introduction
The Isolation level is referenced by the Process Source Definition step only for the SQL(S) extract type as opposed to the Utility(U) type extract.
Correct specification of the isolation level for SQL access to carry out the extract may reduce the contention on DB2 resources.
RC/Extract for DB2 for z/OS (RCX) is using an ISOLEVEL option that we do not want.
ie, ISOLEVEL(RR)
Because of this we are having performance problems while using Online as well as Batch mode. How do we set the required ISOLEVEL in RCX?
Environment
Release: R20
Component: RCX
Resolution
To Change the Isolation level:
At the Perform the Extract(option X) when the "Extract Method" is "S", place a "Y" in the 'Update Extended Options' field.
RXPEXT ------------- Process Source Definition ------------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
Source Definition:
Creator ===> authid1 (Blank or pattern for list)
Name ===> TBEMP (Blank or pattern for list)
Extract Object ===> authid1.TBEMP.EXTOBJ
Registry SSID: ssid --------------------------------------------- authid1
Execution Options:
Process Mode ===> (O - Online, B - Batch)
Extract Method ===> S (S - SQL, U - Utility)
Update Extended Options ===> y (Y - Yes, N - No)
Update Allocations ===> N (Y - Yes, N - No)
Intercept Errors ===> Y (Y - Yes, N - No)
Extract DDL ===> N (Y - Yes, N - No)
Overwrite EXTOBJ ===> N (Y - Yes, N - No)
Set Extract Parameters ===> N (Y - Yes, N - No)
On the "SQL Extract Options" screen see the option "Isolation Level".
The choices are CS, UR, RS and RR.
--------------- RC/EXTRACT SQL Extract Options -------------- yyyy/mm/dd hh:mm
Command ===>
Advanced Options:
Keys mode ==> 0 (0-private, 1-1 dataspace, 2-n dataspaces)
Dup elimination ==> U (N-None, X-Sort only, S-System only, U - User RI)
Extract Options:
Isolation level ==> CS (CS-Cursor Stability,UR-Uncommitted Read,
RS-Read Stability,RR-Repeatable Read)
Set this field to CS or whatever you require.
Use the SAVE command on this screen to save your options to your own ISPF profile.
--------------- RC/EXTRACT SQL Extract Options -------------- yyyy/mm/dd hh:mm
Command ===>
RX170 RX170I: Settings saved in profile. Press HELP for more info.
Advanced Options:
Keys mode ==> 0 (0-private, 1-1 dataspace, 2-n dataspaces)
Dup elimination ==> U (N-None, X-Sort only, S-System only, U - User RI)
Extract Options:
Isolation level ==> CS (CS-Cursor Stability,UR-Uncommitted Read,
RS-Read Stability,RR-Repeatable Read)
If run in batch or Online you can see the Isolation Level parm listed between the .DATA and the .ENDDATA lines:
ISOLEVEL(CS)
.DATA
REGISTRY(ssid)
SRCDEF(source.definition)
EXTOBJ DDNAME(PTIXOBJ) +
DSN(source.definition.dataset) +
UNIT(SYSDA,01) +
LRECL(4078) +
SPACE(500,200,CYL) +
DISP(CATLG) +
MGMTCLAS(mgmtclass) +
STORCLAS(storeclass)
OVFLOW UNIT(SYSDA)
OPTIMIZE(YES)
DDL(NO)
INTERCEPTERRORS(YES)
REPORT(Y,2000)
KEYSMODE(0)
ACCESSMETHOD(SQL)
DUPELIMINATION(U)
SPLIT(NO)
IMAGECOPY(NO)
ISOLEVEL(CS)
SHRLEVEL(REFERENCE)
RESTART(NO)
SORTALLOC(SYSDA,1)
.ENDDATA
Additional Information
Feedback
