How to find IBM i File Record Locks – with or without SQL

How to find IBM i File Record Locks with pure OS Commands

To find file record locks on IBM i using the DSPRCDLCK (Display Record Locks) command, follow these steps:

1. Open a command line on your IBM i system.
2. Enter the DSPRCDLCK command with the appropriate parameters. Here’s a basic example:

DSPRCDLCK FILE(MYLIB/MYFILE) MBR(*FIRST) RCDNBR(*ALL)

In this example:

• FILE(MYLIB/MYFILE) specifies the library and file name.
• MBR(*FIRST) specifies the first member of the file.
• RCDNBR(*ALL) specifies that the lock status of all records in the member should be displayed.

3. Review the output to see the current lock status of the records. The output will show whether the records are in a “wait” or “held” state and indicate the type of lock currently held.

How to find IBM i File Record Locks using SQL

In the IBM i environment, you can use SQL to check for record locks. One of the new SQL Views introduced as part of IBM i 7.2 TR3 and 7.1 TR11 is RECORD_LOCK_INFO, which allows you to identify jobs that are locking specific files. No longer do you need to rely on APIs or the CL command DSPRCDLCK.

To find file record locks on IBM i using SQL, you can use the QSYS2.RECORD_LOCK_INFO table function. This function provides information about the records that are currently locked.

Here’s an example query to get you started:

Here’s how you can use SQL to find out what is locking a record:

SELECT 
 SYS_DNAME,
 SYS_TNAME,
 SYS_MNAME,
 RRN,
 LOCK_STATE,
 LOCK_STATUS,
 LOCK_SCOPE,
 JOB_NAME
FROM QSYS2.RECORD_LOCK_INFO
WHERE
 SYS_DNAME = 'NICKLIB' AND SYS_TNAME = 'NICKFILE'

In this example:

• NICKLIB represents the library name.
• NICKFILE represents the file/table name.

The result will include information about the locking jobs, such as whether the record is locked for read or update, the lock status (held or waiting), and the scope of the lock (job, thread, or lock space).

For instance, if your program executed line 2 (before line 3) and locked record number 3 in TESTFILE, the SQL query would retrieve details like this:

• Library: MYLIB
• File/Table: TESTFILE
• Member: TESTFILE
• Record Number: 3
• Lock State: UPDATE
• Lock Status: HELD
• Lock Scope: JOB
• Job Name: 321545/SIMON/QPADEV0001

Remember that this SQL view provides valuable insights into record locks, but exercise caution when using it, as there could be thousands of locks in a medium to large application at any given time. Most locks are harmless, but occasionally, resolving conflicts promptly is essential to maintain data integrity.

To find file record locks on IBM i using SQL, you can use the QSYS2.RECORD_LOCK_INFO table function. This function provides information about the records that are currently locked. Here’s an example query to get you started:

SELECT 
    JOB_NAME, 
    FILE_NAME, 
    MEMBER_NAME, 
    RECORD_NUMBER, 
    LOCK_STATE 
FROM 
    TABLE(QSYS2.RECORD_LOCK_INFO()) 
ORDER BY 
    JOB_NAME, 
    FILE_NAME, 
    MEMBER_NAME, 
    RECORD_NUMBER;

This query will return details about the jobs holding the locks, the files and members involved, the specific record numbers, and the state of the locks.

If you need more specific information or have other questions, feel free to ask!