RC/Update for DB2 for Z/OS : Isolation level used for queries when browsing Db2 tables
Article ID: 76508
Updated On:
Products
Issue/Introduction
The r20 release of RC/Query for Db2 for z/OS provides a new ability to specify the isolation level used by users that are browsing Db2 tables. Cursor stability (CS) and uncommitted read (UR) are supported at the global and user level. Being able to specify the isolation level prevents queries from locking the table rows when the table rows are being read and changed simultaneously by multiple users. The isolation level is appended to the SELECT statement used while browsing using the WITH clause.
For example, WITH CS or WITH UR.
Environment
Release: 20.0
Component: RC/Query for Db2 for z/OS
Cause
It is important to select the correct isolation level while browsing DB2 table data in order to ensure that other users of the same data are not disrupted unless there is a valid reason to hold the data. Upon connection to a DB2 database, the isolation level used determines
the scope of data integrity and concurrency. A stronger isolation level leads to increased data integrity, while a weaker isolation level leads to more concurrency and better performance. A weaker isolation level also reduces row locking, so the incidence of deadlock
situations is minimized.
Resolution
All users at a site are provided with a new parameter named ISOLATN that has been added to the RCEDIT parmlib member in the hlq.CDBAPARM data set.
CS - CURSOR STABILITY is the default.
Excerpt from the hlq.CDBAPARM(RCEDIT) member:
/********************************************************************/
/* RC/EDIT AND RC/BROWSE PARMLIB SPECIFICATIONS */
/********************************************************************/
CAPNATS (N) /* CAPITALIZE NATIONAL CHARACTERS (Y OR N) */
/* Y - NATIONAL CHARACTERS SHOULD BE */
/* TREATED AS LETTERS FOR */
/* CAPITALIZATION PURPOSES */
/* N - NATIONAL CHARACTERS SHOULD NOT */
/* BE TREATED AS LETTERS FOR */
/* CAPITALIZATION PURPOSES */
.
.
.
ISOLATN (UR) /* ISOLATION LEVEL (CS OR UR) */
/* CS - CURSOR STABILITY */
/* UR - UNCOMMITTED READ */
/*------------------------------------------------------------------*/
Individual users can set a new Isolation Level parameter for RC Browse in the RC/Edit PROFILE Parameters panel. If left blank (the default), the global level setting from the hlq.CDBAPARM(RCEDIT) member is used.
The individual user settings in the RC/Edit Parameters panel are saved on the users own ISPF profile dataset for the users next session.
The RC/Query PROFILE variables screen:
PROCESSING OPTIONS: (For Query Reports)
VIEW LEVELS ==> ALL - For Object Dependency Report.
QUALIFIERS ==> Y (Y/N) - For Obj. Dep. and some Ver. 2.1 cols.
MAX LINES ==> ALL - For List and Detail reports.
FULL DETAIL ==> N (Y/N) - For reports based on multiple tables.
OMIT ORDER BY ==> N (Y/N) - For List and Detail reports.
CA-UTILITIES ==> N (Y/N) - To use CA-Utilities.
SAVED REPORTS ==> Y (Y/N) - To use customized reports.
MATCHCASE ==> ON (ON, - Enables matchcase.
OFF, - Disables matchcase. ( SQL Intensive )
CAPS) - Force object names to upper case.
CAPNATS ==> N (Y/N) - Capitalize National Characters.
ALL LOCATIONS ==> Y (Y/N) - For searches in Plan/Package Report.
UNDERSCORE WILDCARD ==> ON (ON/OFF)- To process underscore as wildcard.
OPTIONS NOTIFY ==> ON (ON/OFF)- For options and notify pop-ups.
PREFILL COMMAND ==> Y (Y/N) - Prefill command line for utility.
ISOLATION LEVEL ==> UR (Blank - Global Level ISOLATION value.
CS, - Cursor Stability.
UR, - Uncommitted Read)
UTILITY OBJECT ==> 1 - Number of objects per step in
COUNT IN 1 STEP utility processing JCL.
The HELP panel for this field:
Field: Isolation Level at User Level
Description: Indicate the isolation level that is appended to the
results of a SELECT statement.
The following values are valid:
CS - Cursor Stability
Allows maximum concurrency with data integrity.WITH CS
is added to the SELECT statement results.
UR - Uncommitted read
Allow an application to read uncommitted data.WITH UR
is added to the SELECT statement results.
Blank - Use the global isolation level specified in the
RCQ parmlib member.
Default : BLANK
The RC/Edit Parameters panel:
PTEDRCU --------- Edit Parmlib Member RCEDIT --------- yyyy/mm/dd hh:mm
OPTION ===>
Type the information in the fields below.
National char capitalization.....> N (Y or N)
Default values for null fields...> N (Y or N)
Enable High speed catalog access.> Y (Y or N)
Enforce lock table option........> Y * (Y or N)
RC/Browse logging level..........> 1 * (0 or 1)
RC/Edit logging level............> 3 * (0,1,2 or 3)
Disable async background fetch...> N (Y or N)
Disable continuous fetch.........> N (Y or N)
Select row limit.................> * (1-99999999 or blank)
Actual microseconds for timestamp> N (Y or N)
RC/Edit Commit Confirmation......> N (Y or N)
RC/Browse isolation level........> UR (CS or UR)
Example SQL that RC/Browse will use when browsing, in this example, a PLAN TABLE where the isolation
level has been set to "UR" in the RC/Edit Parameters:
Isolation level for RC/Browse ==> UR
SELECT "QUERYNO" , QBLOCKNO , APPLNAME , PROGNAME , PLANNO , METHOD , CREATOR
, TNAME , TABNO , ACCESSTYPE , MATCHCOLS , ACCESSCREATOR , ACCESSNAME
, INDEXONLY , SORTN_UNIQ , SORTN_JOIN , SORTN_ORDERBY , SORTN_GROUPBY
, SORTC_UNIQ , SORTC_JOIN , SORTC_ORDERBY , SORTC_GROUPBY , TSLOCKMODE
, TIMESTAMP , REMARKS , PREFETCH , COLUMN_FN_EVAL , MIXOPSEQ , VERSION
, "COLLID" , ACCESS_DEGREE , ACCESS_PGROUP_ID , JOIN_DEGREE ,
JOIN_PGROUP_ID , SORTC_PGROUP_ID , SORTN_PGROUP_ID , PARALLELISM_MODE
, MERGE_JOIN_COLS , CORRELATION_NAME , PAGE_RANGE , JOIN_TYPE ,
GROUP_MEMBER , IBM_SERVICE_DATA , WHEN_OPTIMIZE , QBLOCK_TYPE ,
BIND_TIME , OPTHINT , HINT_USED , PRIMARY_ACCESSTYPE , PARENT_QBLOCKNO
, TABLE_TYPE , TABLE_ENCODE , TABLE_SCCSID , TABLE_MCCSID ,
TABLE_DCCSID , ROUTINE_ID , CTEREF , STMTTOKEN , PARENT_PLANNO ,
BIND_EXPLAIN_ONLY , SECTNOI , EXPLAIN_TIME , MERGC , MERGN ,
SCAN_DIRECTION , EXPANSION_REASON
FROM authid.PLAN_TABLE WITH UR <<<<<<<<<<<<<<< UR is appended using the WITH statement
Additional Information
Feedback
