Deleting QAUDJRN's journal receivers if they are in QSYS

A couple of weeks ago I wrote about deleting the System Audit Journal's receivers. The scenario had the journal's receivers not in the QSYS library, but in QGPL. Someone messaged me saying that in their IBM i partition QAUDJRN's receivers are in QSYS. When they ran the SQL procedure to delete old journal receivers, DELETE_OLD_JOURNAL_RECEIVERS it returned no results.

01 CALL SYSTOOLS.DELETE_OLD_JOURNAL_RECEIVERS( 02 DELETE_OLDER_THAN => CURRENT_TIMESTAMP, 03 JOURNAL_RECEIVER_LIBRARY => 'QSYS', 04 JOURNAL_RECEIVER => 'QAUD%', 05 DELETE_UNSAVED => 'NO', 06 PREVIEW => 'YES')

I checked the last save information for the journal receivers in QSYS. The SQL table function OBJECT_STATISTICS's SAVE_TIMESTAMP column was null. Using Display Object Description, DSPOBJD, the save date field, ODSDAT, was blank. How could I determine how old each of the receivers were?

We created a support incident with IBM and after a couple of days we received the following response:

A SAVSYS does not update the save history information for objects in QSYS. The SQL service DELETE_OLD_JOURNAL_RECEIVERS uses that save history information to determine if an object was saved and is why it is not seeing those that were saved. I agree that it is confusing when WRKJRNA shows they have been saved but this is working correctly and how it has always been. Refer to the following document for an explanation and it does state that journal receivers for QAUDJRN should not reside in QSYS library.

And a link to the IBM Support page about this scenario, which you can see here.

How to delete journal receivers from QSYS?

The method I devised is not as nice and simple as using DELETE_OLD_JOURNAL_RECEIVERS, but I can do it.

The Work With Journal Attributes command, WRKJRNA, can produce a print of the journal's receivers along with date the journal receiver attached to the journal:

WRKJRNA JRN(QSYS/QAUDJRN) OUTPUT(*PRINT) DETAIL(*RCVDIR)

It creates a spool file called QPDSPJNA. I am going to need the job name that created this spool file, and its file number. I can use the SQL View OUTPUT_QUEUE_ENTRIES_BASIC to retrieve that information for me:

01 SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER 02 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 03 WHERE SPOOLED_FILE_NAME = 'QPDSPJNA' 04 ORDER BY CREATE_TIMESTAMP DESC 05 LIMIT 1

Line 1: I just want the spool file name, job name, and the file number in my results.

Line 2: I am using OUTPUT_QUEUE_ENTRIES_BASIC rather than OUTPUT_QUEUE_ENTRIES as it returns the results to me faster than the other.

Line 3: I only want the results for the spool file I created just a few minutes ago.

Line 4: By ordering the results in create timestamp order in descending order the most recent spool file is first.

Line 5: By limiting the number of my results to one, I get only the details for the most recent spool file.

The results are:

SPOOLED _FILE_ FILE_ NAME JOB_NAME NUMBER -------- ----------------------- ------- QPDSPJNA 188505/SIMON/QPADEV0000 1

With that information I can use the SQL table function SPOOLED_FILE_DATA to view the contents of the spool file:

01 SELECT SPOOLED_DATA 02 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( 03 JOB_NAME => '188505/SIMON/QPADEV0000', 04 SPOOLED_FILE_NAME => 'QPDSPJNA', 05 SPOOLED_FILE_NUMBER => 1)) 06 WHERE SUBSTR(SPOOLED_DATA,69,5) = 'SAVED'

Line 1: I only want the spooled data, the actual data in the spool file, in my results.

Line 3: This is the job name I discovered above.

Line 4: The spool file name.

Line 5: And the file number.

Line 6: I am only interested in the results were starting in the 69th position are the letters "SAVED".

I am only going to show the first five results:

SPOOLED_DATA ----------------------------------------------------------------------------- 00001 QAUD0002 QSYS 04/22/24 11/02/25 SAVED ... 00002 QAUD0003 QSYS 05/11/24 11/02/25 SAVED ... 00003 QAUD0004 QSYS 05/30/24 11/02/25 SAVED ... 00004 QAUD0005 QSYS 05/30/24 11/02/25 SAVED ... 00005 QAUD0006 QSYS 05/30/24 11/02/25 SAVED ...

The second column is the name of the journal receiver.

The third is the library that journal receiver is in.

The fourth is the date the journal receiver was attached to the journal.

The fifth is the date this object was last saved.

The sixth is the journal receiver's status.

I only need the journal receiver's name and library, and the attach date. I can get those as columns from the spool file by substring them from the SPOOLED_DATA column:

01 SELECT DATE(TIMESTAMP_FORMAT(SUBSTR(SPOOLED_DATA,43,8),'MMDDYY')) AS "Attach date", 02 SUBSTR(SPOOLED_DATA,28,10) AS "Library", 03 SUBSTR(SPOOLED_DATA,13,10) AS "Receiver" 04 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( 05 JOB_NAME => '188505/SIMON/QPADEV0000', 06 SPOOLED_FILE_NAME => 'QPDSPJNA', 07 SPOOLED_FILE_NUMBER => 1)) 08 WHERE SUBSTR(SPOOLED_DATA,69,5) = 'SAVED'

Line 1: I am substring the attach "date", and then converting it to a real date. As I am in the USA the date is MMDDYY, your job may be using a different date format. You will need to replace the MMDDYY with the date format of your job or system.

Line 2: Substring the journal receiver's library.

Line 3: Substring the journal receiver's name.

The rest of the statement is the same as before.

The first five results are:

Attach date Library Receiver ----------- ------- -------- 2024-04-22 QSYS QAUD0002 2024-05-11 QSYS QAUD0003 2024-05-30 QSYS QAUD0004 2024-05-30 QSYS QAUD0005 2024-05-30 QSYS QAUD0006

As you can see I have the date, that I am going to use to determine which journal receivers will be removed, and its library and name.

To see how many journal receivers QUDJRN I modified the above statement to use a COUNT scalar function:

01 SELECT COUNT(*) AS "Count" 02 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( 03 JOB_NAME => '188505/SIMON/QPADEV0000', 04 SPOOLED_FILE_NAME => 'QPDSPJNA', 05 SPOOLED_FILE_NUMBER => 1)) 06 WHERE SUBSTR(SPOOLED_DATA,69,5) = 'SAVED' 07 AND DATE(TIMESTAMP_FORMAT(SUBSTR(SPOOLED_DATA,43,8),'MMDDYY')) < CURRENT_DATE - 100 DAYS

Line 1: In place of all of the substring statements I am just using the Count scalar function.

Lines 2 – 6: This part of the statement is identical to the previous one.

Line 7: After consulting with the person who messaged we decided that we wanted to deleted QAUDJRN journal receivers that has been detached over 90 days ago. As we only have the attach date, not the detach date, the decision was made to delete journal receivers that were attached and saved 100 days ago.

The result was:

Count ------ 1191

That is a large number of journal receivers, that would be occupying a substantial amount of storage.

I am going to make a SQL View, using the QPDSPJNA spool file, to list all of the saved journal receivers. This way I can use this View for any journal's receivers whenever I want. The code for the View is:

01 CREATE OR REPLACE VIEW MYLIB.JOURNAL_RECEIVER_LIST 02 SYSTEM NAME "JRNRCVLIST" 03 (ATTACH_DATE,LIBRARY,RECEIVER) AS 04 SELECT DATE(TIMESTAMP_FORMAT(SUBSTR(SPOOLED_DATA,43,8),'MMDDYY')), 05 SUBSTR(SPOOLED_DATA,28,10), 06 SUBSTR(SPOOLED_DATA,13,10) 07 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( 08 JOB_NAME => (SELECT JOB_NAME 09 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 10 WHERE SPOOLED_FILE_NAME = 'QPDSPJNA' 11 ORDER BY CREATE_TIMESTAMP DESC 12 LIMIT 1), 13 SPOOLED_FILE_NAME => 'QPDSPJNA', 14 SPOOLED_FILE_NUMBER => '*LAST')) 15 WHERE SUBSTR(SPOOLED_DATA,69,5) = 'SAVED' ;

Line 1: I always like using the CREATE OR REPLACE. If I need to change the View in future I do not have to delete this existing view, I can just execute the modified statement.

Line 2: As the name of my View is longer than ten characters I am giving it a short system name too.

Line 3: I need to give names for the three columns of information I will extracting from the spool file's data.

Lines 8 – 12: I am getting the job name from OUTPUT_QUEUE_ENTRIES_BASIC, that I have shown in a previous example, for the job name parameter.

Line 14: I can just use "*LAST" in place of the actual file number. This use the file number of the last QPDSPJNA spool file created by this job.

After creating the SQL View I can see what is returns using the following in ACS's Run SQL Scripts:

01 CL: WRKJRNA JRN(QSYS/QAUDJRN) OUTPUT(*PRINT) DETAIL(*RCVDIR) ; 02 SELECT * FROM MYLIB.JOURNAL_RECEIVER_LIST ;

This returns the same first five results as before:

Attach date Library Receiver ----------- ------- -------- 2024-04-22 QSYS QAUD0002 2024-05-11 QSYS QAUD0003 2024-05-30 QSYS QAUD0004 2024-05-30 QSYS QAUD0005 2024-05-30 QSYS QAUD0006

Just to prove that this View is universal, I can return the journal receiver information for a different journal:

01 CL: WRKJRNA JRN(MYLIB2/QSQJRN) OUTPUT(*PRINT) DETAIL(*RCVDIR) ; 02 SELECT * FROM RPGPGM1.JOURNAL_RECEIVER_LIST ;

Line 1: I am creating the spool file for the journal QSQJRN that is in the library MYLIB2.

Using the same View the results for QSQJRN:

Attach date Library Receiver ----------- ------- ---------- 2025-10-17 MYLIB2 QSQJRN0001 2025-10-22 MYLIB2 QSQJRN0002 2025-10-22 MYLIB2 QSQJRN0003 2025-10-23 MYLIB2 QSQJRN0004 2025-10-27 MYLIB2 QSQJRN0005

As you can see the results returned are for QSQJRN, and not for QAUDJRN. This is another example of why I love using Views.

Using this View is going to make the rest of my examples easier to write, and for you to understand.

I am going to use the CL command to delete the journal receivers, DLTJRNRCV. Its syntax is:

DLTJRNRCV JRNRCV( < library > / < journal receiver > ) DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV)

Returning to QAUDJRN, I can use that CL command with the QCMDEXC scalar function:

01 SELECT ('DLTJRNRCV JRNRCV(' || RTRIM(LIBRARY) || '/' || RTRIM(RECEIVER) || 02 ') DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV)') 03 AS "CL command" 04 FROM MYLIB.JOURNAL_RECEIVER_LIST 05 WHERE ATTACH_DATE < CURRENT_DATE - 100 DAYS

Lines 1 – 3: I am creating the CL command by concatenating the journal receiver library and name I will substring from the spool file. I am using the RTRIM to remove the trailing blanks from both columns. The double pipe ( || ) is a shorthand equivalent of CONCATENATE.

Line 4: I am using the View I created above. By using the View all the "complicated" substring-ing and date formatting is not needed in this statement.

Line 5: I only want to return journal receivers that are older than 100 days ago.

I am only showing the first five results:

CL command ------------------------------------------------------------------------- DLTJRNRCV JRNRCV(QSYS/QAUD0002) DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV) DLTJRNRCV JRNRCV(QSYS/QAUD0003) DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV) DLTJRNRCV JRNRCV(QSYS/QAUD0004) DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV) DLTJRNRCV JRNRCV(QSYS/QAUD0005) DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV) DLTJRNRCV JRNRCV(QSYS/QAUD0006) DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV)

Now I need to add the QCMDEXC scalar function to the statement:

01 SELECT QSYS2.QCMDEXC('DLTJRNRCV JRNRCV(' || RTRIM(LIBRARY) || '/' || RTRIM(RECEIVER) || 02 ') DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV)') AS "Return code", 03 ('DLTJRNRCV JRNRCV(' || RTRIM(LIBRARY) || '/' || RTRIM(RECEIVER) || 04 ') DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV)') 05 AS "CL command" 06 FROM MYLIB.JOURNAL_RECEIVER_LIST 07 WHERE ATTACH_DATE < CURRENT_DATE - 100 DAYS

Lines 1 – 2: This is the QCMDEXC scalar function containing the CL command, that is built the same way as the CL command was in the previous statement.

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

I am not going to execute this statement, and I recommend you do not too.

Deleting old journal receivers should be part of your regular "cleanup" routines. No-one wants to submit a SQL statement, they want to have a program that will be added to the job schedular to be called on a regular basis. Below is the RPG program I wrote for this person to use:

01 **free 02 ctl-opt main(Main) option(*srcstmt) ; 03 dcl-proc Main ; 04 exec sql 05 CALL QSYS2.QCMDEXC('WRKJRNA JRN(QSYS/QAUDJRN) OUTPUT(*PRINT) DETAIL(*RCVDIR)') ; 06 exec sql DROP TABLE IF EXISTS MYLIB.JRNRCVDLT ; 07 exec sql 08 CREATE TABLE MYLIB.JRNRCVDLT 09 (RETURN_CODE,CL_COMMAND) AS 10 SELECT QSYS2.QCMDEXC('DLTJRNRCV JRNRCV(' || RTRIM(LIBRARY) || '/' || RTRIM(RECEIVER) || 11 ') DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV)'), 12 ('DLTJRNRCV JRNRCV(' || RTRIM(LIBRARY) || '/' || RTRIM(RECEIVER) || 13 ') DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV)') 14 FROM MYLIB.JOURNAL_RECEIVER_LIST 15 WHERE ATTACH_DATE < CURRENT_DATE - 100 DAYS) 16 WITH DATA ; 17 exec sql CALL QSYS2.QCMDEXC('DLTSPLF FILE(QPDSPJNA) SPLNBR(*LAST)') ; 18 return ; 19 end-proc ;

Line 2: I am getting into the habit of always having a Main procedure, by doing so none of the RPG cycle logic is included in the program at compile time. And I always like the *SRCSTMT as then the compiler listing source numbers are the same as the source line numbers.

Lines 4 and 5: I am using the QCMDEXC SQL procedure to run the WRKJRNA command to generate the spool file.

Line 6: I want to output the results of my deletions into a output table. Here I am deleting the table if it already exists.

Lines 7 – 16: This statement creates an output file depending on the results of another.

Line 8: I am creating the Table JRNRCVDLT in my library.

Line 9: The Table will only contain two columns: The return code from the QCMDEXC scalar function, and the CL command that was executed.

Lines 10 and 11: The QCMDEXC scalar function. This will return 1 if the command is successful, and -1 if it fails.

Lines 12 and 13: The same CL command that is executed by the QCMDEXC scalar function.

Line 14: Retrieve the results from the View I built.

Line 15: Same date test as before.

Line 17: When the SQL statement to delete the journal receivers has completed I am deleting the spool file that was created on line 4 and 5, as it is no longer needed.

After compiling the program we ran it. After it completed I wanted to see what has been deleted by querying the output table that was created.

01 SELECT * 02 FROM MYLIB.JRNRCVDLT 03 LIMIT 5

Even though I am only showing only the first five results, all of the journal receivers that were more than 100 days old were deleted.

RETURN _CODE CL_COMMAND ------ ---------------------------------------------------------------------------- 1 DLTJRNRCV JRNRCV(QSYS/QAUDRCV0002) DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV) 1 DLTJRNRCV JRNRCV(QSYS/QAUDRCV0003) DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV) 1 DLTJRNRCV JRNRCV(QSYS/QAUDRCV0004) DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV) 1 DLTJRNRCV JRNRCV(QSYS/QAUDRCV0005) DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV) 1 DLTJRNRCV JRNRCV(QSYS/QAUDRCV0006) DLTOPT(*IGNINQMSG *IGNEXITPGM *IGNTGTRCV)

After all of the above the person who messaged me was contacted days later by IBM Support to say they has a new version of DELETE_OLD_JOURNAL_RECEIVERS that should delete all the journal receivers in QSYS too. At the time I am writing this the person is working with IBM to test this new version. I am assuming that once this new version is a success it will be included in a future group of PTFs. I recommend before you try creating the View and program, you try DELETE_OLD_JOURNAL_RECEIVERS with the following parameters:

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

If you do not see any results run the WRKJRNA command to see if there are really receivers to delete, or if there not.

If there are results returned by DELETE_OLD_JOURNAL_RECEIVERS use it instead of my examples.

When IBM does provide a modified DELETE_OLD_JOURNAL_RECEIVERS this post still provides value as an example of how to take data from a spool file, extract it, create a View for that data, and use it in a program.

 

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