RECORD_LOCK_INFO view

The RECORD_LOCK_INFO view returns one row for every record lock for the partition.

The values returned for the columns in the view are closely related to the values returned by Retrieve Record Locks API. Refer to the APIs for more detailed information.

When querying this view, you should use a WHERE clause to restrict the result set to avoid excessive use of system resources.

The following table describes the columns in the view. The schema is QSYS2.

Table 1. RECORD_LOCK_INFO view
Column Name	System Column Name	Data Type	Description
TABLE_SCHEMA	TABSCHEMA	VARCHAR(128)	Name of the schema.
TABLE_NAME	TABNAME	VARCHAR(128)	Name of the table.
TABLE_PARTITION	TABPART	VARCHAR(128)	Name of the table partition or member that contains the locked record.
SYSTEM_TABLE_SCHEMA	SYS_DNAME	VARCHAR(10)	System name of the schema.
SYSTEM_TABLE_NAME	SYS_TNAME	VARCHAR(10)	System name of the table
SYSTEM_TABLE_MEMBER	SYS_MNAME	VARCHAR(10)	The name of the member that contains the locked record.
RELATIVE_RECORD_NUMBER	RRN	BIGINT	The relative record number (RRN) of the record that is locked.
LOCK_STATE	LOCK_STATE	VARCHAR(8)	The lock condition for the record. READ The record is locked for read. Another job may read the same record but cannot lock the record for update intent. The record cannot be changed by another job as long as one job holds a read lock on the record. UPDATE The record is locked for update intent. Another job may read the record but may not obtain a read or update lock on it until the lock is released. INTERNAL The row is locked internally for read. For a short time the operating system holds an internal lock to access the row. Another job may read the same row and may even have the row locked for update intent. However, if another job does have the row locked for update intent, the actual change of the row will not proceed until the internal lock is released.
LOCK_STATUS	STATUS	VARCHAR(9)	The status of the lock. HELD The lock is currently held by the job. WAITING The job is waiting for the lock.
LOCK_SCOPE	LOCK_SCOPE	VARCHAR(10)	The scope of the lock. Values are: JOB THREAD LOCK SPACE
JOB_NAME	JOB_NAME	VARCHAR(28)	The qualified job name.
THREAD_ID	THREAD_ID	BIGINT Nullable	The thread that is associated with the lock. If a held lock is job scoped, returns the null value. If a held lock is thread scoped, contains the identifier for the thread holding the lock. If the scope of the lock is to the lock space and the lock is not held, contains the identifier of the thread requesting the lock. If the lock is requested but not yet available, contains the identifier of the thread requesting the lock.
LOCK_SPACE_ID	LOCKID	BINARY(20) Nullable	When the LOCK_SCOPE column value is `LOCK SPACE` and the lock is being waited on by a thread, contains the lock space ID value for which the lock is being waited on. Otherwise, contains the null value.

Example

Review the jobs that are updating the SALES table:

SELECT JOB_NAME, COUNT(*) AS ROWS_UPDATING
  FROM QSYS2.RECORD_LOCK_INFO
  WHERE SYSTEM_TABLE_NAME = 'SALES' AND
        SYSTEM_TABLE_SCHEMA = 'TOYSTORE' AND
        LOCK_STATE = 'UPDATE'
  GROUP BY JOB_NAME
  ORDER BY ROWS_UPDATING DESC