CHANGE ISOLATION LEVEL command

The CHANGE ISOLATION LEVEL command changes the way that Db2® isolates data from other processes while a database is being accessed.

Authorization

None

Required connection

None

Command syntax

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. 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).