Tuesday, June 2, 2020

New SQL table function to display a job's locks

retrieving job lock information using sql

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.

No comments:

Post a Comment

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.