OBJECT_LOCK_INFO view

The OBJECT_LOCK_INFO view returns one row for every lock held for every object on the partition in *SYSBAS and in the current thread's ASP group.

The values returned for the columns in the view are closely related to the values returned by Retrieve Lock Information API and Retrieve Lock Request Information API. Refer to the APIs for more detailed information.

Authorization:

  • For a *JOBQ or *OUTQ object the caller must have:
    • *EXECUTE authority to the library and one of the following
      • Some authority to the object, or
      • *SPLCTL special authority, or
      • *JOBCTL special authority and the queue has OPRCTL(*YES), or
      • Authorization to the QIBM_LIST_ALL_OBJS_SQL function usage identifier.
  • For an *AUTL object the caller must have:
    • Some authority to the object, or
    • Ownership of the object, or
    • *AUTLMGT authority for the object, or
    • Authorization to the QIBM_DB_SECADM or QIBM_LIST_ALL_OBJS_SQL function usage identifier.
  • For a *USRPRF object the caller must have some authority to the user profile.
  • For any other object type the caller must have:
    • *EXECUTE authority to the library and one of the following
      • Some authority to the object, or
      • Authorization to the QIBM_LIST_ALL_OBJS_SQL function usage identifier.

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

Table 1. OBJECT_LOCK_INFO view
Column Name System Column Name Data Type Description
OBJECT_SCHEMA OSCHEMA VARCHAR(128) The name of the schema containing the object.
OBJECT_NAME NAME VARCHAR(128) The name of the object.
SYSTEM_OBJECT_SCHEMA SYS_DNAME VARCHAR(10) The system library name of the object.
SYSTEM_OBJECT_NAME SYS_ONAME VARCHAR(10) The system name of the object
SYSTEM_TABLE_MEMBER SYS_MNAME VARCHAR(10)
Nullable
 The name of the member that is locked in the file.

Contains the null value if the lock information is not for a member.
OBJECT_TYPE OBJTYPE VARCHAR(8) The system object type of the locked object.
SQL_OBJECT_TYPE SQLTYPE VARCHAR(9)
Nullable
 The SQL type of the object. Values are:
  • ALIAS
  • FUNCTION
  • INDEX
  • PACKAGE
  • PROCEDURE
  • ROUTINE
  • SEQUENCE
  • TABLE
  • TRIGGER
  • TYPE
  • VARIABLE
  • VIEW
  • XSR

Contains the null value if the object is not an SQL object or the caller has insufficient authority to the object.
ASP_NUMBER ASPNUM INTEGER The numeric identifier of the ASP containing the object that is locked. 0 indicates the system ASP.
ASPGRP ASPGRP VARCHAR(10) The name of the ASP device containing the object that is locked. Can contain the special value of *SYSBAS.
MEMBER_LOCK_TYPE LOCK_TYPE VARCHAR(10)
Nullable
 The type of lock that is held.
ACCESSPATH
Lock on the access path used to access the member's data.
DATA
Lock on the actual data within the member.
MEMBER
Lock on the member control block.

Contains the null value if the lock information is not for a member.
LOCK_STATE LOCK_STATE VARCHAR(7) The lock condition for the object or member.
*EXCL
Lock exclusive no read.
*EXCLRD
Lock exclusive allow read.
*SHRNUP
Lock shared no update.
*SHRRD
Lock shared for read.
*SHRUPD
Lock shared for update.
LOCK_STATUS STATUS VARCHAR(9) The status of the lock.
HELD
The lock is currently held by the job.
REQUESTED
The job has a lock request outstanding for the object.
WAITING
The job is waiting for the lock.
LOCK_SCOPE LOCK_SCOPE VARCHAR(10) The scope of the lock. Values are:
  • JOB
  • LOCK SPACE
  • THREAD
JOB_NAME JOB_NAME VARCHAR(28)
Nullable
 The qualified job name.

Contains the null value when the LOCK_SCOPE column value is LOCK SPACE.
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 held, contains the lock space ID value of the lock space that holds the lock. If 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.
LOCK_COUNT LOCK_COUNT INTEGER The number of identical locks held.
PROGRAM_LIBRARY_NAME PROGLIB VARCHAR(10)
Nullable
 The name of the library containing the program or service program.

Contains the null value if the lock holder information is not available.
PROGRAM_NAME PROGNAME VARCHAR(10)
Nullable
 The name of the program holding the lock. This can be any type of program object, including objects of type *PGM and *SRVPGM.

Contains the null value if the lock holder information is not available.
MODULE_NAME_LIBRARY MODLIB VARCHAR(10)
Nullable
 The library containing the module.

Contains the null value if the lock holder information is not available or if the program is not an ILE program.

MODULE_NAME MODNAME VARCHAR(10)
Nullable
 The module containing the ILE procedure.

Contains the null value if the lock holder information is not available or if the program is not an ILE program.

PROCEDURE_NAME PROCNAME VARCHAR(4096)
Nullable
 The name of the procedure.

Contains the null value if the lock holder information is not available.
STATEMENT_ID STMTID CHAR(10)
Nullable
 The high-level language statement identifier. For a character representation of a number, the number is right-adjusted and padded on the left with zeros (for example, '0000000246').

Contains the null value if the lock holder information is not available.
MACHINE_INSTRUCTION INSTRUCT INTEGER
Nullable
 The current machine instruction number in the program.

Contains the null value if the lock holder information is not available or if it is an ILE procedure.

Example

Find all the jobs holding object locks over the SALES table:

SELECT * FROM QSYS2.OBJECT_LOCK_INFO
   WHERE SYSTEM_OBJECT_SCHEMA = 'TOYSTORE' AND
         SYSTEM_OBJECT_NAME = 'SALES'