Deleting spool files using SQL

Many of the IBM i partitions I have worked upon have had an issue of old spool files occupying valuable storage space. Since IBM i 7.2 there has been a way for me to analyze the spool files, using SQL, and make decisions on which would be deleted. The process of doing the deleting had to be entrusted to a program containing CL commands.

The latest round of Technology Refreshes, 7.4 TR2 and 7.3 TR8, gave me a way I can delete old spool files using a SQL procedure, DELETE_OLD_SPOOLED_FILES

Warning:  Use this SQL procedure carefully. If you do not you run the risk of deleting all spool files from your partition.

DELETE_OLD_SPOOLED_FILES has five parameters:

1. DELETE_OLDER_THAN:  Delete spool files older than this date. The default for this parameter is three months ago, CURRENT_TIMESTAMP – 3 MONTHS
2. P_OUTPUT_QUEUE_LIBRARY_NAME:  Name of the library that contains the output queue to clear, or '*ALL'. Default is '*ALL'
3. P_OUTPUT_QUEUE_NAME:  Name of the output queue to clear, or '*ALL'. Default is '*ALL'
4. P_USER_NAME:  Name of the user I want to delete the spool files for. Default is '*ALL'
5. PREVIEW:  This is the most important parameter. If NO all the spool files that fit the criteria are deleted. If YES a preview of what is eligible to be deleted is displayed, no delete. Default is 'NO'

For example:

CALL SYSTOOLS.DELETE_OLD_SPOOLED_FILES( DELETE_OLDER_THAN => CURRENT_TIMESTAMP – 14 DAYS, P_OUTPUT_QUEUE_LIBRARY_NAME => 'QGPL', P_OUTPUT_QUEUE_NAME => 'MYOUTQ', P_USER_NAME => 'SIMON', PREVIEW => 'YES')

The parameters can be in any order as the parameter names are also passed to the procedure:

CALL SYSTOOLS.DELETE_OLD_SPOOLED_FILES( P_OUTPUT_QUEUE_LIBRARY_NAME => 'QGPL', PREVIEW => 'YES', DELETE_OLDER_THAN => CURRENT_TIMESTAMP - 14 DAYS, P_USER_NAME => 'SIMON', P_OUTPUT_QUEUE_NAME => 'MYOUTQ')

I cannot use *LIBL in the library name, rather if I exclude the output queue library name parameter the default value of *ALL does the same thing.

CALL SYSTOOLS.DELETE_OLD_SPOOLED_FILES( DELETE_OLDER_THAN => CURRENT_TIMESTAMP - 14 DAYS, P_OUTPUT_QUEUE_NAME => 'MYOUTQ', PREVIEW => 'YES')

The above examples have the preview parameter as YES. As I mentioned above this returns a list of spool files would be deleted if the same was run with the preview as NO. Below I am just going to give one of the rows from the results to illustrate what you can expect:

SPOOLED_FILE_NAME JOB_NAME FILE_NUMBER ----------------- ----------------------- ----------- QPDZDTALOG 193911/SIMON/QPADEV0002 3 USER_NAME SIZE OUTPUT_QUEUE_NAME OUTPUT_QUEUE_LIBRARY_NAME --------- ---- ----------------- ------------------------- SIMON 28 MYOUTQ QGPL CREATE_TIMESTAMP USER_DATA STATUS TOTAL_PAGES -------------------------- --------- ------ ----------- 2020-05-14 17:33:34.765100 - READY 1 COPIES DEVICE_TYPE OUTPUT_PRIORITY SYSTEM ------ ----------- --------------- ------ 1 *SCS DEV740

Down to business, it is time to start deleting some spool files:

CALL SYSTOOLS.DELETE_OLD_SPOOLED_FILES( DELETE_OLDER_THAN => '2020-05-14-17.31.00.000000', P_OUTPUT_QUEUE_NAME => 'MYOUTQ', PREVIEW => 'NO')

Notice rather than give a timestamp calculated from the current timestamp, I passed a hard coded value. As I did give an output queue name any spool file in that output queue older than the passed timestamp was deleted.

This procedure is so simple to use, and delete spool files by accident, I recommend using the preview YES first to ensure what you are going to delete is within your scope before you delete anything.

 

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

 

This article was written for IBM i 7.4 TR2 and 7.3 TR8.