Document Actions

How to perform queries using isolation levels

The isolation level can be specified in several different ways.

The following heuristics are used in determining which isolation level will be used in compiling an SQL statement:

Static SQL:

If an isolation clause is specified in the statement, then the value of that clause is used.

If no isolation clause is specified in the statement, then the isolation level used is the one specified for the package at the time when the package was bound to the database.

Dynamic SQL:

If an isolation clause is specified in the statement, then the value of that clause is used.

If no isolation clause is specified in the statement, and a SET CURRENT ISOLATION statement has been issued within the current session, then the value of the CURRENT ISOLATION special register is used.

If no isolation clause is specified in the statement, and no SET CURRENT ISOLATION statement has been issued within the current session, then the isolation level used is the one specified for the package at the time when the package was bound to the database.

Procedure To specify the isolation level:

Use the WITH clause. The statement-level isolation level overrides the isolation level specified for the package in which the statement appears.

You can specify an isolation level for the following SQL statements:

SELECT

SELECT INTO

Searched DELETE

INSERT

Searched UPDATE

DECLARE CURSOR

The following conditions apply to isolation levels specified for statements:

The WITH clause cannot be used on subqueries

The WITH UR option applies only to read-only operations. In other cases, the statement is automatically changed from UR to CS.

Repeatable Read = RR

Read Stability = RS

Cursor Stability = CS

Uncommitted Read = UR

Example:

db2 select col1, col2 from tablex with ur

DBA to DBA

Personal tools