Wednesday, January 6, 2016

Finding record locks using SQL

sql record lock info

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:

Column Description
SYS_DNAME Library name
SYS_TNAME File/Table name
SYS_MNAME Member name
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.

5 comments:

  1. Thank you for the informative article.
    Another great feature courtesy of IBM.

    By the way, I find it would be more readable if
    SELECT SYS_DNAME,SYS_TNAME,SYS_MNAME,RRN were written as
    SELECT SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, SYSTEM_TABLE_MEMBER, RELATIVE_RECORD_NUMBER

    Depending on who is looking at the data and whether or not they prefer system names, you could use
    SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_PARTITION, RELATIVE_RECORD_NUMBER

    ReplyDelete
  2. This is a great article, and that new locking view has its purposes and uses. For row-at-a-time processing I find testing SQLCOD for -0913 after an UPDATE or DELETE is faster and cleaner. Of course during set processing the view you write of works best.

    ReplyDelete
  3. I try it on our V7.2 system and I got the following error:
    SELECT SYS_DNAME,SYS_TNAME, SYS_MNAME, RRN, LOCK_STATE, LOCK_STATUS.LOCK_SCOPE, JOB_NAME
    FROM QSYS2.RECORD_LOCK_INFO
    WHERE SYS_DNAME = 'sysadm'
    AND SYS_TNAME = 'USERPRF'

    SQL State: 42704
    Vendor Code: -204
    Message: [SQL0204] RECORD_LOCK_INFO in QSYS2 type *FILE not found. Cause . . . . . : RECORD_LOCK_INFO in QSYS2 type *FILE was not found. If the member name is *ALL, the table is not partitioned. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, trigger or sequence object was not found. If a function was not found, RECORD_LOCK_INFO is the service program that contains the function. The function will not be found unless the external name and usage name match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.

    ReplyDelete
    Replies
    1. It would appear from that message that you do not have the PTFs for TR3 loaded.

      Check with your system administrator whether he/she has downloaded and applied the TR3 PTFs.

      Delete
  4. When updating a large number of records in a table, we write into an audit file the records that are lock. Would there be a way to do a single SQL statement to update the target table and write to some other file (an audit file) if the record for update is locked to some other user job or session?

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.