New Job columns added to the Record Lock View

In my last post I explained how the parts of the IBM i job name had been added as separate columns to the OBJECT_LOCK_INFO SQL View. I am pleased to say that the same three columns have been added to the RECORD_LOCK_INFO View too:

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

Before these columns were added, 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. With the addition of these three columns, I will show much easier this becomes.

If I wanted to check for record locks, not using RECORD_LOCK_INFO, I would use the Display Record Locks command, DSPRCDLCK. For example, if I wanted to check for record locks on TESTFILE, in my library, I would use the following:

01 DSPRCDLCK FILE(TESTFILE)

Which will show me the following:

Display Member Record Locks System: XXXXXXXX File . . . . . . . . : TESTFILE Member . . . . . . . : TESTFILE Library . . . . . : MYLIB Record Lock Number Job User Number Status Type 1 QPADEV0002 SIMON 315867 HELD UPDATE

I can get the same results using RECORD_LOCK_INFO in a SQL statement:

01 SELECT SYSTEM_TABLE_SCHEMA,SYSTEM_TABLE_NAME,SYSTEM_TABLE_MEMBER, 02 RELATIVE_RECORD_NUMBER, 03 LOCK_STATE,LOCK_STATUS, 04 JOB_NAME 05 FROM QSYS2.RECORD_LOCK_INFO 06 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 07 AND SYSTEM_TABLE_NAME = 'TESTFILE'

Lines 1 – 4: These are the columns I want in my results. I think the columns' names describe their contents; therefore, I am not going to waste the space in this post to list them.

Lines 6 and 7: As I did with DSPRCDLCK I only want to list the record locks for TESTFILE in my library.

I have only one result:

SYSTEM_ SYSTEM_ SYSTEM_ RELATIVE TABLE_ TABLE_ TABLE_ _RECORD_ LOCK_ LOCK_ SCHEMA NAME MEMBER NUMBER STATE STATUS JOB_NAME ------- --------- --------- -------- ------- ------ ----------------------- MYLIB TESTFILE TESTFILE 1 UPDATE HELD 315867/SIMON/QPADEV0002

The advantage of results, over those of the command, is that I can consume them in a program.

Another advantage is that I can look for any record lock in a library:

01 SELECT SYSTEM_TABLE_SCHEMA,SYSTEM_TABLE_NAME,SYSTEM_TABLE_MEMBER, 02 RELATIVE_RECORD_NUMBER, 03 LOCK_STATE,LOCK_STATUS, 04 JOB_NAME 05 FROM QSYS2.RECORD_LOCK_INFO 06 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB'

The statement is identical to the previous one, except that this statement's Where clause is for just the library. In the one before I had selected the library and the file. You cannot do that using the command.

I am not going to show the result as it is the same as the previous result.

The one column I would want to add to the results is one for the Job user's name. On the systems I currently use it is obvious that my profile is me. In the past I have had ones that were cryptic, making it unclear to others which profile is mine.

To add the user profile description I would use the new column JOB_USER, and use it to retrieve my row from the View USER_INFO_BASIC:

01 SELECT A.SYSTEM_TABLE_SCHEMA,A.SYSTEM_TABLE_NAME,A.SYSTEM_TABLE_MEMBER, 02 A.RELATIVE_RECORD_NUMBER,A. LOCK_STATE,A.LOCK_STATUS, 03 A.JOB_NAME,B.TEXT_DESCRIPTION 04 FROM QSYS2.RECORD_LOCK_INFO A, QSYS2.USER_INFO_BASIC B 05 WHERE A.JOB_USER = B.AUTHORIZATION_NAME 06 AND SYSTEM_TABLE_SCHEMA IN ('MYLIB','MYLIB2')

Line 3: I have added the column TEXT_DESCRIPTION from USER_INFO_BASIC.

Line 4 and 5: I think this is the easiest way to join two Views, Tables, Files, etc. On the first line, line 4, I list the two Views, separated by a column. And on the second line, line 5, I list the columns that join the data from the first to the second. Here I am using the new column JOB_USER to match to the authorization name in USER_INFO_BASIC.

Line 6: I am just limiting my results to two libraries, as I do not care about any record locks there may be in files and tables in the system libraries.

The result is:

SYSTEM_ SYSTEM_ SYSTEM_ RELATIVE TABLE_ TABLE_ TABLE_ _RECORD_ LOCK_ LOCK_ SCHEMA NAME MEMBER NUMBER STATE STATUS JOB_NAME ------- --------- --------- -------- ------- ------ ------------------------ MYLIB TESTFILE TESTFILE 1 UPDATE HELD 315867/SIMON/QPADEV0002 TEXT_DESCRIPTION ---------------------------- Simon Hutchinson, RPGPGM.COM

I thought I would show how I can take the results from this statement and consume it into a RPG program. My scenario is that I would display any record locks in the libraries MYLIB and MYLIB2, and then refresh the screen every so many seconds to capture any new record locks.

To achieve that I have a display file, TESTDSPF, that is just a subfile:

01 A DSPSIZ(27 132 *DS4) 02 A INDARA *-------------------------------------------------------- 03 A R SFL01 SFL 04 A ZRRN 4S 0H 05 A ZLIBRARY 10A O 3 2 06 A ZFILE 10A O 3 13 07 A ZMBRRRN 10A O 3 24ALIAS(ZMEMBER_RRN) 08 A ZJOBNAME 28A O 3 39ALIAS(ZJOB_NAME) 09 A ZUSERTEXT 50A O 4 7ALIAS(ZUSER_TEXT) *-------------------------------------------------------- 10 A R CTL01 SFLCTL(SFL01) 11 A SFLSIZ(0999) 12 A SFLPAG(0010) 13 A LOCK 14 A OVERLAY 15 A 31 SFLDSP 16 A 30 SFLDSPCTL 17 A N30 SFLDLT 18 A 30 SFLEND(*MORE) 19 A 1 2USER 20 A 1 50TIME 21 A 1 59DATE 22 A EDTCDE(Y) 23 A 2 2'Library File Member RRN - 24 A Job number ' 25 A DSPATR(UL) 26 A DSPATR(HI)

In what I call the "headers":

Line 1: I am setting my display to be the larger size, rather than the default.

Line 2: I am using an Indicator area, so that I can give the indicators in this display file meaningful names in the RPG program.

The subfile definition looks pretty standard, except for:

Lines 7 – 9: I am using the Alias keyword to define longer, and more meaningful, names for these fields.

The subfile control definition again is, again, pretty much the same as any other I would create, except for:

Line 13: I need to Lock keyword to prevent the user from pressing any keys while this program is running.

Lines 15 – 18: I am only using two indicators in this program, which are used to condition the subfile.

I am going to show the RPG program in parts as it will make it easier for me to explain what is going on. Let me start with the global definitions:

01 **free 02 ctl-opt option(*srcstmt) main(Main) dftactgrp(*no) actgrp(*caller) ; 03 dcl-f TESTDSPF workstn indds(Dspf) sfile(SFL01 : ZRRN) alias ; 04 dcl-ds Dspf qualified ; 05 SflDsp char(2) pos(30) ; 06 end-ds ; 07 dcl-ds Data qualified dim(*auto : 999) ; 08 Library char(10) ; 09 File char(10) ; 10 MemberRRN packed(10 : 0) ; 11 JobName char(28) ; 12 UserText char(50) ; 13 end-ds ;

Line 2: I am going to be using a Main procedure, therefore, I need the MAIN control option. As I am going to be using procedures in this program, I use the default activation group.

Line 3: This is the definition for the display file. The INDDS, indicator data structure, is what the data from the display file's Indicator area is moved to. The ALIAS is what allows me to use the long field names I defined in the display file.

Lines 4 – 6: The Indicator data structure only contains one subfield, SflDsp. As Indicators 30 and 31 are next to one another I have defined a two long character subfield to overlay both indicators.

Lines 7 – 8: This is the definition for the data structure array that will contain the results from RECORD_LOCK_INFO. It is an auto-extending array of up to 999 elements. The subfields will contain the results when I Fetch data from RECORD_LOCK_INFO.

Next section I will show in the Main procedure:

14 dcl-proc Main ; 15 dcl-pr sleep int(10) extproc('sleep') ; 16 *n uns(10) value ; 17 end-pr ; 18 dow (*on) ; 19 LoadSubfile() ; 20 write CTL01 ; 21 sleep(5) ; 22 enddo ; 23 return ; 24 on-exit ; 25 close *ALL ; 26 end-proc ;

Lines 15 – 17: The definition for the sleep C procedure, that delays the program for a given number of seconds, is within the Main procedure as this is the only place it is used.

Lines 18 – 22: This is a "never ending loop" that calls the procedure to load the subfile, this will be explained below, write the subfile Control record, and then sleeps for five seconds. The subfile control record is written, as opposed to executed with EXFMT, as I do not want input returned from the display file to the program. The program just writes the subfile control and continues, without stopping.

Lines 24 and 25: I am using an ON-EXIT group to ensure that any open files are closed by the CLOSE *ALL, no matter how the Main procedure ends.

27 dcl-proc LoadSubfile ; 28 dcl-ds Single likeds(Data) ; 29 Dspf.SflDsp = '00' ; 30 write CTL01 ; 31 Dspf.SflDsp = '11' ; 32 GetData() ; 33 if (%elem(Data) = 0) ; 34 clear SFL01 ; 35 ZRRN = 1 ; 36 ZLIBRARY = 'No record' ; 37 ZFILE = 'locks' ; 38 write SFL01 ; 39 else ; 40 ZRRN = 0 ; 41 for-each Single in Data ; 42 ZRRN += 1 ; 43 ZLIBRARY = Single.Library ; 44 ZFILE = Single.File ; 45 ZMEMBER_RRN = %triml(%editc(Single.MemberRRN : 'J')) ; 46 ZJOB_NAME = Single.JobName ; 47 ZUSER_TEXT = Single.UserText ; 48 write SFL01 ; 49 endfor ; 50 endif ; 51 end-proc ;

Line 28: As I am using an auto-extending array I will need a non-array to receive the data from it into. Here I am defining a new data structure with the LIKEDS keyword.

Lines 29 – 31: Now you understand why I defined the indicators in my Indicator data structure the way I did. Rather than have to set the two indicators off and then set them on, I can do that in less lines of code like this.

Line 32: Call the procedure to get the data from RECORD_LOCK_INFO, it will be described soon.

Lines 33 – 38: If no results were retrieved from RECORD_LOCK_INFO then the data structure array will have no elements. On line 33 if the array has zero elements then I want to display a default in the subfile which will say "No record locks".

Lines 40 – 49: If results were retrieved this section of code is executed.

Line 40: The subfile relative record number to zero. It will be incremented within the following For group.

Lines 41 – 49: The FOR-EACH will read every element from, in this case, a data structure array into the data structure was defined on line 28. The subfile relative number is incremented, the subfields from the array are moved into the subfile's fields, and the subfile record is written. When all of the elements of the data structure array have been "read" we exit the FOR-EACH.

In the final procedure I am showing is the one where the results from RECORD_LOCK_INFO are fetched:

52 dcl-proc GetData ; 53 dcl-s Elements uns(5) inz(%elem(Data : *max)) const ; 54 %elem(Data) = 0 ; 55 exec sql DECLARE C0 CURSOR FOR 56 SELECT A.SYSTEM_TABLE_SCHEMA,A.SYSTEM_TABLE_NAME, 57 A.RELATIVE_RECORD_NUMBER,A.JOB_NAME, 58 B.TEXT_DESCRIPTION 59 FROM QSYS2.RECORD_LOCK_INFO A, QSYS2.USER_INFO_BASIC B 60 WHERE A.JOB_USER = B.AUTHORIZATION_NAME 61 AND A.SYSTEM_TABLE_SCHEMA IN ('MYLIB','MYLIB2') 62 FOR READ ONLY ; 63 exec sql OPEN C0 ; 64 exec sql FETCH C0 FOR :Elements ROWS INTO :Data ; 65 on-exit ; 66 exec sql CLOSE C0 ; 67 end-proc ;

Line 53: This variable is defined to contain the maximum number of elements the data structure array, Data can contain. The CONST keyword is used to define that this variable is a constant, and cannot be changed.

Line 54: By setting the number of elements in the data structure array to zero deletes all the elements from it, and the data they contained.

Lines 55 - 62: Define the cursor I will be using to fetch data from RECORD_LOCK_INFO. I am retrieving record locks from the two libraries listed on line 61. And the cursor is used for input only; the cursor will not be updated.

Line 63: The cursor is opened.

Line 64: A multi-row fetch of the results is performed using the variable I defined on line 53 for the number of rows to get.

Line 66: The closing of the cursor is within an ON-EXIT group to ensure that the cursor is closed no matter how this procedure ends.

After compiling the display file and program I call the program. As there are no record lock in libraries MYLIB and MYLIB2 no results are fetched. And the following is shown in the display file:

SIMON TT:TT:TT DD/DD/DD Library File Member RRN Job number No record locks

I now cause a record lock, and in a few seconds the screen changed:

SIMON TT:TT:TT DD/DD/DD Library File Member RRN Job number MYLIB TESTFILE 1 370316/SIMON/QPADEV0002 Simon Hutchinson of RPGPGM.COM

If I resolve the lock, in a few seconds the fist screen, showing there are no locks, is displayed again.

I know I should just one example of using the new columns, but I am sure you can come up with many ideas on how to use them.

 

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

 

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