Pages

Wednesday, September 28, 2022

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.

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.