isolation-clause

The isolation-clause specifies the isolation level at which the statement is executed. (Isolation level does not apply to declared temporary tables because no locks are acquired.)

isolation-clause

lock-clause

RR

Repeatable read

RR lock-clause

Repeatable read, using and keeping the type of lock that is specified in lock-clause on all accessed pages and rows

RS

Read stability

RS lock-clause

Read stability, using and keeping the type of lock that is specified in lock-clause on all accessed pages and rows

CS

Cursor stability

UR

Uncommitted read

lock-clause

Specifies the type of lock.

USE AND KEEP EXCLUSIVE LOCKS
USE AND KEEP UPDATE LOCKS
USE AND KEEP SHARE LOCKS: Specifies that Db2 is to acquire and hold X, U, or S locks, respectively.

WITH UR can be specified only if the result table of the fullselect or the SELECT INTO statement is read-only.

In an ODBC application, the SQLSetStmtAttr function can be used to set statement attributes that interact with the lock-clause. If SQLSetStmtAttr is invoked with a cursor's statement handle and specifying that its SQL_ATTR_CLOSE_BEHAVIOR is SQL_CC_RELEASE (locks are to be released when the cursor is closed), then irrespective of any lock-clause, lock used by the cursor that are not needed to protect the integrity of changed data are released..

Although requesting an UPDATE or EXCLUSIVE LOCK can reduce concurrency, it can prevent some types of deadlocks.

The default isolation level of the statement depends on:

The isolation of the package or plan that the statement is bound in
Whether the result table is read-only

Table 1 shows the default isolation level of the statement.

Table 1. Default isolation level based on the isolation level of the package or plan and whether the result table is read-only
If package isolation is:	And plan isolation is:	And the result table is:	Then the default isolation is:
RR	Any	Any	RR
RS	Any	Any	RS
CS	Any	Any	CS
UR	Any	Read-only	UR
UR	Any	Not read-only	CS
Not specified	Not specified	Any	RR
	RR	Any	RR
	RS	Any	RS
	CS	Any	CS
	UR	Read-only	UR
	UR	Not read-only	CS

A simple way to ensure that a result table is read-only is to specify FOR READ ONLY in the SQL statement.

Alternative syntax and synonyms: KEEP UPDATE LOCKS can be specified as a synonym for USE AND KEEP EXCLUSIVE LOCKS. However, KEEP UPDATE LOCKS can be specified only if FOR UPDATE OF is specified, and it is not supported in the SELECT INTO statement.