A data guarantee level can be specified for each UAP. To specify a data guarantee level for each SQL statement, specify a lock option for that SQL statement.

If both a data guarantee level and a lock option are specified concurrently, the lock option specification becomes valid. Table 4-18 shows the relationships between data guarantee level and lock option.

Table 4-18 Relationship between data guarantee level and lock option

Data guarantee level	Lock option
0	WITHOUT LOCK NOWAIT1, 3
1	WITHOUT LOCK WAIT3
2	WITH SHARE LOCK or EXCLUSIVE LOCK2

¹ When the FOR UPDATE clause is specified in a cursor declaration or a dynamic SELECT statement, a data guarantee level of 1 is assumed even if 0 is specified.

² When the FOR UPDATE clause is specified in a cursor declaration or a dynamic SELECT statement, WITH EXCLUSIVE LOCK is assumed.

³ WITH EXCLUSIVE LOCK is assumed in the following cases:

• YES is specified in client environment definition PDFORUPDATEEXLOCK when a cursor declaration or dynamic SELECT statement in which the FOR UPDATE clause is specified is executed.
• FOR UPDATE EXLOCK is specified immediately after the data guarantee level in the SQL compile options that are specified when the routine is defined.

The data guarantee level can be specified at the following locations:

• PDISLLVL in the client environment definition
• SQL compile option in ALTER PROCEDURE
• SQL compile option in ALTER ROUTINE
• SQL compile option in ALTER TRIGGER
• SQL compile option in CREATE PROCEDURE
• SQL compile option in CREATE TRIGGER
• SQL compile option in procedure body of CREATE TYPE