Enabling QMF CURRENT LOCK TIMEOUT

The CURRENT LOCK TIMEOUT special register specifies the number of seconds to wait for a lock before returning an error indicating that a lock cannot be obtained.

About this task

This special register impacts row, table, alter table, online backup, key value, MDC block, reorg, XML path, extent movement, plan, variation, catalog, insert range, dictionary and table serialize locks.

The data type of the register is INTEGER.

Valid values for the CURRENT LOCK TIMEOUT special register are integers between -1 and 32767, inclusive. This special register can also be set to the null value. A value of -1 specifies that timeouts are not to take place and that the application is to wait until the lock is released or a deadlock is detected. A value of 0 specifies that the application is not to wait for a lock; if a lock cannot be obtained, an error is to be returned immediately.

The value of the CURRENT LOCK TIMEOUT special register can be changed by invoking the SET CURRENT LOCK TIMEOUT statement. The initial value is null; in this case, the current value of the locktimeout database configuration parameter is used when waiting for a lock, and this value is returned for the special register.

Procedure

The SET CURRENT LOCK TIMEOUT statement assigns a value to the CURRENT LOCK TIMEOUT special register: 
        .-CURRENT-. .- = -.
>>--SET-+---------+--LOCK TIMEOUT--+-----+------>
>--+-WAIT--------------------------+-----------<
   |-NOT WAIT----------------------|
   |-NULL--------------------------|
   | .-WAIT-. |
   |-+------+---integer-constant---|
   '-variable----------------------'
WAIT
Specifies a value of -1, which means that the database manager waits until a lock is released, or a deadlock is detected. When a lock cannot be obtained an error is returned.
NOT WAIT
Specifies a value of 0, which means that the database manager does not wait for locks that cannot be obtained. When a lock cannot be obtained an error is returned.
NULL
Specifies that the database manager uses the current value of the IRLMRWT subsystem parameter when waiting for a resource. The value returned for the special register is the current value of the IRLMRWT subsystem parameter, and this value changes as the value of the subsystem parameter changes. When a lock cannot be obtained an error is returned.
WAIT integer-constant
Specifies an integer value between -1 and 32767 (SQLSTATE 428B7, SQLCODE -490 detected by parser). 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. When a lock cannot be obtained after the specified number of seconds, an error is returned.
Note: When specifying a SET CURRENT LOCK TIMEOUT statement for a profile (in the ATTRIBUTE1 column of the 4.6.1 DSN_PROFILE_ATTRIBUTES table) with a numeric value, do not include the WAIT keyword. Instead, specify the numeric value. For a user profile, the WAIT keyword must not be followed by a numeric value. For example: SET CURRENT LOCK TIMEOUT = 500
variable

A variable that contains an integer value between -1 and 32767 (SQLSTATE 42821, SQLCODE -408 for invalid data type; SQLSTATE 42815, SQLCODE -713 detected by runtime for out of range). When a lock cannot be obtained after the number of seconds specified by the variable an error is returned.

If host-variable is specified with an associated indicator variable, and the value of that indicator variable is a null value, the CURRENT LOCK TIMEOUT value is reset to the initial value. This is equivalent to specifying the NULL keyword.

Notes
Considerations for the SPREG_LOCK_TIMEOUT_MAX subsystem parameter: The rules for assigning a value to this special register depend on the value of the SPREG_LOCK_TIMEOUT_MAX subsystem parameter, which gives an installation a way to limit the values that can be assigned to this special register. If the value of the SPREG_LOCK_TIMEOUT_MAX subsystem parameter is not -1 (which allows for unlimited waiting), the following rules must be met (SQLSTATE 42815, SQLCODE -713).
  • an integer value must not be greater than the value of the SPREG_LOCK_TIMEOUT_MAX subsystem parameter.
  • WAIT without an integer value, or WAIT -1, must not be specified.
  • NULL must not be specified if the value of the SPREG_LOCK_TIMEOUT_MAX subsystem parameter is less than the value of the IRLMRWT subsystem parameter.

Example

  1. Set the lock timeout value to wait for 30 seconds before returning an error.

    SET CURRENT LOCK TIMEOUT = 30

  2. Reset the lock timeout value to the initial value, so that the IRLMRWT subsystem parameter value is used when waiting for a resource.

    SET CURRENT LOCK TIMEOUT = NULL