Isolation Levels with Informix XPS and DB2 UDB

Published 12 January 2005

More options


Authors: Chuck Ballard


Locking isolation levels affect the behavior of SQL statements. This Technote discusses locking isolation levels in DB2 and XPS.


Locking isolation levels affect the behavior of SQL statements. In XPS the default isolation behavior is dependent on database type, but DB2 does not use database types. All DB2 databases are of the same type with a default isolation of Cursor Stability (CS). Both XPS and DB2 allow you to override the default isolation level.

Unless your application explicitly directs isolation level, a transition from XPS to DB2 will generally result in a change in locking behavior. If you have an XPS non-log-mode-ANSI database, your default isolation level will go from committed read to cursor stability, which is a more restrictive isolation level. If you have an XPS unlogged database, your default isolation level will go from dirty read to cursor stability (a very significant change). Depending on your application, you may end up with an increase in overall database locking requirements, and thus you may need to increase lock resources.

Both XPS and DB2 support a SET ISOLATION LEVEL statement. XPS uses the keyword “TO” where DB2 prefers an equal sign (=), but now also accepts the keyword “TO” for compatibility with XPS.

The DB2 equivalent of the XPS dirty read isolation level is called uncommitted read. Many XPS applications set isolation to dirty read. For compatibility reasons DB2 now supports the phrase dirty read as a substitute for uncommitted read.



The SET ISOLATION statement assigns a value to the CURRENT ISOLATION special register. This statement is not under transaction control, and the register setting is retained across COMMIT boundaries. This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. No particular authorization is required to execute it.



The value of the CURRENT ISOLATION special register is replaced by the specified value or set to blanks if RESET is specified.

The following syntax is also supported:

  1. The word CURRENT is optional *
  2. The equals sign is optional — but TO can be specified in place of the equal sign (=) *
  3. DIRTY READ can be specified in place of UR *
  4. READ UNCOMMITTED can be specified in place of UR *
  5. READ COMMITTED is recognized and upgraded to CS *
  6. CURSOR STABILITY can be specified in place of CS *
  7. REPEATABLE READ can be specified in place of RR *
  8. SERIALIZABLE can be specified in place of RR

The asterisked items (*) are for compatibility with IBM Informix SQL syntax.

Tip: DB2 also supports specifying the isolation level of a particular SQL statement by using a WITH UU (or RR or CS or RS) clause at the end of the SELECT statement. Example:

SELECT count(*) FROM SysCat.Tables WHERE Type in ('A')

Special Notices

This material has not been submitted to any formal IBM test and is published AS IS. It has not been the subject of rigorous review. IBM assumes no responsibility for its accuracy or completeness. The use of this information or the implementation of any of these techniques is a client responsibility and depends upon the client's ability to evaluate and integrate them into the client's operational environment.

Follow IBM Redbooks

Follow IBM Redbooks