Using SQL to look for object locks

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.