DB2 10.5 for Linux, UNIX, and Windows

isolation-clause (subselect query)

The optional isolation-clause specifies the isolation level at which the subselect or fullselect is run, and whether a specific type of lock is to be acquired.


>>---WITH--+-RR--+---------------------+-+---------------------><
           |     '-lock-request-clause-' |     
           +-RS--+---------------------+-+     
           |     '-lock-request-clause-' |     
           +-CS--------------------------+     
           '-UR--------------------------'

RR - Repeatable Read
RS - Read Stability
CS - Cursor Stability
UR - Uncommitted Read

lock-request-clause


>>-USE AND KEEP--+-SHARE-----+--LOCKS--------------------------><
                 +-UPDATE----+          
                 '-EXCLUSIVE-'

The lock-request-clause applies only to queries and to positioning read operations within an insert, update, or delete operation. The insert, update, and delete operations themselves will run using locking determined by the database manager.

The optional lock-request-clause specifies the type of lock that the database manager is to acquire and hold:

SHARE: Concurrent processes can acquire SHARE or UPDATE locks on the data.
UPDATE: Concurrent processes can acquire SHARE locks on the data, but no concurrent process can acquire an UPDATE or EXCLUSIVE lock.
EXCLUSIVE: Concurrent processes cannot acquire a lock on the data.

isolation-clause restrictions:

The isolation-clause is not supported for a CREATE TABLE or ALTER TABLE statement (SQLSTATE 42601).
The isolation-clause cannot be specified for a subselect or fullselect that will cause trigger invocation, referential integrity scans, or MQT maintenance (SQLSTATE 42601).
A subselect or fullselect cannot include a lock-request-clause if that subselect or fullselect references any SQL functions that are not declared with the option INHERIT ISOLATION LEVEL WITH LOCK REQUEST (SQLSTATE 42601).
A subselect or fullselect that contains a lock-request-clause are not be eligible for MQT routing.
If an isolation-clause is specified for a subselect or fullselect within the body of an SQL function, SQL method, or trigger, the clause is ignored and a warning is returned.
If an isolation-clause is specified for a subselect or fullselect that is used by a scrollable cursor, the clause is ignored and a warning is returned.
Neither isolation-clause nor lock-request-clause can be specified in the context where they will cause conflict isolation or lock intent on a common table expression (SQLSTATE 42601). This restriction does not apply to aliases or base tables. The following examples create an isolation conflict on a and returns an error:
- View:
```
create view a as (...);
(select * from a with RR USE AND KEEP SHARE LOCKS)
UNION ALL 
(select * from a with UR);
```
- Common table expression:
```
WITH a as (...)
(select * from a with RR USE AND KEEP SHARE LOCKS)
UNION ALL 
(select * from a with UR);
```
An isolation-clause cannot be specified in an XML context (SQLSTATE 2200M).
The WITH clause specifying isolation cannot be specified at a subselect level in any statement that accesses a column-organized table (SQLSTATE 42858).