Making a list of journal receivers with the detach date

Update

A SQL View was added to IBM i 7.5 that makes it easier to retireve information about Journal Receiver. Before using what is described below check my post about getting Journal Receiver information with SQL.

 

---

 

I wanted to write a program that would delete old journal receivers, ones detached from the journal and more than a certain number of days old (to be determined later). I can see the detach date when I use the Display Journal Receiver Attributes command, DSPJRNRCVA, but I can only do this for one receiver at a time, and the command only allows for display or print. Looking at my chart of SQL Views and Table functions I found that is not one for Journal Receivers. Alas, this leaves me having to use an API to get to this information.

The information I desire is:

• Journal name and library
• Journal receiver name and library
• Attach date (timestamp) of the receiver
• Detach date (timestamp) for the receiver

There is an API that will return to me the information. It comes with the long name: QjoRtvJrnReceiverInformation. Before I go into the example of using this API let me get started with how I made a list of all the receivers in a library. Here I can use a SQL Table function, OBJECT_STATISTICS. I use the following statement to display a list of all the receivers in a library.

SELECT OBJNAME,OBJLIB FROM TABLE(QSYS2.OBJECT_STATISTICS(<library name>,'*JRNRCV','*ALLSIMPLE'))

I am only interested in two columns from the results:

1. OBJNAME:  Object name
2. OBJLIB:  The name of the library the object is in

My example of the Object Statistics statement has three parameters:

1. Name of the library that contains the receivers I am interested in
2. Object type, in this case I only want the Journal Receivers, *JRNRCV
3. *ALLSIMPLE denotes that I only want the basic set of results. The table function returns all the columns, as the full version does, but only a few of the columns contain data, the rest contain null. By using the *ALLSIMPLE the results are returned to me quicker than using the full version.

If I modified this statement for the library JRNLIB it would look like:

SELECT OBJNAME,OBJLIB FROM TABLE(QSYS2.OBJECT_STATISTICS('JRNLIB','*JRNRCV','*ALLSIMPLE')) ORDER BY 1 LIMIT 10

I am sorting the results by the first column, receiver name, and only want ten results returned just to illustrate this example.

OBJNAME OBJLIB ---------- -------- PMEDHR0079 JRNLIB PMEDHR0080 JRNLIB PMEDHR0081 JRNLIB PMEDHR0082 JRNLIB PMEDHR0083 JRNLIB PMEDHR0084 JRNLIB PMEDHR0085 JRNLIB PMEDHR0086 JRNLIB PMEDHR0087 JRNLIB PMEDHR0088 JRNLIB

This shows that I can use this to drive the API.

I also have two output tables. The first contains the results from the API:

01 CREATE OR REPLACE TABLE MYLIB.JOURNAL_RECEIVER_INFO 02 FOR SYSTEM NAME "JRNRCVINF" 03 (JOURNAL_LIBRARY FOR COLUMN "JRNLIB" CHAR(10), 04 JOURNAL_NAME FOR COLUMN "JRNNAME" CHAR(10), 05 RECEIVER_LIBRARY FOR COLUMN "RCVLIB" CHAR(10), 06 RECEIVER_NAME FOR COLUMN "RCVNAME" CHAR(10), 07 ATTACH_TIMESTAMP FOR COLUMN "ATTACH" TIMESTAMP, 08 DETACH_TIMESTAMP FOR COLUMN "DETACH" TIMESTAMP) ;

I don't think I need to describe the columns as their names describe precisely what they contain. Notice that as I have used long SQL names, I have also given short system names too.

I will use the second table if I encounter any errors when using the QjoRtvJrnReceiverInformation API.

01 CREATE OR REPLACE TABLE MYLIB.JOURNAL_RECEIVER_ERROR 02 FOR SYSTEM NAME "JRNRCVERR" 03 (QUALIFIED_RECEIVER_NAME FOR COLUMN "RCVNAME" CHAR(20), 04 ERROR_MESSAGE FOR COLUMN "ERRMSG" CHAR(10)) ;

Line 3: This column contains the "qualified journal receiver name", in other words the receiver's name in the first ten characters, followed by the library name, starting at position 11. The second column is to contain the message code returned from QjoRtvJrnReceiverInformation.

The program I wrote is 72 lines long, therefore, I am going to break it into parts and describe what the program will do. Let's start with the definitions.

01 **free 02 ctl-opt option(*nodebugio:*srcstmt) dftactgrp(*no) ; 03 /include QSYSINC/QRPGLESRC,QUSEC //Error DS 04 /include QSYSINC/QRPGLESRC,QJOURNAL //All journal DS 05 dcl-ds RcvInfo likeds(QJOV0100) ; 06 dcl-pr GetRcvInfo extproc('QjoRtvJrnReceiverInformation') ; 07 *n likeds(RcvInfo) ; //Receiver DS 08 *n int(10) const ; //Length 09 *n char(20) const ; //Qualified name 10 *n char(8) const ; //Format 11 *n likeds(QUSEC) ; //Error DS 12 end-pr ; 13 dcl-ds Rcvs qualified dim(9999) ; 14 Rcv char(10) ; 15 Lib char(10) ; 16 end-ds ; 17 dcl-s Rows int(5) inz(%elem(Rcvs)) ; 18 dcl-s Counter int(5) ; 19 dcl-s QualRcv char(20) ; 20 dcl-s Timestamp1 timestamp ; 21 dcl-s Timestamp2 timestamp ; 22 dcl-s Null1 int(5) ; 23 dcl-s Null2 like(Null1) ; 24 dcl-s Lib char(10) inz('JRNLIB') ; 25 exec sql SET OPTION COMMIT = *NONE ;

Line 1: If you are not programming in totally free RPG you are making it hard for yourself.

Line 2: My favorite control options. I cannot run the program in the default activation group as it calls an external procedure.

Lines 3 and 4: Rather than create the necessary data structures for the error handling data structure, QUSEC, and the one for the format of data I am receiving from the API, I can include (copy) in their definitions from source members in the QRPGLESRC source file in the library.

Line 5: I am creating my own data structure like the one that is in the included definition by using the LIKEDS.

Lines 6 – 12: This is the procedure prototype for the API. I have defined it with a different name, GetRcvInfo, as QjoRtvJrnReceiverInformation is just too long. The name I gave it follows the DCL-PR and the actual name is in the EXTPROC.

There are five parameters for this API:

1. Data structure that contains the results
2. Length of that data structure, input only so it is defined as CONST
3. Qualified name of the Journal Receiver, also input and CONST
4. Name of the format for the results, input and CONST
5. Error data structure

I never bother to give these names in the procedure prototype, hence the *N to denote "no name" or null.

Lines 13 – 16: This is the data structure array I will be copying the results from the OBJECT_STATISTICS into. It only contains two subfields for the receiver's name and library.

Lines 17 – 24: Variable definitions.

Line 17: Rows is initialized with the number of array elements that there are in the data structure array Rcvs.

Lines 22 and 23: These are SQL null "indicators" that will be used for initializing some of the Table's columns with null.

Line 24: I have "soft coded" my statement for the OBJECT_STATISTICS so I can pass the library name to it.

Line 25: This is not really a definition, but I regard the SQL SET OPTION as its equivalent of a control option.

Onto defining of the cursor for the OBJECT_STATISTICS.

26 exec sql DELETE FROM JOURNAL_RECEIVER_INFO ; 27 exec sql DELETE FROM JOURNAL_RECEIVER_ERROR ; 28 exec sql DECLARE C0 CURSOR FOR SELECT OBJNAME,OBJLIB FROM TABLE(QSYS2.OBJECT_STATISTICS(:Lib,'*JRNRCV','*ALLSIMPLE')) FOR READ ONLY ; 29 exec sql OPEN C0 ;

Lines 26 and 27: Rather that use the CLRPFM command I am using the SQL Delete statement to clear the two output files.

line 28: The definition for the cursor C0. I only want the object name and library in the results from the library in the variable :Lib. I always add FOR READ ONLY so that other programmers know I will not be updating this cursor.

Line 29: The cursor is opened.

Next part is a Do-loop that is performed until all of the data from the OBJECT_STATISTICS for the library has been processed.

30 dow (1 = 1) ; 31 clear Rcvs ; 32 reset Rows ; 33 exec sql FETCH C0 FOR :Rows INTO :Rcvs ; 34 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ; 35 if (Rows = 0) ; 36 leave ; 37 endif ; 38 for Counter = 1 to Rows ; 39 clear RcvInfo ; 40 clear QUSEC ; 41 %subst(QualRcv:1:10) = Rcvs(Counter).Rcv ; 42 %subst(QualRcv:11:10) = Rcvs(Counter).Lib ; 43 monitor ; 44 GetRcvInfo(RcvInfo:%len(RcvInfo):QualRcv:'RRCV0100':QUSEC) ; 45 on-error ; 46 QUSEI = 'NotAuth' ; 47 endmon ; 48 if (QUSEI <> ' ') ; //Error found 49 exec sql INSERT INTO JOURNAL_RECEIVER_ERROR 50 VALUES(:QualRcv,:QUSEI) ; 51 iter ; 52 endif ; 53 if (RcvInfo.QJOADT = *all'0') ; 54 exec sql SET :Timestamp1 :Null1 = NULL ; 55 else ; 56 Timestamp1 = %date(%subst(RcvInfo.QJOADT:1:7):*cymd0) + %time(%subst(RcvInfo.QJOADT:8:6):*hms0) ; 57 endif ; 58 if (RcvInfo.QJODDT = *all'0') ; 59 exec sql SET :Timestamp2 :Null2 = NULL ; 60 else ; 61 Timestamp2 = %date(%subst(RcvInfo.QJODDT:1:7):*cymd0) + %time(%subst(RcvInfo.QJODDT:8:6):*hms0) ; 62 endif ; 63 exec sql INSERT INTO JOURNAL_RECEIVER_INFO VALUES(:RcvInfo.QJOJLN00,:RcvInfo.QJOJN01, :RcvInfo.QJOJRLN00,:RcvInfo.QJOJRN00, :Timestamp1 :Null1,:Timestamp2 :Null2) ; 64 Null1 = 0 ; 65 Null2 = 0 ; 66 endfor ; 67 if (Rows < %elem(Rcvs)) ; 68 leave ; 69 endif ; 70 enddo ;

Line 31: Clear the data structure array that will contain the results from the cursor.

Line 32: I am using the RESET operation code to reset the value in this variable to be as it was when it was defined on line 17.

Line 33: Performing a multiple row fetch for the number of rows in the variable Rows into the data structure array.

Line 34: Retrieving the number of rows fetched into the variable Rows.

Lines 35 – 37: If no rows were fetched, leave the Do-loop.

Line 38: Start of a For-group. I am using this to "read" the data structure array and then to call the QjoRtvJrnReceiverInformation API.

Lines 39 and 40: Clear the data structures used by the API.

Lines 41 and 42: Make the Journal Receiver's qualified name.

Lines 43: I had to put the call to the API into a MONITOR group as the API will error if I am not authorized to the Journal Receiver.

Line 44: The QjoRtvJrnReceiverInformation API is called using the name I gave it. I am passing to the API:

1. RcvInfo:  The data structure into which the API will place the results
2. %len(RcvInfo):  Length of the above data structure
3. QualRcv:  Qualified receiver name
4. 'RRCV0100':  Name of the format I want the results in
5. QUSEC:  Data structure that contains the error information

Line 45: If the API errors...

Line 46: In my experience this API only fails when I am not authorized to the receiver. Therefore, I am moving 'NotAuth' to the error message id subfield of the QUSEC data structure.

Line 47: End of monitor group.

Line 48 – 52: If there was an error then the subfield QUSEI will not be blank. I insert a row into the error table containing the qualified receiver name and the error id. Then iterate to get the next element from the data structure array.

Lines 53 – 57: The Attach and Detach dates are return in a 13 long character variable with the format of 'CYYMMDDHHMMSS'. If there is no value to return 13 zeroes are returned. Rather than check for '0000000000000' I am using *ALL'0' which does the same thing.

If no "timestamp" is returned then I want the column to be null. I am using the SQL Set statement to do that by having the variable Timestamp1 with its matching null indicator Null1 set to the value of null.

If there is a "timestamp" I am converting it to a true timestamp value in the variable Timestamp1. Notice that all the RcvInfo subfields are prefixed with the data structures name.

Lines 58 – 62: The same logic for the detach date. In my experience if the detach date is not given this is the receiver that is currently attached to the journal.

Line 63: This statement inserts the rows into the first output table. Notice that on the last line of the statement that the timestamp columns are followed by their null indicators. I need the indicators there for the column to be null. If they were not present then the column would contain the default for a timestamp.

Lines 64 and 65: By moving zero to the SQL null "indicators" I am setting them to not be null.

Line 66: End of the For-group.

Lines 67-69: If the number of rows fetched from the cursor is less than the number of elements of the data structure array then I know I have read the equivalent of "end of file". If not, then as this is a Do-loop the program fetches another set of data from the cursor and repeats.

Line 70: End of Do-loop.

And we finally have:

71 exec sql CLOSE C0 ; 72 *inlr = *on ;

Line 71: The cursor is closed as all the relevant rows have been retrieved from OBJECT_STATISTICS.

What do the output tables look like? Let me start with the first one, the one for successfully retrieved information from QjoRtvJrnReceiverInformation. I am going to use the following SQL statement to show the data:

SELECT * FROM JOURNAL_RECEIVER_INFO ORDER BY 2,4 LIMIT 5

The ORDER BY clause will order the results by the second, journal name, and fourth, journal receiver name, columns. I am also going to return just five rows of results as you don't need to see all of them.

JOURNAL_ JOURNAL RECEIVER RECEIVER ATTACH_ DETACH_ LIBRARY _NAME _LIBRARY _NAME TIMESTAMP TIMESTAMP -------- -------- -------- ---------- ---------------------- ---------------------- JRNLIB PMEDHJRN JRNLIB PMEDHR0079 2019-07-17 15:44:29... 2019-08-29 09:19:45... JRNLIB PMEDHJRN JRNLIB PMEDHR0080 2019-08-29 09:19:45... 2019-09-13 13:12:15... JRNLIB PMEDHJRN JRNLIB PMEDHR0081 2019-09-13 13:12:15... 2020-03-14 17:36:45... JRNLIB PMEDHJRN JRNLIB PMEDHR0082 2020-03-14 17:36:45... 2020-04-18 18:01:15... JRNLIB PMEDHJRN JRNLIB PMEDHR0083 2020-04-18 18:01:15... 2020-09-04 06:58:27... JRNLIB PMEDHJRN JRNLIB PMEDHR0107 2021-12-16 12:21:01... <NULL>

OK, I lied and included a sixth row of results. The last row shows the row in the table for a receiver that is still attached to the journal. The three dots at the end of the timestamp columns show that I have altered the width of the columns to not display the microseconds. I have done this so that this would fit in the width of this screen, and they are always zero.

And what of the error table? There were none for JRNLIB. Therefore, I widened scope of the program replacing the value in the variable Lib to be '*ALL', this means that OBJECT_STATISTICS will return a list of all the journal receivers on this partition. Only then were there error rows. I display them using the following the SQL statement:

SELECT * FROM JOURNAL_RECEIVER_ERROR

The three rows I show below I grabbed at random from the results:

QUALIFIED_RECEIVER_NAME ERROR_MESSAGE ----------------------- ------------- QDBJXQ0002QRECOVERY NotAuth Q5A0472606QSYS NotAuth QAOSDIAC01QUSRSYS NotAuth

The qualified receiver name looks strange until you realize that it contains both the receiver's name (positions 1-10) and library (11-20). I can use the SQL substring to make the results easier to understand:

SELECT SUBSTR(QUALIFIED_RECEIVER_NAME,1,10) AS "Receiver", SUBSTR(QUALIFIED_RECEIVER_NAME,11,10) AS "Library", ERROR_MESSAGE AS "Error" FROM JOURNAL_RECEIVER_ERROR

Which gives me these results, that are so much easier to understand:

Receiver Library Error ---------- ------------- ------------- QDBJXQ0002 QRECOVERY NotAuth Q5A0472606 QSYS NotAuth QAOSDIAC01 QUSRSYS NotAuth

All of the rows were 'NotAuth' caused by QjoRtvJrnReceiverInformation erroring as the profile I was using to run the program was not authorized to these journal receivers. If I had a profile with something more than *PGMR then these errors probably would not have occurred.

I know this is not as slick as using a SQL View or Table Function. But I need to get the information now. I am keeping my fingers crossed that IBM will find this oversight and create a Journal Receiver View or Table Function for us in the future.

 

You can learn more about the QjoRtvJrnReceiverInformation API from the IBM website here.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.