Statement level isolation in a federated system

For federated data sources, you must use the WITH isolation clause to specify the isolation of a statement.

You must use the WITH isolation clause in your statement if you want to use statement level isolation. If you use attributes with the Call Level Interface (CLI) or other application API for statement level isolation, it does not affect statement isolation.

The data sources that support statement level isolation in a federated system are the Db2® family of products and Microsoft SQL server. The statement isolation is sent to remote data sources for the Db2 family of products and SQL server.

Use the DB2_STATEMENT_ISOLATION server option to turn on or off statement level isolation. You can specify this option in the CREATE SERVER and ALTER SERVER statements. The server option is automatically set to Y.

You can use the WITH isolation clause in these statements:

  • SELECT
  • SELECT INTO
  • Searched DELETE
  • INSERT
  • Searched UPDATE
  • DECLARE CURSOR

Lock request clause

You can use the lock request clause in a SELECT or SELECT INTO statement. The federated data sources that support the lock request clause are the Db2, and the Db2 for z/OS®.

Limitations using the WITH clause to set the isolation level

The following conditions apply to isolation levels that are specified for statements:

  • The WITH clause cannot be used in subqueries.
  • The UR isolation level applies only if the result table of the fullselect or the SELECT INTO statement is read-only. In other cases, the UR isolation level for the statement is changed from UR to CS for the Db2 family of data sources. For the SQL server data source, the UR isolation level is upgraded to Read committed.
  • If you specify the lock request clause for the following data sources, the clause is ignored by the federated server.
    • Db2 for IBM® i
    • Db2 for VM
    • Microsoft SQL server