Using locks for data consistency

Locks can ensure that data remains consistent even when multiple users try to access the same data at the same time. You can use locks to ensure that only one user is privileged to change data at a given time and that no user is privileged to access uncommitted data.

About this task

If you use repeatable read (RR), read stability (RS), or cursor stability (CS) as your isolation level, Db2 automatically controls access to data by using locks. However, if you use uncommitted read (UR) as your isolation level, users can access uncommitted data and introduce inconsistent data. As an auditor, you must know the applications that use UR isolation and that can introduce inconsistent data or create security risks.

Begin general-use programming interface information. For static SQL, you can determine the plans and packages that use UR isolation by querying the catalog.

For dynamic SQL statements, turn on performance trace class 3 to determine which plans and packages use UR isolation. End general-use programming interface information.

Consistency between systems: When an application program writes data to both Db2 and IMS, or to both Db2 and CICS®, the subsystems prevent concurrent use of data until the program declares a point of consistency.

Examples

Example

For static SQL statements, use the following query to determine which plans use UR isolation:

SELECT DISTINCT Y.PLNAME
   FROM SYSIBM.SYSPLAN X, SYSIBM.SYSSTMT Y
   WHERE (X.NAME = Y.PLNAME AND X.ISOLATION = 'U')
       OR Y.ISOLATION = 'U'
   ORDER BY Y.PLNAME;

Example

For static SQL statements, use the following query to determine which packages use UR isolation:

SELECT DISTINCT Y.COLLID, Y.NAME, Y.VERSION
   FROM SYSIBM.SYSPACKAGE X, SYSIBM.SYSPACKSTMT Y
   WHERE (X.LOCATION = Y.LOCATION AND
          X.LOCATION = ' '        AND
          X.COLLID   = Y.COLLID   AND
          X.NAME     = Y.NAME     AND
          X.VERSION  = Y.VERSION  AND
          X.ISOLATION = 'U')
       OR Y.ISOLATION = 'U'
   ORDER BY Y.COLLID, Y.NAME, Y.VERSION;