Using SQL to get information about Journal Receivers

Earlier this year I wrote about using the QjoRtvJrnReceiverInformation API to get information about journal receivers. I noticed that in a recent batch of PTFs a new SQL View, JOURNAL_RECEIVER_INFO, which provides all of the information I am interested in. It is easier to get the information I desire from the SQL View than the API, so why would I continue to use the API?

The View JOURNAL_RECEIVER_INFO is found in the QSYS2 library. I recommend that you run the following statement to see all of the columns and information it contains:

SELECT * FROM QSYS2.JOURNAL_RECEIVER_INFO LIMIT 10 ;

The LIMIT 10 means only the first ten rows of results are returned. In my opinion that is enough to see what the View contains. If you want to see all the results for all the journal receivers you can remove the limit from the above statement.

I am working with a partition that contains a couple of thousand journal receivers that are just cluttering it up. I have been asked to delete all the unwanted receivers. First I need to identify them, and the journal they were attached to. The information I need for this is the following:

1. JOURNAL_LIBRARY:  The library the journal is in
2. JOURNAL_NAME:  The journal's name
3. JOURNAL_RECEIVER_LIBRARY:  The library the journal receiver is in
4. JOURNAL_RECEIVER_NAME:  The journal receiver's name
5. ATTACH_TIMESTAMP:  The date and time the journal receiver was created and attached to the journal
6. DETACH_TIMESTAMP:  The date and time the journal receiver was detached from the journal. This column will contain null if the receiver is currently attached to the journal

I put all of these columns into the following SQL statement, and limited the results returned to ten.

01 SELECT JOURNAL_LIBRARY,JOURNAL_NAME,JOURNAL_RECEIVER_LIBRARY,JOURNAL_RECEIVER_NAME, 02 ATTACH_TIMESTAMP,DETACH_TIMESTAMP 03 FROM QSYS2.JOURNAL_RECEIVER_INFO 04 ORDER BY JOURNAL_LIBRARY,JOURNAL_NAME,ATTACH_TIMESTAMP 05 LIMIT 10 ;

The results are:

JOURNAL_ JOURNAL_ RECEIVER LIBRARY NAME _LIBRARY NAME ATTACH_TIMESTAMP DETACH_TIMESTAMP -------- -------- -------- ---------- ---------------------- ---------------------- LIBRARY1 JOURNAL1 LIBRARY1 JOURNR0116 2019-08-11 02:30:56... 2019-08-18 02:29:45... LIBRARY1 JOURNAL1 LIBRARY1 JOURNR0117 2019-08-18 02:29:45... 2019-08-25 02:31:38... LIBRARY1 JOURNAL1 LIBRARY1 JOURNR0118 2019-08-25 02:31:38... 2019-09-01 02:42:17... LIBRARY1 JOURNAL1 LIBRARY1 JOURNR0119 2019-09-01 02:42:17... 2019-09-08 02:41:08... LIBRARY1 JOURNAL1 LIBRARY1 JOURNR0120 2019-09-08 02:41:08... 2019-09-15 02:47:46... LIBRARY1 JOURNAL1 LIBRARY1 JOURNR0121 2019-09-15 02:47:46... 2019-09-22 02:11:25... LIBRARY1 JOURNAL1 LIBRARY1 JOURNR0122 2019-09-22 02:11:25... 2019-09-29 02:12:03... LIBRARY1 JOURNAL1 LIBRARY1 JOURNR0123 2019-09-29 02:12:03... 2019-10-06 02:10:14... LIBRARY1 JOURNAL1 LIBRARY1 JOURNR0124 2019-10-06 02:10:14... 2019-10-13 02:05:05... LIBRARY1 JOURNAL1 LIBRARY1 JOURNR0125 2019-10-13 02:05:05... 2019-10-20 02:08:43...

As you can see from these results I have journal receivers that are many years old. I can use the following statement to see how many:

01 SELECT YEAR(ATTACH_TIMESTAMP) as "Attach year",COUNT(*) as "Count" 02 FROM QSYS2.JOURNAL_RECEIVER_INFO 03 GROUP BY YEAR(ATTACH_TIMESTAMP) 04 ORDER BY YEAR(ATTACH_TIMESTAMP) ;

Which returns:

Attach year Count ----------- ----- 2008 2 2014 1 2018 1 2019 168 2020 481 2021 509 2022 525

It is the GROUP BY that allows this statement to "break" by year.

But these results are not really what I wanted as they contain the journal receivers that are currently attached to the journals. As I mentioned above the receivers that are still attached to the journals have a detach timestamp of null. I can use this in the following statement to exclude them:

01 SELECT YEAR(ATTACH_TIMESTAMP) as "Attach year",COUNT(*) as "Count" 02 FROM QSYS2.JOURNAL_RECEIVER_INFO 03 WHERE DETACH_TIMESTAMP IS NOT NULL 04 GROUP BY YEAR(ATTACH_TIMESTAMP) 05 ORDER BY YEAR(ATTACH_TIMESTAMP) ;

The numbers of journal receivers are less than the previous statement, but there are still too many of them.

Attach year Count ----------- ----- 2019 168 2020 480 2021 509 2022 481

If I wanted to get a list of journal receivers by the journal they belong to I can use the following:

01 SELECT JOURNAL_LIBRARY,JOURNAL_NAME,COUNT(*) AS "Count" 02 FROM QSYS2.JOURNAL_RECEIVER_INFO 03 GROUP BY JOURNAL_LIBRARY,JOURNAL_NAME 04 ORDER BY 3 DESC,JOURNAL_LIBRARY,JOURNAL_NAME 05 LIMIT 10 ;

The above will list the "worse offenders" the top ten journals with the most journal receivers.

JOURNAL_LIBRARY JOURNAL_NAME Count --------------- ------------ ----- QSYS QACGJRN 245 LIBRARY1 JOURNAL1 160 LIBRARY5 LIB5JRN1 160 LIBRARY5 LIB5XRN3 160 LIBRARY7 TRAINJRN 160 QUSRSYS QA0SDIAJRN 160 QUSRSYS QIPFILTER 160 QUSRSYS QIPNAT 160 LIBRARY8 E2A1JRN 122 LIBRARY8 A1IJRA 16

I tend to be wary of doing things to objects in IBM's libraries. I looked up what the IBM journals in this list are used for:

• QACGJRN  Job accounting entries
• QA0SDIAJRN  Document Library Objects (DLO) changes
• QIPFILTER  QVPN journal entries
• QIPNAT  Network Address Translation (NAT) entries

Personally I see no reason for keeping information from those journals for more than a month.

If I wanted to delete all the journal receivers that are more than a year old I can use the following to create a table containing a list of them:

01 CREATE TABLE QTEMP.TODELETE AS 02 (SELECT JOURNAL_RECEIVER_LIBRARY,JOURNAL_RECEIVER_NAME,DETACH_TIMESTAMP 03 FROM QSYS2.JOURNAL_RECEIVER_INFO 04 WHERE DETACH_TIMESTAMP IS NOT NULL 05 AND DETACH_TIMESTAMP < CURRENT_TIMESTAMP - 1 YEAR 06 ORDER BY DETACH_TIMESTAMP) 07 WITH DATA ;

I could then write a simple program to read the output file and delete the journal receivers.

 

You can learn more about the JOURNAL_RECEIVER_INFO SQL View from the IBM website here.

 

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