Because the isolation level determines how data is isolated
from other processes while the data is being accessed, you should
select an isolation level that balances the requirements of concurrency
and data integrity.
About this task
The isolation level that you specify is in effect for
the duration of the unit of work (UOW). The following heuristics are
used to determine which isolation level will be used when compiling
an SQL or XQuery statement:
- For static SQL:
- If an isolation-clause is specified
in the statement, the value of that clause is used.
- If an isolation-clause is not specified
in the statement, the isolation level that was specified for the package
when the package was bound to the database is used.
- For dynamic SQL:
- If an isolation-clause is specified
in the statement, the value of that clause is used.
- If an isolation-clause is not specified
in the statement, and a SET CURRENT ISOLATION statement has been issued
within the current session, the value of the CURRENT ISOLATION special
register is used.
- If an isolation-clause is not specified
in the statement, and a SET CURRENT ISOLATION statement has not been
issued within the current session, the isolation level that was specified
for the package when the package was bound to the database is used.
- For static or dynamic XQuery statements, the isolation level of
the environment determines the isolation level that is used when the
XQuery expression is evaluated.
Note: Many commercially-written applications provide a method
for choosing the isolation level. Refer to the application documentation
for information.
The isolation level can be specified in
several different ways.
Procedure
- At the statement level:
Note: Isolation
levels for XQuery statements cannot be specified at the statement
level.
Use the WITH clause. The WITH clause cannot be used
on subqueries. The WITH UR option applies to read-only operations
only. In other cases, the statement is automatically changed from
UR to CS.
This isolation level overrides the isolation level
that is specified for the package in which the statement appears.
You can specify an isolation level for the following SQL statements:
- DECLARE CURSOR
- Searched DELETE
- INSERT
- SELECT
- SELECT INTO
- Searched UPDATE
- For dynamic SQL within the current session:
Use the SET CURRENT ISOLATION statement to set the isolation
level for dynamic SQL issued within a session. Issuing this statement
sets the CURRENT ISOLATION special register to a value that specifies
the isolation level for any dynamic SQL statements that are issued
within the current session. Once set, the CURRENT ISOLATION special
register provides the isolation level for any subsequent dynamic SQL
statement that is compiled within the session, regardless of which
package issued the statement. This isolation level is in effect until
the session ends or until the SET CURRENT ISOLATION...RESET statement
is issued.
- At precompile or bind time:
For an
application written in a supported compiled language, use the ISOLATION
option of the PREP or BIND commands.
You can also use the sqlaprep or sqlabndx API
to specify the isolation level.
- If you create a bind file at precompile time, the isolation level
is stored in the bind file. If you do not specify an isolation level
at bind time, the default is the isolation level that was used during
precompilation.
- If you do not specify an isolation level, the default level of
cursor stability (CS) is used.
To determine the isolation level of a package, execute the
following query:
select isolation from syscat.packages
where pkgname = 'pkgname'
and pkgschema = 'pkgschema'
where
pkgname is
the unqualified name of the package and
pkgschema is
the schema name of the package. Both of these names must be specified
in uppercase characters.
- When working with JDBC or SQLJ at run time:
Note: JDBC and SQLJ are implemented with CLI on DB2® servers,
which means that the db2cli.ini settings might
affect what is written and run using JDBC and SQLJ.
To create
a package (and specify its isolation level) in SQLJ, use the SQLJ
profile customizer (db2sqljcustomize command).
- From CLI or ODBC at run time:
Use
the CHANGE ISOLATION LEVEL command. With DB2 Call-level
Interface (CLI), you can change the isolation level as part of the
CLI configuration. At run time, use the SQLSetConnectAttr function
with the SQL_ATTR_TXN_ISOLATION attribute to set the transaction isolation
level for the current connection referenced by the ConnectionHandle argument.
You can also use the TXNISOLATION keyword in the db2cli.ini file.
- On database servers that support REXX:
When a database is created, multiple bind files that support
the different isolation levels for SQL in REXX are
bound to the database. Other command line processor (CLP) packages
are also bound to the database when a database is created.
REXX and
the CLP connect to a database using the default CS isolation level.
Changing this isolation level does not change the connection state.
To
determine the isolation level that is being used by a REXX application,
check the value of the SQLISL predefined REXX variable.
The value is updated each time that the CHANGE ISOLATION
LEVEL command executes.