Isolation level to be used for queries when browsing DB2 tables.

Document ID : KB000076508
Last Modified Date : 26/06/2018
Show Technical Document Details
Introduction:
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 minimised.

 
Background:
The R20 release of CA 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:
DB2 for Z/OS
Instructions:
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:

ISOLATN  (CS)            /* 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 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.

Excerpt from the RC/Edit Parameters panel:

Isolation level for RC/Browse        ==>       BLANK - Global Isolation level
                                                                  CS - Cursor stability
                                                                  UR - Uncommitted read


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: