New SQL table function to display a job's locks

Before the latest round of new Technology Refreshes, 7.4 TR2 and 7.3 TR8, the only ways I knew to get Job Lock information was either by using an API or the Display Job command, DSPJOB. These new TRs introduced a new table function, JOB_LOCK_INFO, that allows me to see the same information with just a simple SQL statement.

JOB_LOCK_INFO has three parameters:

1. Job name: This is the only required parameter. This can either be the full job name, or for information about the current job an asterisk ( * ) can be used
2. Internal objects only: Optional. NO, which is the default, shows only external objects. YES shows internal objects, internal space objects, and external objects
3. Ignore error: Optional. NO when an error is encountered it is returned. YES, which is the default, only a warning is returned

In this example I signed on to an IBM i partition, executed the DSPJOB command, take 12 to "Display locks", and the bottom section of the page displays these job locks:

Object Member Object Library Type Lock Status Locks MYMENU MYLIB *MENU *SHRNUP HELD *SHRNUP HELD QDUI132 QSYS *FILE-DSP *SHRNUP HELD QGPL QSYS *LIB *SHRRD HELD QGWCJLCK QSYS *PNLGRP *SHRNUP HELD QGWCJOB QSYS *PNLGRP *SHRNUP HELD QHLPSYS QSYS *LIB *SHRRD HELD QPADEV0001 QSYS *DEVD *EXCLRD HELD *EXCLRD HELD QSYS QSYS *LIB *SHRRD HELD QSYS2 QSYS *LIB *SHRRD HELD QTEMP QSYS *LIB *SHRRD HELD QUSRSYS QSYS *LIB *SHRRD HELD SIMON QSYS *USRPRF *SHRRD HELD *SHRRD HELD *SHRRD HELD SIMON QUSRSYS *MSGQ *EXCL HELD MYLIB QSYS *LIB *SHRRD HELD *SHRRD HELD MYLIB2 QSYS *LIB *SHRRD HELD MYLIB3 QSYS *LIB *SHRRD HELD MYLIB4 QSYS *LIB *SHRRD HELD *EXCLRD HELD

I can display the same information using this new table function. I am not using all of the columns that are returned by JOB_LOCK_INFO, for more information about these columns and those I have omitted click on the link to IBM's documentation at the end of this post.

01 SELECT LOCK_CATEGORY AS "Type", 02 OBJECT_NAME AS "Object", 03 OBJECT_LIBRARY AS "Library", 04 OBJECT_TYPE AS "Obj type", 05 LOCK_STATE AS "Lock", 06 LOCK_STATUS AS "Status", 07 MEMBER_LOCKS AS "Mbr l" 08 FROM TABLE(QSYS2.JOB_LOCK_INFO('194953/SIMON/QPADEV0001')) ;

Lines 1 – 7: I have given these columns custom column heading so that they resemble those shown on the "Display job locks" display, and to reduce the width of the results so that they will fit on this page.

Line 8: I am only passing the job name.

The results look very similar:

Type Object Library Obj type Lock Status Mbr l --------- ---------- ------- -------- -------- ------ ----- EXTERNAL MYMENU MYLIB *MENU *SHRNUP HELD - EXTERNAL QDUI132 QSYS *FILE *SHRNUP HELD 0 EXTERNAL QGPL QSYS *LIB *SHRRD HELD - EXTERNAL QGWCJOB QSYS *PNLGRP *SHRNUP HELD - EXTERNAL QHLPSYS QSYS *LIB *SHRRD HELD - EXTERNAL QPADEV0001 QSYS *DEVD *EXCLRD HELD - EXTERNAL QSYS QSYS *LIB *SHRRD HELD - EXTERNAL QSYS2 QSYS *LIB *SHRRD HELD - EXTERNAL QUSRSYS QSYS *LIB *SHRRD HELD - EXTERNAL SIMON QSYS *USRPRF *SHRRD HELD - EXTERNAL SIMON QUSRSYS *MSGQ *EXCL HELD - EXTERNAL MYLIB QSYS *LIB *SHRRD HELD - EXTERNAL MYLIB2 QSYS *LIB *SHRRD HELD - EXTERNAL MYLIB3 QSYS *LIB *SHRRD HELD - EXTERNAL MYLIB4 QSYS *LIB *SHRRD HELD -

I added the first column to display the type of lock. I am not going to describe the types of locks you will encounter. I will ask that you refer to IBM's documentation about this table function for that information.

The only time I really use the DSPJOB command is to find which files are being used by a job. In this example I have a program with three files in a program:

1. CITY defined for input only
2. PERSON also input only
3. TESTFILE defined for update

I have put the program into debug immediately after TESTFILE and PERSON are read. When I call the program in one 5250 session I can go to my "Run SQL scripts" and search for these three files:

SELECT LOCK_CATEGORY AS "Type", OBJECT_NAME AS "Object", OBJECT_LIBRARY AS "Library", OBJECT_TYPE AS "Obj type", LOCK_STATE AS "Lock", LOCK_STATUS AS "Status", MEMBER_LOCKS AS "Mbr l" FROM TABLE(QSYS2.JOB_LOCK_INFO('194953/SIMON/QPADEV0001')) WHERE OBJECT_LIBRARY = 'MYLIB'

I am only including the results from the three files below:

Type Object Library Obj type Lock Status Mbr l --------- --------- ------- -------- -------- ------ ----- EXTERNAL CITY MYLIB *FILE *SHRRD HELD 2 MEMBER CITY MYLIB *FILE *SHRRD HELD 0 MEMBER CITY MYLIB *FILE *SHRRD HELD 0 EXTERNAL PERSON MYLIB *FILE *SHRRD HELD 2 MEMBER PERSON MYLIB *FILE *SHRRD HELD 0 MEMBER PERSON MYLIB *FILE *SHRRD HELD 0 EXTERNAL TESTFILE MYLIB *FILE *SHRRD HELD 2 MEMBER TESTFILE MYLIB *FILE *SHRRD HELD 0 MEMBER TESTFILE MYLIB *FILE *SHRUPD HELD 0

But if I just want a list of data files from non-IBM libraries that is too much information. I would use a SQL statement that would look something like:

01 SELECT DISTINCT OBJECT_NAME,OBJECT_LIBRARY 02 FROM TABLE(QSYS2.JOB_LOCK_INFO('194953/SIMON/QPADEV0001')) 03 WHERE OBJECT_TYPE = '*FILE' 04 AND SUBSTR(OBJECT_LIBRARY,1,1) <> 'Q'

Line 1: By using SELECT DISTINCT I am only going to have returned to me once in the results each combination of File and Library names.

Line 3: I only need the results for files.

Line 4: This is my crude attempt to exclude all the files in IBM libraries, as on the whole IBM's libraries start with the letter "Q".

The results are what I want, just of list of the files and the libraries they are in:

OBJECT_NAME OBJECT_LIBRARY ----------- -------------- PERSON MYLIB TESTFILE MYLIB CITY MYLIB

 

You can learn more about the JOB_LOCK_INFO SQL table function from the IBM website here.

 

This article was written for IBM i 7.4 TR2 and 7.3 TR8.