JOB_LOCK_INFO table function

The JOB_LOCK_INFO table function returns a list of objects that have been locked or have lower level locks acquired by the specified job. If the job is not active, no rows are returned.

This information is similar to what is returned by the Retrieve Job Locks (QWCRJBLK) API.

Authorization: None required to see information for jobs where the caller's user profile is the same as the job user identity of the job for which the information is being returned. Otherwise, the caller must have *JOBCTL special authority.

The schema is QSYS2.

job-name

A character or graphic string expression that identifies the qualified name of a job. The special value of '*' indicates the current job.

internal-objects

A character or graphic string expression that indicates whether rows are returned for internal objects.

NO: Only external objects are returned. This is the default.
YES: Rows for internal objects and internal space objects are returned in addition to external objects.

ignore-errors

A character or graphic string expression that identifies what to do when an error is encountered.

NO: An error is returned.
YES: A warning is returned.; No row is returned when an error is encountered. This is the default.

The result of the function is a table containing rows with the format shown in the following table. All the columns are nullable.

Table 1. JOB_LOCK_INFO table function
Column Name	Data Type	Description
LOCK_CATEGORY	VARCHAR(23)	The type of entity to which this row of lock information applies. EXTERNAL IBM i external object EXTERNAL SPACE LOCATION IBM i external object space location INTERNAL Internal system object This type of row is only returned if the internal-objects parameter value is YES. INTERNAL SPACE LOCATION Internal system object space location This type of row is only returned if the internal-objects parameter value is YES. LOCK SPACE Lock space object MEMBER Member object Contains the null value if LOCK_CATEGORY is not known.
OBJECT_LIBRARY	VARCHAR(10)	The name of the library containing the locked object. Contains the null value if LOCK_CATEGORY is INTERNAL or INTERNAL SPACE LOCATION or if the library name cannot be determined.
OBJECT_NAME	VARCHAR(30)	The name of the object that is locked. If LOCK_CATEGORY is MEMBER, the object name is the name of the file that owns the member. If the MEMBER_LOCK_TYPE is MEMBER or ACCESS PATH, the file that owns the member may be either a physical file or a logical file. If the MEMBER_LOCK_TYPE is DATA, the file that owns the member will be a physical file. If LOCK_CATEGORY is LOCK SPACE, the name is the lock space id for that lock space. Contains the null value if is LOCK_CATEGORY is INTERNAL or INTERNAL SPACE LOCATION and the user does not have *JOBCTL special authority or if the object name cannot be determined.
OBJECT_TYPE	VARCHAR(7)	The object type. If the lock is on a database member the object type will be *FILE.
OBJECT_ATTRIBUTE	VARCHAR(10)	The extended attribute value for this object's type. Contains the null value if there is no extended attribute associated with the object type.
LOCK_STATE	VARCHAR(7)	The lock condition for the lock request. Lower level locks are returned and can occur when a member of a file is locked but the file itself is not locked. EXCL Lock exclusive, no read allowed. EXCLRD Lock exclusive, read allowed. SHRNUP Lock shared, no update. SHRRD Lock shared for read. *SHRUPD Lock shared for update. Contains the null value if the object is not locked but there are locks on lower level objects.
LOCK_STATUS	VARCHAR(17)	The status of the lock request. ASYNCHRONOUS WAIT The job or thread has a lock request outstanding for this object (asynchronous). The lock may be a single request or part of a multiple lock request for which some other object specified in the request has been identified as unavailable. HELD The lock on this object currently is held by the job or thread. SYNCHRONOUS WAIT The job or thread is waiting to get the lock on this object (synchronous). Contains the null value if the object is not locked but there are locks on lower level objects.
LOCK_COUNT	INTEGER	The number of identical locks on this entity.
LOCK_SCOPE	VARCHAR(10)	The scope of the lock. Lower level locks are returned and can occur when a member of a file is locked but the file itself is not locked. JOB The lock is scoped to the job. LOCK SPACE The lock is scoped to a lock space. THREAD The lock is scoped to a thread. Contains the null value if the object is not locked but there are locks on lower level objects.
MEMBER_LOCKS	INTEGER	The number of member locks for a database file. Contains the null value if the object is not a database file.
MEMBER_NAME	VARCHAR(10)	The name of the member that has a lock held or waiting on it. Contains *N if the member name cannot be determined. Contains the null value if LOCK_CATEGORY is not MEMBER.
MEMBER_LOCK_TYPE	VARCHAR(11)	The type of member lock. ACCESS PATH The lock is an access path lock. DATA The lock is a data lock. MEMBER The lock is a member lock. Contains the null value if LOCK_CATEGORY is not MEMBER.
SPACE_LOCATION_LOCK_OFFSET	BIGINT	A value in bytes to the location in the space that is locked. Contains the null value if LOCK_CATEGORY is not EXTERNAL SPACE LOCATION or INTERNAL SPACE LOCATION.
LOCK_SPACE_ID	BINARY(20)	The identifier of the lock space for which the lock is being waited on. Contains the null value if LOCK_SCOPE is not LOCK SPACE.
THREAD_ID	BIGINT	A value which uniquely identifies a thread within a job holding a thread scope lock or the thread waiting for a lock. Contain the null value if this lock is not thread scoped.
THREAD_HANDLE	INTEGER	A value which addresses a particular thread within a job holding a thread scope lock or the thread waiting for a lock. Contain the null value if this lock is not thread scoped.
ASP_NAME	VARCHAR(10)	The name of the Auxiliary Storage Pool (ASP) that contains the object that is locked. Contains the null value if the name of the ASP device cannot be determined.
ASP_NUMBER	INTEGER	The numeric identifier of the ASP containing the locked object. Contains the null value if the name of the ASP device cannot be determined.
OBJECT_LOCK_HANDLE	BINARY(64)	An identifier that can be input to Retrieve Lock Information (QWCRLCKI) API to find additional information about other holders of locks on this object. Contains the null value if additional information cannot be retrieved.
LOCK_REQUEST_HANDLE	BINARY(64)	A handle to lock request information. Using the Retrieve Lock Request Information (QWCRLRQI) API and passing in this handle you can retrieve additional information about the program that requested this lock. Contains the null value if additional information cannot be retrieved.

Example

Retrieve information about locks for the current job.
```
SELECT * FROM TABLE(QSYS2.JOB_LOCK_INFO('*'));
```