IBM i: The SQL Way · #2

Find IBM i Object Locks and the Jobs Holding Them

Use QSYS2.OBJECT_LOCK_INFO to identify jobs holding or waiting for IBM i object locks, inspect the lock state, and continue the investigation safely.

When an IBM i object is locked, the first question is usually simple: which job is holding the lock, and which job is waiting for it?

Object-lock problems are familiar to most IBM i developers and administrators.

A deployment cannot replace an object.

A file cannot be cleared.

A program cannot be updated.

A job waits longer than expected.

Or an application reports that an object is allocated by another job.

The traditional IBM i starting point is:

WRKOBJLCK OBJ(MYLIB/MYFILE) OBJTYPE(*FILE)

That remains an excellent operational tool.

But SQL gives us another useful view—especially when we want to:

• select only the useful columns
• distinguish held locks from waiting requests
• identify the program or procedure holding the lock
• save or export the result
• add the check to a utility
• create repeatable troubleshooting queries

The primary SQL query

Replace:

MYLIB
MYFILE
*FILE

with the object you need to investigate.

Run this from ACS Run SQL Scripts:

SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    OBJECT_TYPE,
    SYSTEM_TABLE_MEMBER,
    JOB_NAME,
    LOCK_STATE,
    LOCK_STATUS,
    LOCK_SCOPE,
    LOCK_COUNT,
    PROGRAM_LIBRARY_NAME,
    PROGRAM_NAME,
    MODULE_NAME_LIBRARY,
    MODULE_NAME,
    PROCEDURE_NAME,
    STATEMENT_ID
FROM QSYS2.OBJECT_LOCK_INFO
WHERE OBJECT_SCHEMA = 'MYLIB'
  AND OBJECT_NAME = 'MYFILE'
  AND OBJECT_TYPE = '*FILE'
ORDER BY
    CASE LOCK_STATUS
        WHEN 'WAITING'   THEN 1
        WHEN 'REQUESTED' THEN 2
        WHEN 'HELD'      THEN 3
        ELSE 4
    END,
    JOB_NAME;

This displays the jobs that:

currently hold a lock
have requested a lock
are waiting for a lock

The ordering places waiting and requested locks before held locks so the contention is easier to see.

Important performance warning

Do not begin with:

SELECT *
FROM QSYS2.OBJECT_LOCK_INFO;

OBJECT_LOCK_INFO is a view over current system lock information.

Without restrictive predicates, IBM i may need to examine every object in every library to determine whether it is locked.

Instead, provide as much information as possible:

WHERE OBJECT_SCHEMA = 'MYLIB'
  AND OBJECT_NAME = 'MYFILE'
  AND OBJECT_TYPE = '*FILE'

The exact library, object name, and object type give IBM i the narrowest investigation target.

What the columns tell us

OBJECT_SCHEMA and OBJECT_NAME

These identify the locked object.

For traditional system objects, the schema generally corresponds to the library.

For SQL objects that use long names, the SQL name may differ from the ten-character system name.

The view also provides:

SYSTEM_OBJECT_SCHEMA
SYSTEM_OBJECT_NAME

when the system names are needed.

OBJECT_TYPE

The IBM i object type.

Examples include:

*FILE
*PGM
*SRVPGM
*DTAARA
*DTAQ
*OUTQ
*JOBQ

Including the object type in the predicate improves precision and avoids returning another object with the same name.

SYSTEM_TABLE_MEMBER

For a database file member lock, this identifies the affected member.

It contains null when the lock does not apply to a member.

JOB_NAME

The qualified job name:

job-number/job-user/job-name

This value can be used directly with:

WRKJOB JOB(job-number/job-user/job-name)

LOCK_STATE

The object lock condition.

Possible values include:

*EXCL
*EXCLRD
*SHRNUP
*SHRRD
*SHRUPD

These values describe how exclusively the job has allocated the object and what other access may still be allowed.

LOCK_STATUS

The status is especially important.

HELD

The job currently owns the lock.

REQUESTED

The job has requested a lock that is not yet available.

WAITING

The job is waiting for the lock.

A common pattern is:

Job A   *SHRUPD   HELD
Job B   *EXCL     WAITING

That tells us Job B wants an exclusive lock, but Job A currently holds a lock that prevents it.

LOCK_SCOPE

The lock may be associated with:

JOB
THREAD
LOCK SPACE

For multithreaded or database work, thread and lock-space information can be important.

LOCK_COUNT

This is the number of identical locks held.

A count greater than one does not necessarily mean multiple different objects or jobs are involved. It means the same lock has been acquired more than once in that context.

PROGRAM_NAME and PROGRAM_LIBRARY_NAME

When available, these columns show the program or service program associated with the lock.

This can be more useful than knowing only the job name.

For example:

Job:      123456/APPUSER/ORDERJOB
Program:  APPLIB/ORDUPD

Now the investigation has both the runtime job and the application program involved.

MODULE_NAME and PROCEDURE_NAME

For ILE programs, the view may also identify:

• module library
• module name
• procedure name
• statement identifier

This can help connect the lock to a specific RPG procedure or source statement.

These fields may be null when the information is not available.

Example result

OBJECT       JOB_NAME                    LOCK     STATUS    PROGRAM
CUSTOMER     123456/APPUSER/ORDENTRY     *SHRUPD  HELD      CUSTUPD
CUSTOMER     123457/DEPLOY/DEPLOYJOB     *EXCL    WAITING   DEPLOY

This tells us:

1. ORDENTRY currently holds a shared-update lock.
2. DEPLOYJOB is waiting for an exclusive lock.
3. Ending the waiting deployment job would not remove the original lock.
4. The next investigation should focus on ORDENTRY and why it still needs the object.

Show only held locks

To list only jobs currently holding the object:

SELECT
    JOB_NAME,
    LOCK_STATE,
    LOCK_SCOPE,
    LOCK_COUNT,
    PROGRAM_LIBRARY_NAME,
    PROGRAM_NAME,
    MODULE_NAME,
    PROCEDURE_NAME,
    STATEMENT_ID
FROM QSYS2.OBJECT_LOCK_INFO
WHERE OBJECT_SCHEMA = 'MYLIB'
  AND OBJECT_NAME = 'MYFILE'
  AND OBJECT_TYPE = '*FILE'
  AND LOCK_STATUS = 'HELD'
ORDER BY JOB_NAME;

This is useful when the immediate question is:

Which job currently owns the lock?

Show only waiting or requested locks

To find jobs blocked while trying to acquire the object:

SELECT
    JOB_NAME,
    LOCK_STATE,
    LOCK_STATUS,
    LOCK_SCOPE,
    PROGRAM_LIBRARY_NAME,
    PROGRAM_NAME
FROM QSYS2.OBJECT_LOCK_INFO
WHERE OBJECT_SCHEMA = 'MYLIB'
  AND OBJECT_NAME = 'MYFILE'
  AND OBJECT_TYPE = '*FILE'
  AND LOCK_STATUS IN ('WAITING', 'REQUESTED')
ORDER BY JOB_NAME;

This answers a different question:

Which jobs are currently being affected by the lock?

Do not confuse the waiting job with the job causing the contention.

The job with:

LOCK_STATUS = 'HELD'

is normally the one whose lock must be understood.

Query using system object names

When the object has an SQL long name or the system name is known from a command or message, use the system-name columns:

SELECT
    SYSTEM_OBJECT_SCHEMA,
    SYSTEM_OBJECT_NAME,
    OBJECT_TYPE,
    JOB_NAME,
    LOCK_STATE,
    LOCK_STATUS,
    LOCK_SCOPE,
    PROGRAM_LIBRARY_NAME,
    PROGRAM_NAME
FROM QSYS2.OBJECT_LOCK_INFO
WHERE SYSTEM_OBJECT_SCHEMA = 'MYLIB'
  AND SYSTEM_OBJECT_NAME = 'MYFILE'
  AND OBJECT_TYPE = '*FILE'
ORDER BY LOCK_STATUS, JOB_NAME;

This can be useful when troubleshooting information comes from a message that reports the ten-character IBM i object name.

Find locks within one library

Sometimes the specific object is not yet known, but the affected application library is known.

SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    OBJECT_TYPE,
    JOB_NAME,
    LOCK_STATE,
    LOCK_STATUS,
    PROGRAM_LIBRARY_NAME,
    PROGRAM_NAME
FROM QSYS2.OBJECT_LOCK_INFO
WHERE OBJECT_SCHEMA = 'MYLIB'
ORDER BY
    OBJECT_NAME,
    LOCK_STATUS,
    JOB_NAME;

This is broader and may take longer than querying one exact object.

Use it carefully, especially in a library containing many objects.

Working with an independent ASP

OBJECT_LOCK_INFO returns information for objects in:

*SYSBAS
the current thread's ASP group

If the object is in an independent ASP, the SQL session must be associated with the correct ASP group for the object to be visible.

This is similar to other IBM i situations where an object exists but is not visible because the job is not connected to the correct ASP group.

What to do after identifying the job

Once a job appears to be holding the lock, continue the investigation rather than ending it immediately.

Start with:

WRKJOB JOB(job-number/job-user/job-name)

Review:

• job status
• call stack
• job log
• open files
• commitment-control status
• SQL activity
• current program
• current procedure
• whether the job is active or abandoned
• whether it is part of a critical business process

Useful native options may include:

WRKOBJLCK OBJ(MYLIB/MYFILE) OBJTYPE(*FILE)
WRKJOB JOB(job-number/job-user/job-name)

From WRKJOB, review the job’s locks and call stack.

Object locks are not record locks

OBJECT_LOCK_INFO reports object and member locks.

It does not replace record-level lock investigation.

For database record locks, use:

QSYS2.RECORD_LOCK_INFO

For example:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_PARTITION,
    RELATIVE_RECORD_NUMBER,
    JOB_NAME,
    LOCK_STATE,
    LOCK_STATUS,
    LOCK_SCOPE,
    THREAD_ID
FROM QSYS2.RECORD_LOCK_INFO
WHERE TABLE_SCHEMA = 'MYLIB'
  AND TABLE_NAME = 'MYTABLE'
ORDER BY
    RELATIVE_RECORD_NUMBER,
    LOCK_STATUS,
    JOB_NAME;

RECORD_LOCK_INFO can show record locks with statuses such as:

HELD
WAITING

and lock states such as:

READ
UPDATE
INTERNAL

Use it when the application is waiting on a specific row rather than an allocation of the file object itself.

Count update locks by job

For a quick summary of which jobs hold the most update-intent record locks:

SELECT
    JOB_NAME,
    COUNT(*) AS UPDATE_LOCK_COUNT
FROM QSYS2.RECORD_LOCK_INFO
WHERE TABLE_SCHEMA = 'MYLIB'
  AND TABLE_NAME = 'MYTABLE'
  AND LOCK_STATE = 'UPDATE'
GROUP BY JOB_NAME
ORDER BY UPDATE_LOCK_COUNT DESC;

This can help identify a job holding many rows within one table.

A high count does not automatically mean the job is defective.

It may indicate:

• a large transaction
• a batch update
• delayed commits
• an application design that keeps transactions open
• expected processing that has not completed yet

Native option versus SQL

Use WRKOBJLCK when:

• you know the exact object
• you need an immediate interactive view
• you want to work directly with the associated job
• the green-screen interface is the fastest operational option

Use SQL when:

• you want to distinguish held and waiting locks clearly
• you want selected program and procedure information
• you want to save or export the result
• you want repeatable filters
• you need to query several known objects
• you want to include the check in a support utility

The two approaches complement each other.

A possible troubleshooting workflow

1. Capture the object name, library, and object type.
2. Run WRKOBJLCK for an immediate native view.
3. Query OBJECT_LOCK_INFO with exact predicates.
4. Separate HELD locks from WAITING or REQUESTED locks.
5. Identify the program, module, procedure, and statement when available.
6. Work with the job holding the lock.
7. Review commitment control and business impact.
8. Resolve the underlying application or transaction condition safely.
9. Confirm that the waiting process can continue.

Turning the query into a utility

This query could become part of a small IBM i support tool.

The tool could accept:

Library
Object
Object type

and return:

Jobs holding locks
Jobs waiting for locks
Lock states
Programs and procedures involved
Commands for working with each job

It could also save a snapshot before the lock disappears.

That can be useful because intermittent lock problems are often gone by the time a developer begins investigating.

Final takeaway

When an IBM i object is locked, do not stop at:

The object is allocated by another job.

Use the lock information to answer:

Which job holds the lock?
Which job is waiting?
What lock state is involved?
Which program or procedure acquired it?
Is the issue at the object level or record level?
What business process will be affected by resolving it?

Use:

WRKOBJLCK

for the immediate native view.

Use:

QSYS2.OBJECT_LOCK_INFO

when you need a result that can be filtered, examined, exported, or incorporated into a reusable troubleshooting process.

The query identifies the lock relationship.

The investigation determines why the lock exists and how it should be resolved safely.