Wednesday, June 18, 2025

IBM makes it easier to delete old journal receivers

Old journal receivers can quickly become one of the big wasters of storage on any IBM i partition. I have written before how to identify receivers I think can be deleted. In IBM i 7.6 and 7.5 TR6 comes a new SQL procedure that makes it easy to delete the old receivers.

I need to define what I mean by "old receivers". An "old" journal receiver has been detached from its journal and saved. In my mind if it is detached and been saved, I can delete it. If I need the information contained within it, I can restore it.

I would not use the new procedure to look if I have old receivers, I would use the JOURNAL_RECEIVER_INFO View to get to the information I would want, before using the new procedure.

For example, if I want to find the ten oldest journal on my partition, I would use the following statement:

01  SELECT JOURNAL_RECEIVER_LIBRARY As "Rcvr lib",
02         JOURNAL_RECEIVER_NAME AS "Rcvr name",
03         JOURNAL_LIBRARY AS "Jrn lib",
04         JOURNAL_NAME AS "Jrn name",
05         DATE(ATTACH_TIMESTAMP) AS "Attach",
06         DATE(DETACH_TIMESTAMP) AS "Detached",
07         DATE(SAVE_TIMESTAMP) AS "Saved"
08    FROM QSYS2.JOURNAL_RECEIVER_INFO
09   ORDER BY ATTACH_TIMESTAMP 
10   LIMIT 10

Lines 1 and 2: I want the journal receiver's name and which library it is in. I am also giving all of the column short names so that the results from this statement will fit on the width of this page.

Lines 3 and 4: The journal that the journal receiver was attached to name and library.

Line 5: I don't want the timestamp value for when the receiver was attached to journal, the date will suffice.

Line 6: Same applies to the detach timestamp, I only want the date.

Line 7: The same is true for the timestamp when the receiver was last saved. I want only the date.

Line 8: From the JOURNAL_RECEIVER_INFO View.

Line 9: I want the result with the oldest receiver first.

Line 10: I only want the first ten results.

The results are:

Rcvr lib  Rcvr name  Jrn lib   Jrn name   Attach      Detach Saved
--------- ---------- --------- ---------- ----------  ------ ----------
QMAMS     AMQA000001 QMAMS     AMQAJRN    2019-12-04  <NULL> 2025-03-02
QUSRSYS   QDSNX      QUSRSYS   QDSNX      2021-06-11  <NULL> 2025-03-02
QUSRSYS   QZMF       QUSRSYS   QZMF       2021-06-11  <NULL> 2025-03-02
QUSRSYS   QDSNX1001  QUSRSYS   QDSNX      2022-05-11  <NULL> 2025-03-02
QUSRSYS   QZMF1001   QUSRSYS   QZMF       2022-05-11  <NULL> 2025-03-02
QUSRSYS   QDSNX4128  QUSRSYS   QDSNX      2022-05-11  <NULL> 2025-03-02
QUSRSYS   QZMF4128   QUSRSYS   QZMF       2022-05-11  <NULL> 2025-03-02
QAUDRCV   AUDRCV1354 QSYS      QAUDJRN    2022-05-11  <NULL> 2025-03-02
QRECOVERY QDBJXQ0001 QRECOVERY QDBJRNXRFQ 2022-05-12  <NULL> <NULL>
FLGHT400  QSQJRN8003 FLGHT400  QSQJRN     2022-05-12  <NULL> 2025-03-02

With the Detach date being null means that that all of the ten oldest receivers are still attached to their journals. And the recovery receiver, second from bottom, has never been saved.

I know that there is a Status column, STATUS, that shows the status of the receiver. I have tried using it, and determined that it is simpler to rely on the Detach timestamp and Save timestamp to determine if the receiver is attached to its journal and if it has been saved.

I would not delete any of those receivers, as they are still attached to their journals. Therefore, I need to refine my SQL statement:

01  SELECT JOURNAL_RECEIVER_LIBRARY As "Rcvr lib",
02         JOURNAL_RECEIVER_NAME AS "Rcvr name",
03         JOURNAL_LIBRARY AS "Jrn lib",
04         JOURNAL_NAME AS "Jrn name",
05         DATE(ATTACH_TIMESTAMP) AS "Attach",
06         DATE(DETACH_TIMESTAMP) AS "Detach",
07         DATE(SAVE_TIMESTAMP) AS "Saved"
08    FROM QSYS2.JOURNAL_RECEIVER_INFO
09   WHERE DETACH_TIMESTAMP < SAVE_TIMESTAMP
10     AND DETACH_TIMESTAMP < CURRENT_DATE - 30 DAYS
11   ORDER BY ATTACH_TIMESTAMP
12   LIMIT 10

There are two lines I added:

Line 9: Only select rows where the Save timestamp is greater than the Detach timestamp, in other words the receiver was saved after it was detached.

Line 10: Only select receivers that were detached over 30 days ago.

My results look more promising:

Rcvr lib  Rcvr name  Jrn lib   Jrn name   Attach      Detach     Saved
--------- ---------- --------- ---------- ----------  ---------- ----------
"thetop"  QSQJRN1021 "thetop"  QSQJRN     2023-01-07  2023-09-24 2025-03-02
BANK_022  QSQJRN1053 BANK_022  QSQJRN     2023-01-07  2023-09-24 2025-03-02
BANK_026  QSQJRN1053 BANK_026  QSQJRN     2023-01-07  2023-09-24 2025-03-02
BANK_027  QSQJRN1053 BANK_027  QSQJRN     2023-01-07  2023-09-24 2025-03-02
BANK_028  QSQJRN1053 BANK_028  QSQJRN     2023-01-07  2023-09-24 2025-03-02
BANK_099  QSQJRN1053 BANK_099  QSQJRN     2023-01-07  2023-09-24 2025-03-02
BANKNERST QSQJRN1029 BANKNERST QSQJRN     2023-01-07  2023-09-24 2025-03-02
IDXADV800 QSQJRN0004 IDXADV800 QSQJRN     2023-01-07  2023-09-24 2025-03-02
BANK_025  QSQJRN1053 BANK_025  QSQJRN     2023-01-07  2023-09-24 2025-03-02
BBANK     QSQJRN1009 BBANK     QSQJRN     2023-01-07  2023-09-24 2025-03-02

OK, that is the top ten. How many eligible receivers I have for deleting?

01  SELECT TO_CHAR(COUNT(*),'999G999') AS "Count"
02    FROM QSYS2.JOURNAL_RECEIVER_INFO
03   WHERE DETACH_TIMESTAMP < SAVE_TIMESTAMP
04     AND DETACH_TIMESTAMP < CURRENT_DATE - 30 DAYS

Line 1: I am using the Count scalar function to count the number of rows. To format the number with thousand separator characters I am using the TO_CHAR scalar function.

The rest of this statement is the same as the previous one.

The result was:

Count
--------
   3,175

As I now have an idea of the size of my problem I can start using the new procedure, DELETE_OLD_JOURNAL_RECEIVERS. This procedure is found in the SYSTOOLS library. This procedure will only delete detached receivers.

The syntax for the procedure is:

01  CALL SYSTOOLS.DELETE_OLD_JOURNAL_RECEIVERS(
02           DELETE_OLDER_THAN => CURRENT_TIMESTAMP,
03           JOURNAL_RECEIVER_LIBRARY => '*ALL', -- Default
04           JOURNAL_RECEIVER => '*ALL', -- Default
05           DELETE_UNSAVED => 'NO', -- Default
06           PREVIEW => 'YES') -- Default

There are five parameters:

  1. DELETE_OLDER_THAN:  Starting timestamp for deleting the old receivers. This is a timestamp, and has no default.
  2. JOURNAL_RECEIVER_LIBRARY:  Name of the library the receivers are found in. Default is '*ALL'.
  3. JOURNAL_RECEIVER:  Name of the journal receiver. Default is '*ALL'.
  4. DELETE_UNSAVED:  'YES' will delete receivers that have not been saved. 'NO' only saved receivers. Default is 'NO'.
  5. PREVIEW:  'YES' no deleting, just shows the results. 'NO' will delete all receivers that fit the selected criteria. Default is 'YES'.

If I wanted to delete all detached receivers that were older than 30 days, I would use:

01  CALL SYSTOOLS.DELETE_OLD_JOURNAL_RECEIVERS(
02           DELETE_OLDER_THAN => CURRENT_DATE - 30 DAYS,
03           PREVIEW => 'YES')

I am only using two of the parameters:

Line 2: The receiver has to be more than 30 days old.

Line 3: I know the Preview default is Yes, but in an abundance of caution I am including the Preview = Yes in every statement.

This produces more results than I want to display on this page. I recommend you try it to see what columns are returned from this procedure.

On this partition the receivers for the QAUDJRN journal are in the library QAUDRCV. I want to keep data from the audit journal, so I first use the Audit Data Mart to extract data from the audit journal's receivers before deleting them. I could use the following statement to delete those detached receivers:

01  CALL SYSTOOLS.DELETE_OLD_JOURNAL_RECEIVERS(
02           DELETE_OLDER_THAN => CURRENT_TIMESTAMP - 10 DAYS,
03           JOURNAL_RECEIVER_LIBRARY => 'QAUDRCV',
04           DELETE_UNSAVED => 'NO',
05           PREVIEW => 'YES')

Line 2: I have changed the retention to ten days.

Line 3: As I have not given a JOURNAL_RECEIVER parameter this statement will delete all detached receivers in the library QAUDRCV.

Line 4: I don't have to give this parameter, but I do so that someone else looking at this statement will understand that it will delete all saved receivers.

I would test every one of these DELETE_OLD_JOURNAL_RECEIVERS statements multiple times to make sure I was happy with the results before changing the Preview to 'NO' and let it delete all of the eligible receivers.

I so like this I am going to delete my program to delete old receivers and use this instead.

 

You can learn more about DELETE_OLD_JOURNAL_RECEIVERS procedure from the IBM website here.

 

This article was written for IBM i 7.6 and 7.5 TR6.

3 comments:

  1. I ran the first query and got similar results for my system journal receivers such as QDSNX and QZMF. I was surprised by these very old journal receivers (2018) since I thought the cleanup program was managing them automatically. It turns out that these receivers were in a "partial" status and assume they don't get deleted because of that. There are other journal receivers with active and saved status connected to each of these journals. I assume these old receivers could be deleted since they have been backed up and the status indicates they are not of much use. I tried researching (googling) whether or not these are save to delete but found nothing. Does anyone have any thoughts on this?

    ReplyDelete
    Replies
    1. As the journal receivers are 7 years old I do not see the point in the keeping them on your partition.

      Is it ever likely you will need the information contained within?
      Most companies keep records for 7 years. Why keep these longer?

      Delete
    2. That's what I thought as well. However, I was reluctant to delete these system journal receivers not knowing exactly how the system uses them. I guess worst case I can always restore them.

      Delete

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.