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. The following table shows the lock option for each data guarantee level.
Table 4-19 Relationship between data guarantee level and lock option
Data guarantee level |
Lock option |
0 |
WITHOUT LOCK NOWAIT#1, #3 |
1 |
WITHOUT LOCK WAIT#3 |
2 |
WITH SHARE LOCK or EXCLUSIVE LOCK#2 |
- #1: 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.
- #2: When the FOR UPDATE clause is specified in a cursor declaration or a dynamic SELECT statement, WITH EXCLUSIVE LOCK is assumed.
- #3: 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
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.