New columns added to the Object Lock View

While the addition of these three new columns to the OBJECT_LOCK_INFO SQL View may not appear to be significant, I know it will make it easier for me to process data for jobs.

The three new columns are all part of the long job name, and now have their own columns:

1. JOB_USER:  User profile of the job
2. JOB_NAME_SHORT:  Name of the job
3. JOB_NUMBER:  Number of the job

Prior to the addition of these columns if I wanted to list my jobs, with the user profile "SIMON", I would need to extract it from the full job name or use a wildcard in the Where clause.

Before I show any SQL statements I need to explain the scenario I created. I have two programs:

1. Program 1: This RPG program opens the file TESTFILE for update and then pauses for ten minutes
2. Program 2: The second program uses a display file, TESTDSPF, and uses the EXFMT operation code to show it on the screen

Both of these objects/files are locked by the programs that called them.

Before these new columns were added if I just wanted to list my jobs that had object locks I would use the following SQL statement:

01 SELECT SYSTEM_OBJECT_NAME AS "Object", 02 OBJECT_TYPE AS "Obj type", 03 MEMBER_LOCK_TYPE "Lock type", 04 LOCK_STATE AS "State", 05 LOCK_SCOPE AS "Scope", 06 JOB_NAME, 07 SUBSTR(JOB_NAME,8,LOCATE_IN_STRING(JOB_NAME,'/',8)-8) AS "User" 08 FROM QSYS2.OBJECT_LOCK_INFO 09 WHERE SUBSTR(JOB_NAME,8,LOCATE_IN_STRING(JOB_NAME,'/',8)-8) = 'SIMON' 10 AND SYSTEM_OBJECT_SCHEMA = 'MYLIB' 11 ORDER BY JOB_NAME

Lines 1 – 5: I wanted to give these columns shorter column headings than the default ones for these columns.

Line 7: This is the part of the statement that extracts the user profile from the Job Name.

Line 9: I repeat the extraction logic from line 7 in the Where clause.

Line 10: I have restricted the results just to my library for my convenience. If I omit this line all of the overrides in the partition are considered for the results. This can take, relatively, a long time. My giving the library here my results are returned quickly. Remember, all jobs have some object locks.

I could replace line 9 with the following:

09 WHERE JOB_NAME LIKE '%/SIMON/%'

I have included the slashes ( '/' ) to exclude any other user profiles that may include the string 'SIMON' within them. For example: ASIMON and SIMONA.

The above statement returns the following results:

Object Obj type Lock type State Scope JOB_NAME User -------- --------- --------- ------- ----- ------------------------ ------ TESTDSPF *FILE <NULL> *SHRNUP JOB 286574/SIMON/QPADEV0003 SIMON TESTFILE *FILE <NULL> *SHRRD JOB 286693/SIMON/QPADEV0007 SIMON TESTFILE *FILE MEMBER *SHRRD JOB 286693/SIMON/QPADEV0007 SIMON TESTFILE *FILE DATA *SHRUPD JOB 286693/SIMON/QPADEV0007 SIMON

The first result is from the job with the display file, and the other three are from the other job with the data file.

The addition of the JOB_USER makes it a lot easier to get the same results, without the long and complicated statements on lines 7 and 9. My statement becomes:

01 SELECT SYSTEM_OBJECT_NAME AS "Object", 02 OBJECT_TYPE AS "Obj type", 03 MEMBER_LOCK_TYPE "Lock type", 04 LOCK_STATE AS "State", 05 LOCK_SCOPE AS "Scope", 06 JOB_NAME, 07 JOB_USER, 08 JOB_NAME_SHORT, 09 JOB_NUMBER 10 FROM QSYS2.OBJECT_LOCK_INFO 11 WHERE JOB_USER = 'SIMON' 12 AND SYSTEM_OBJECT_SCHEMA = 'MYLIB' 13 ORDER BY JOB_NAME

Lines 6 – 9: I have added the new columns to my results.

Line 11: The Where clause becomes so much simpler by using JOB_USER.

The results are the same as before, just with the new columns:

JOB JOB_ NAME_ JOB_ Object Obj type Lock type State Scope JOB_NAME USER SHORT NUMBER -------- --------- --------- ------- ----- ------------------------ ------- ---------- ------ TESTDSPF *FILE <NULL> *SHRNUP JOB 286574/SIMON/QPADEV0003 SIMON QPADEV0003 286574 TESTFILE *FILE <NULL> *SHRRD JOB 286693/SIMON/QPADEV0007 SIMON QPADEV0007 286693 TESTFILE *FILE MEMBER *SHRRD JOB 286693/SIMON/QPADEV0007 SIMON QPADEV0007 286693 TESTFILE *FILE DATA *SHRUPD JOB 286693/SIMON/QPADEV0007 SIMON QPADEV0007 286693

The new JOB_NAME_SHORT columns makes it easier if I want to look for object locks in all of the jobs with a particular name:

01 SELECT SYSTEM_OBJECT_NAME AS "Object", 02 OBJECT_TYPE AS "Obj type", 03 MEMBER_LOCK_TYPE "Lock type", 04 LOCK_STATE AS "State", 05 LOCK_SCOPE AS "Scope", 06 JOB_NAME, 07 JOB_USER, 08 JOB_NAME_SHORT, 09 JOB_NUMBER 10 FROM QSYS2.OBJECT_LOCK_INFO 11 WHERE JOB_NAME_SHORT = 'QPADEV0003' 12 AND SYSTEM_OBJECT_SCHEMA = 'MYLIB' 13 ORDER BY JOB_NAME

Line 11: The Where clause not selects all jobs with the job name 'QPADEV0003'. I realize that there is only one job with that name, but you get the idea as any job name could be entered here.

Only one result is returned, which is expected.

JOB JOB_ NAME_ JOB_ Object Obj type State Scope JOB_NAME USER SHORT NUMBER -------- --------- ------- ----- ------------------------ ------- ---------- ------ TESTDSPF *FILE *SHRNUP JOB 286574/SIMON/QPADEV0003 SIMON QPADEV0003 286574

Now for a more practical example of using these new fields. In this example I want to end any of my jobs that have an object lock on the display file, TESTDSPF, in my library.

My SQL statement would be:

01 SELECT DISTINCT JOB_NAME, 02 QSYS2.QCMDEXC('ENDJOB JOB(' || JOB_NAME || ') OPTION(*IMMED) LOGLMT(0)') 03 AS "Return code" 04 FROM QSYS2.OBJECT_LOCK_INFO 05 WHERE JOB_USER = CURRENT_USER 06 AND SYSTEM_OBJECT_SCHEMA = 'MYLIB' 07 AND SYSTEM_OBJECT_NAME = 'TESTDSPF'

Line 1: I use SELECT DISTINCT as I only need to end the job once, no matter how many overrides it has.

Lines 2 and 3: I am using the QCMDEXC Scalar function to execute the ENDJOB command. I concatenate the job name into the JOB parameter. Rather than use CONCAT, I am using the double pipe ( || ) short cut. If you are transferring this code to another partition with a different CCSID, the double pipes may be translated to another pair of characters. If you need to do that, then use CONCAT. As all the partitions I use have the same CCSID (37) I use the double pipes.

Line 5: I only want to select the jobs of the current user. I do this by matching CURRENT_USER to the new JOB_USER column.

Lines 6 and 7: And I only want to select the rows for the display file.

I have an object lock on the display file the following result is returned.

JOB_NAME Return code ------------------------ ----------- 293714/SIMON/QPADEV0001 1

The "1" in the Return code column informs me that the QCMDEXC Scalar function completed successfully, in other words the job was ended.

If I have no jobs locking the display file I just get the column headings, with no results:

JOB_NAME Return code ------------------------ -----------

As I said before, I think these three new columns make it a lot easier to keep SQL statements, like the one I just gave, simpler. And as you all know I am always trying to keep things simple and easy.

 

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

 

This article was written for IBM i 7.6 TR1 and 7.5 TR7.