Wednesday, January 29, 2020

Using SQL to look for object locks

view all jobs locking an object using sql

This is almost embarrassing. I was asked if there is an easy way to programmatically check if an object is being used by another job. I knew the name of the SQL View, but when I searched this site I could find I mentioned it when IBM i 7.2 TR3 was announced, but I did not write anything more about this View. Today I am making amends of that oversight.

The question was is it possible to know which users are using an object. I could just use the Work Object Lock command, WRKOBJLCK, at the command line:

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

But I needed to be able to retrieve within a program the same information.

Fortunately included as part of IBM i 7.2 TR3 was the View OBJECT_LOCK_INFO. There are more columns in the View than I am going to mention here, therefore, I encourage you to check the link to IBM's documentation at the bottom of this post.

Let me start with the following SQL Select statement, it contains more information than was asked for, but I think it is interesting to know that it is there in the results.

01  SELECT MEMBER_LOCK_TYPE,LOCK_STATE,LOCK_SCOPE,JOB_NAME,
02         SUBSTR(JOB_NAME,8,LOCATE_IN_STRING(JOB_NAME,'/',8)-8) AS "User"
03    FROM QSYS2.OBJECT_LOCK_INFO
04   WHERE SYSTEM_OBJECT_SCHEMA = 'MYLIB' 
05     AND SYSTEM_OBJECT_NAME = 'TESTFILE'
06     AND OBJECT_TYPE = '*FILE'

In this example I want to see who is using the file TESTFILE in the library MYLIB.

I have selected the following columns:

Column name Description
MEMBER_LOCK_TYPE The type of record lock.
Null, indicated by -: there is not a lock on a member.
MEMBER: there is a lock on a member.
DATA: there is lock on data within the member.
LOCK_STATE The type of lock on the member or object.
*SHRRD: lock for read.
*SHRUPD: lock for update
*SHRNUP: no lock for update
*EXCLRD: exclusive lock allows read
*EXCL: exclusive lock does not allow read
LOCK_SCOPE JOB
THREAD LOCK SPACE
JOB_NAME The full job name

I have also created a column, User, for the job's user profile by substring it from JOB_NAME and using the LOCATE_IN_STRING scalar function. The function returns a value for the location of the second slash, which allows me to set the length of the string to substring.

The results look something like:

MEMBER_LOCK_TYPE  LOCK_STATE  LOCK_SCOPE
----------------  ----------  ----------
-                 *SHRRD      JOB
-                 *SHRRD      JOB
MEMBER            *SHRRD      JOB
DATA              *SHRRD      JOB
DATA              *SHRUPD     JOB


JOB_NAME                      "User"
----------------------------  ----------
553060/A*********/QPADEV0013  A*********
552658/B*********/QPADEV0002  B*********
494617/C*********/QPADEV002C  C*********
494617/C*********/QPADEV002C  C*********
494617/C*********/QPADEV002C  C*********

This is all "fine and dandy" but let me give a more real world scenario: I need to create a program that will end all jobs that have a lock on TESTFILE in MYLIB. This simple RPG program does this:

01  **free
02  ctl-opt main(Main)
              option(*nodebugio:*srcstmt:*nounref)
              dftactgrp(*no) ;

03  dcl-ds Jobs qualified dim(9999) ;
04    JobName char(28) ;
05  end-ds ;

06  dcl-s JobRows packed(5) inz(%elem(Jobs)) ;
07  dcl-s Counter like(JobRows) ;
08  dcl-s CmdString char(100) ;

09  dcl-proc Main ;
10    exec sql SET OPTION CLOSQLCSR=*ENDMOD ;

11    exec sql DECLARE C0 CURSOR FOR
12               SELECT DISTINCT JOB_NAME
13                 FROM QSYS2.OBJECT_LOCK_INFO
14                WHERE SYSTEM_OBJECT_SCHEMA = 'MYLIB'
15                  AND SYSTEM_OBJECT_NAME = 'TESTFILE'
16                  AND OBJECT_TYPE = '*FILE'
17                  FOR READ ONLY ;

18    exec sql OPEN C0 ;

19    exec sql FETCH C0 FOR :JobRows ROWS INTO :Jobs ;

20    exec sql GET DIAGNOSTICS :JobRows = ROW_COUNT ;

21    exec sql CLOSE C0 ;

22    if (JobRows = 0) ;
23      return ;
24    endif ;

25    for Counter = 1 to JobRows ;
26      CmdString = 'ENDJOB JOB(' + Jobs(Counter).JobName +
                    ') OPTION(*IMMED) LOGLMT(0)' ;
27      exec sql CALL QSYS2.QCMDEXC(:CmdString) ;
28    endfor ;
29  end-proc ;

Line 1: I have for the past few years written everything in totally free RPG, as in my opinion it is easier to write than the other forms of the language.

Line 2: My favorite control options include the directive that this program uses a Main procedure, which means it does not use the RPG cycle. As there are procedures I need the DFTACTGRP(*NO) directive.

Lines 3 – 5: I need to define a data structure array to contain the results of my SQL cursor. Even though there is only one data structure subfield for the job name.

Line 6 – 8: These variables will be used in the program. Note that JobRows, line 6, is initialized with the number of array elements from the data structure array Jobs.

Line 9: Start of the Main procedure.

Line 10: The only SQL option I need in this program is the one to designate when the cursor should be closed.

Lines 11 – 17: The definition of the SQL cursor that will be used to get the results from the View.

Line 12: I need to use the SELECT DISTINCT so that only one row is returned in the results for each job name. Otherwise I can get multiple rows returned for each job, see the results from the earlier example.

Line 14 – 16: I need to give the file, library, and object type of the file I am interested in.

Line 17: It is not mandatory but I always like to add the FOR READ ONLY so that everyone knows that this cursor will not be updating the file.

Line 18: The cursor is opened.

Line 19: I fetch the results from the cursor, same number of rows as there are elements in the data structure array Jobs.

Line 20: I could be using GET DIAGNOSTICS for more than the number of rows retrieved in the results, but I am keeping this example simple.

Line 21: As I am finished with the cursor I close the cursor.

Lines 22 - 24: If I did not retrieve any results, in other words there are no jobs locking the file, I quit the program.

Line 25 – 28: This is the section of code where I end all the jobs that have this file locked.

Line 25: I am using a For group to loop through the data structure array, for the same number of times as rows were retrieved in the cursor's fetched results.

Line 26: I am building the command string to end the job.

Line 27: I am using SQL's QCMDEXC API to execute the command built on line 26.

Line 29: This is the end of the Main procedure, no RETURN or *INLR is needed.

It is as simple as I have shown, when this program has finished all jobs that were locking the file have been ended. I could then use the Allocate Object command, ALCOBJ to lock the file so this job will be the only one who can use it.

 

You can learn more about the OBJECT_LOCK_INFO SQL View from the IBM website here.

 

This article was written for IBM i 7.4, and should work for all releases greater than IBM i 7.2 TR3 and 7.1 TR 11.

13 comments:

  1. Stephan SchüttlerJanuary 29, 2020 at 7:33 AM

    Wow ! Amazing ! .. THANKS !

    ReplyDelete
  2. Thanks, is there a way, in SQL, to check for a IFS file locked?

    ReplyDelete
  3. Thanks for sharing

    ReplyDelete
  4. Reynaldo Dandreb MedillaApril 17, 2021 at 6:50 AM

    That's cool Simon

    ReplyDelete
  5. Hi Simon,
    Is there a way to know the specific program that is locking a file without having to look at the source of programs in the call stack?
    (OBJECT_LOCK_INFO doesn't return the actual program name).
    Thanks,

    ReplyDelete
    Replies
    1. Alas, the only way I know is to go to the program stack.

      Are you doing that using SQL, if not check it out here.

      Delete
  6. This will help me

    ReplyDelete
  7. Simon, thanks for sharing. Another very helpful tool. Great read and examples..

    ReplyDelete
  8. Thank you Simon! I will be implementing this useful tool forthwith :-) I created something similar years ago using cryptic apis. Love all the enhancements to db2!

    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.