Command Reference

CHANGE ISOLATION LEVEL

Changes the way that DB2 isolates data from other processes while a database is being accessed.

Authorization

None

Required Connection

None

Command Syntax

                                  .-CS--.
>>-CHANGE----+-SQLISL----+--TO----+-NC--+----------------------><
             '-ISOLATION-'        +-RR--+
                                  +-RS--+
                                  '-UR--'
 

Command Parameters

TO

CS

Specifies cursor stability as the isolation level.

NC

Specifies no commit as the isolation level. Not supported by DB2.

RR

Specifies repeatable read as the isolation level.

RS

Specifies read stability as the isolation level.

UR

Specifies uncommitted read as the isolation level.

Usage Notes

DB2 uses isolation levels to maintain data integrity in a database. The isolation level defines the degree to which an application process is isolated (shielded) from changes made by other concurrently executing application processes.

If a selected isolation level is not supported by a database, it is automatically escalated to a supported level at connect time.

Isolation level changes are not permitted while connected to a database with a type 1 connection (see SET CLIENT). The back end process must be terminated before isolation level can be changed:

   db2 terminate
   db2 change isolation to ur 
   db2 connect to sample

Changes are permitted using a type 2 connection, but should be made with caution, because the changes will apply to every connection made from the same command line processor back-end process. The user assumes responsibility for remembering which isolation level applies to which connected database.

In the following example, a user is in DB2 interactive mode following creation of the SAMPLE database:

   update command options using c off
   catalog db sample as sample2
 
   set client connect 2
 
   connect to sample
   connect to sample2
 
   change isolation to cs
   set connection sample
   declare c1 cursor for select * from org
   open c1
   fetch c1 for 3 rows
 
   change isolation to rr
   fetch c1 for 2 rows

An SQL0514N error occurs because c1 is not in a prepared state for this isolation level.

   change isolation to cs
   set connection sample2
   fetch c1 for 2 rows

An SQL0514N error occurs because c1 is not in a prepared state for this database.

   declare c1 cursor for select division from org

A DB21029E error occurs because cursor c1 has already been declared and opened.

   set connection sample
   fetch c1 for 2 rows

This works because the original database (SAMPLE) was used with the original isolation level (CS).

For more information about isolation levels, see the SQL Reference.

See Also

QUERY CLIENT.