isolation-clause

The isolation-clause specifies an isolation level at which the select statement is executed.


>>-WITH--+-NC------------------+-------------------------------><
         +-UR------------------+   
         +-CS--+------------+--+   
         |     '-KEEP LOCKS-'  |   
         +-RS--+-------------+-+   
         |     '-lock-clause-' |   
         '-RR--+-------------+-'   
               '-lock-clause-'     

lock-clause

|--USE AND KEEP EXCLUSIVE LOCKS---------------------------------|

RR

Repeatable Read

USE AND KEEP EXCLUSIVE LOCKS: Exclusive row locks are acquired and held until a COMMIT or ROLLBACK statement is executed.

RS

Read Stability

USE AND KEEP EXCLUSIVE LOCKS

Exclusive row locks are acquired and held until a COMMIT or ROLLBACK statement is executed. The USE AND KEEP EXCLUSIVE LOCKS clause is only allowed in the isolation-clause in the following SQL statements:

DECLARE CURSOR
FOR
select-statement
SELECT INTO
PREPARE in the ATTRIBUTES string

It is not allowed on updatable cursors.

CS

Cursor Stability

KEEP LOCKS: The KEEP LOCKS clause specifies that any read locks acquired will be held for a longer duration. Normally, read locks are released when the next row is read. If the isolation clause is associated with a cursor, the locks will be held until the cursor is closed or until a COMMIT or ROLLBACK statement is executed. Otherwise, the locks will be held until the completion of the SQL statement.

UR

Uncommitted Read

NC

No Commit

If isolation-clause is not specified, the default isolation is used with the exception of a default isolation level of uncommitted read. See Isolation level for a description of how the default is determined.

Exclusive locks: The USE AND KEEP EXCLUSIVE LOCKS clause should be used with caution. If it is specified, the exclusive row locks that are acquired on rows will prevent concurrent access to those rows by other users running COMMIT(*CS), COMMIT(*RS), and COMMIT(*RR) till the end of the unit of work. Concurrent access by users running COMMIT(*NC) or COMMIT(*UR) is not prevented.

Keyword Synonyms: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

The keyword NONE can be used as a synonym for NC.
The keyword CHG can be used as a synonym for UR.
The keyword ALL can be used as a synonym for RS.