DB2 10.5 for Linux, UNIX, and Windows

SET CURRENT LOCK TIMEOUT statement

The SET CURRENT LOCK TIMEOUT statement changes the value of the CURRENT LOCK TIMEOUT special register.

This statement is not under transaction control.

Invocation

The statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax


        .-CURRENT-.                .-=-.   
>>-SET--+---------+--LOCK TIMEOUT--+---+------------------------>

>--+-WAIT-----------------------+------------------------------><
   +-NOT WAIT-------------------+   
   +-NULL-----------------------+   
   | .-WAIT-.                   |   
   +-+------+--integer-constant-+   
   '-host-variable--------------'

Description

The specified value must be an integer between -1 and 32767, inclusive (SQLSTATE 428B7), or the null value.

WAIT: Specifies a CURRENT LOCK TIMEOUT value of -1, which means that the database manager is to wait until a lock is released, or a deadlock is detected (SQLSTATE 40001 or 57033).
NOT WAIT: Specifies a CURRENT LOCK TIMEOUT value of 0, which means that the database manager is not to wait for locks that cannot be obtained, and an error (SQLSTATE 40001 or 57033) will be returned.
NULL: Specifies that the CURRENT LOCK TIMEOUT value is to be unset, and that the value of the locktimeout database configuration parameter is to be used when waiting for a lock. The value that is returned for the special register will change as the value of locktimeout changes.
WAIT integer-constant: Specifies an integer value between -1 and 32767. A value of -1 is equivalent to specifying the WAIT keyword without an integer value. A value of 0 is equivalent to specifying the NOT WAIT clause. If the value is between 1 and 32767, the database manager will wait that number of seconds (if a lock cannot be obtained) before an error (SQLSTATE 40001 or 57033) is returned.
host-variable: A variable of type INTEGER. The value must be between -1 and 32767. If host-variable has an associated indicator variable, and the value of that indicator variable specifies a null value, the CURRENT LOCK TIMEOUT value is unset. This is equivalent to specifying the NULL keyword.

Notes

An updated value of the special register takes effect immediately upon successful execution of this statement. Because the special register value that is to be used during statement execution is fixed at the beginning of statement execution, an updated value of the CURRENT LOCK TIMEOUT special register will only be returned by statements that start execution after the SET LOCK TIMEOUT statement has completed successfully.
Syntax alternatives: The following syntax alternatives are supported for compatibility with Informix® database products. These alternatives are non-standard and should not be used.
- MODE can be specified in place of TIMEOUT.
- TO can be specified in place of the equals (=) operator.

Examples

Example 1: Set the lock timeout value to wait for 30 seconds before returning an error.
```
   SET CURRENT LOCK TIMEOUT 30
```
Example 2: Unset the lock timeout value, so that the locktimeout database configuration parameter value will be used instead.
```
  SET CURRENT LOCK TIMEOUT NULL
```