One of the new SQL Views introduced as part of IBM i 7.2 TR3 and 7.1 TR11 was one that allows us to check for record locks. Now I can execute a SQL query and return all of the jobs locking the file I inquired about. No longer do I need to use APIs or the CL command Display Member Record Locks, DSPRCDLCK.
The new View, RECORD_LOCK_INFO, is found in the library QSYS2. It returns one row of every lock in your IBM i. This is why I should be careful how I use this View. I am sure there are many thousands of record locks in any medium to large size applications at any one time, most of them are harmless as the record or row is locked, updated and released. Every once in a while something goes "bump" and a tries to lock a record/row that is already locked by another. An error message occurs and I have to work out what the lock is and how to release it, in a timely manner, before I get more record locks to the same record/row.
I am not going to list of the available columns, instead I am going to refer you to IBM's Knowledge Center for the full list here. The ones I care about are:
|RRN||Relative record number of the record/row|
|LOCK_STATE||Lock condition for the record/row
READ – Lock for read, another job may read the record but cannot lock the record for update.
UPDATE – Record is locked for update. Another job may read the record, but cannot lock it for update until the original lock is released.
INTERNAL – Locked internally for read. For a short time the operating system holds an internal lock to access the record. Another job can read the record and may even have the record locked for update. If another job does hold the row locked for update the change of the record will not proceed until the internal lock is released.
|LOCK_STATUS||Status of the lock
HELD – Lock is held by this job WAITING – Job is waiting for the lock
|LOCK_SCOPE||Scope of the lock
JOB, THREAD, LOCK SPACE
|JOB_NAME||Full job name|
I can use these columns to build a SQL statement I can use to find what is locking my 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 = 'MYLIB' AND SYS_TNAME = 'TESTFILE'
I have used the WHERE clause to give the library and file names to reduce the number of returned results, and the system resources need to generate them.
If my file, TESTFILE, was locked by this program:
01 dcl-f TESTFILE usage(*update) ; 02 chain 3 TESTFILER ; 03 update TESTFILER ; 04 *inlr = *on ;
If I caught the program after line 2 had executed and before line 3, then my SQL statement would retrieve the following:
SYSTEM_TABLE_SCHEMA SYS_TNAME SYS_MNAME RRN LOCK_STATE LOCK_STATUS MYLIB TESTFILE TESTFILE 3 UPDATE HELD LOCK_SCOPE JOB_NAME JOB 321545/SIMON/QPADEV0001
The equivalent using the Display Member Record Locks command, DSPRCDLCK, gives me:
Display Member Record Locks System: MYIBM_I File . . . . . . . . : TESTFILE Member . . . . . . . : TESTFILE Library . . . . . : MYLIB Record Lock Number Job User Number Status Type 3 QPADEV0003 RPGPGM 321545 HELD UPDATE
As this is output from a display command I cannot retrieve it into a program. The output from my SQL select statement can easily be incorporated into to a program to send the operator or locking user a message that there is a record lock error.
I could cause a record locking in a RPG program with embedded SQL:
01 dcl-s Fld1 packed(3) ; 02 exec sql DECLARE C0 CURSOR FOR SELECT FLD1 FROM TESTFILE FOR UPDATE OF FLD1 ; 03 exec sql OPEN C0 ; 04 exec sql FETCH NEXT FROM C0 INTO :Fld1 ; 05 exec sql UPDATE TESTFILE SET FLD1 = 10 WHERE CURRENT OF C0 ; 06 exec sql CLOSE C0 ; 07 *inlr = *on ;
If I managed to catch this program between the FETCH, line 4, and the UPDATE, line 5, I would see:
SYSTEM_TABLE_SCHEMA SYS_TNAME SYS_MNAME RRN LOCK_STATE LOCK_STATUS MYLIB TESTFILE TESTFILE 1 UPDATE HELD LOCK_SCOPE JOB_NAME JOB 321545/SIMON/QPADEV0001
I am sure you can come with several programs that cause record locks like my two examples. When the record lock occurs you can use a SQL statement similar to mine to see what job is causing the record lock, and then decide what corrective action to take.
I do not have IBM i 7.2 TR3 loaded onto any of the IBM i servers I use at work. When it is I can see myself writing a tool to quickly retrieve the details of the jobs with record locks on a file to determine which job is locking the record that is causing a record lock error message.
You can learn more about the RECORD_LOCK_INFO View from the IBM website here.
This article was written for IBM i 7.2 TR3 and 7.1 TR11, and will not work with earlier releases or TRs.