Start of change

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.

Read syntax diagramSkip visual syntax diagram JOB_LOCK_INFO ( JOB_NAME => job-name,INTERNAL_OBJECTS => internal-objects,IGNORE_ERRORS => ignore-errors)
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('*'));
End of change