IBM Support

How to Delete Spool files for a User based on a Retention Period

How To


Summary

How to delete spool files associated to a user profile based on a retention period.

Objective

Would like to automate the process of deleting spool files for a user profile while keeping certain spool files based on their creation date.

Steps

The IBM i Operating System does not provide with a native tool/command to delete spool files based on a retention period.  
 
With the introduction of the OUTPUT_QUEUE_ENTRIES  and QSYS2.QCMDEXC SQL Services, we can use SQL to query the creation date on the spool files associated to a user profile and issue a DLTSPLF command for each of the spool files older that the time frame specified on the query.
 
Restrictions:  The user executing the SQL requires *SPLCTL special authority if deleting spool files not created by themselves.
Method 1: Using the DELETE_OLD_SPOOLED_FILES procedure(recommended) :
Step 1:  Using the Run SQL Scripts tool, run the following SQL Statement:
CALL SYSTOOLS.DELETE_OLD_SPOOLED_FILES
    (
    DELETE_OLDER_THAN => CURRENT DATE - 1 MONTH, -- adjust the time frame
        P_OUTPUT_QUEUE_NAME => '*ALL', -- Change to OUTQ name if needed
        P_USER_NAME => 'QBRMS', -- Specify the User ID Profile
        PREVIEW => 'NO'
    );  -- Change to YES to preview the action.
image-20250502072203-1
NOTE 1: Adjust the the CURRENT_DATE value to the retention period.  In our sample, the SQL deletes all spool files for user QBRMS that were created  more than 1 month ago. 
NOTE 2: The SQL can take a very long time to process depending on the number of spool files on the System and the number of spool files associated to the user profile.
NOTE 3:  Use the parameter PREVIEW => 'YES' to preview the list of spool files to be deleted by the operation. 
NOTE 4:  When the parameter PREVIEW => 'NO' no listing of deleted spool files will be returned.
Method 2: Using the OUTPUT_QUEUE_ENTRIES  procedure:
Step 1:  Using the Run SQL Scripts tool, run the following SQL Statement:
WITH SpoolFiles AS (
        SELECT JOB_NAME,
               SPOOLED_FILE_NAME,
               OUTPUT_QUEUE_NAME,
               CREATE_TIMESTAMP
            FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
            WHERE USER_NAME = 'CLEANUP' -- specify the User Profile ID
                  AND CREATE_TIMESTAMP < CURRENT_DATE - 3 MONTH
    )-- specify the retention period
-- Execute the DLTSPLF command for each selected spool file
    SELECT JOB_NAME,
           SPOOLED_FILE_NAME,
           OUTPUT_QUEUE_NAME,
           CREATE_TIMESTAMP,
           QSYS2.QCMDEXC('DLTSPLF FILE(' || SPOOLED_FILE_NAME || ' ) JOB(' || JOB_NAME || ') SPLNBR(*LAST)') AS "Success?"
        FROM SpoolFiles;
image-20250501074922-1
NOTE 1: Adjust the the CURRENT_DATE value to the retention period.  In our sample, the SQL deletes all spool files for user CLEANUP that were created  more than 3 months ago. 
NOTE 2: The SQL can take a very long time to process depending on the number of spool files on the System and the number of spool files associated to the user profile.
NOTE 3: The SQL has no confirmation screen and will automatically delete all spool files that match the criteria regardless if they have been saved on the system
NOTE 5: A '1' on the Completion column indicates that the DLTSPLF completed normally, a '-1' indicates the it failed. 
NOTE 6: The above SQL only works on Releases R730 and above.

Additional Information

More information on the SQL Service SYSTOOLS.DELETE_OLD_SPOOLED_FILES() 

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHFAA2","label":"Print"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.3.0;7.4.0;7.5.0;7.6.0"}]

Document Information

Modified date:
02 May 2025

UID

ibm17232262

Page Feedback