Specifying the isolation level

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.

• At the statement or subselect level:
  Note: Isolation levels for XQuery statements cannot be specified at the statement level.

  Use the WITH clause. 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.

• Changing the default isolation level used for new sessions:
  The normal default isolation level used for dynamic SQL within a new session is determined by the isolation level of the package being used in that session. While the application can change this value during its processing, the database administrator can also change the default isolation level outside of the application by either implementing:

  • a customized CONNECT procedure (https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0057372.html)
  • setting the DB2_DEFAULT_ISOLATION_VALUE registry variable (https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.regvars.doc/doc/r0005657.html) and, if desired, the DB2_BYPASS_DEFAULT_ISOLATION_APPS, DB2_BYPASS_DEFAULT_ISOLATION_GROUPS, or DB2_BYPASS_DEFAULT_ISOLATION_USERS registry variable.