ISOLATION bind option

The ISOLATION option determines how far to isolate an application from the effects of other running applications.

ISOLATION ( CS )( RS )( RR )( UR )( NC ) On: BIND and REBIND PLAN and PACKAGE, REBIND TRIGGER PACKAGE

Start of changeNot valid for REBIND of a native SQL procedure package.End of change
( CS )
Cursor stability. Ensures, like repeatable read, that your application does not read a row that another process changes until that process releases that row. Unlike repeatable read, cursor stability does not prevent other applications from changing rows that your application reads before your program commits or terminates.
( RR )
Repeatable read. Ensures that:
  • Your application does not read a row that another process has changed until that process releases that row.
  • Other processes do not change a row that your application reads until your application commits or terminates.
( RS )
Read stability. Ensures that:
  • Your application does not read a row that another process has changed until that process releases that row.
  • Other processes do not change a row that satisfies the application's search condition until your application commits or terminates. It does allow other application processes to insert a row, or to change a row that did not originally satisfy the search condition.

If the server does not support RS, it uses RR.

( UR )
Uncommitted read. Unlike repeatable read and cursor stability, does not ensure anything. With the exception of LOB data, uncommitted read avoids acquiring locks on data and allows:
  • Other processes change any row your application reads during the unit of work.
  • Your application read any row that another process has changed, even if the process has not committed the row.

You can use this option only with a read-only operation: SELECT, SELECT INTO, or FETCH using a read-only cursor. If you specify ISOLATION(UR) for any other operation, DB2® uses ISOLATION(CS) for that operation.

( NC )
No commit. Used on packages that are bound to certain servers other than DB2 for z/OS®. DB2 for z/OS does not support NC. If the server does not support this isolation level, it uses UR.

Defaults:

Process Default value
BIND PLAN CS
BIND PACKAGE
  • For a local server: The plan value
  • For a remote server: CS
REBIND PLAN Existing value
REBIND PACKAGE Existing value. You cannot change ISOLATION from a specified value to a default of the plan value by using REBIND PACKAGE. To do that, you must use BIND PACKAGE ACTION(REPLACE).

Catalog record: Column ISOLATION of tables SYSPACKAGE and SYSPLAN.