DELETE_OLD_SPOOLED_FILES procedure

Edit online

The DELETE_OLD_SPOOLED_FILES procedure deletes spooled files according to filtering criteria. It can optionally return a preview of the files that meet the filtering criteria without performing the delete.

Authorization: See Note below.

Read syntax diagramSkip visual syntax diagramDELETE_OLD_SPOOLED_FILES( DELETE_OLDER_THAN => delete-older-than,P_OUTPUT_QUEUE_LIBRARY_NAME => p-output-queue-library-name,P_OUTPUT_QUEUE_NAME => p-output-queue-name,P_USER_NAME => p-user-name,PREVIEW => preview )

The schema is SYSTOOLS.

delete-older-than
A timestamp value that defines the starting point for deleting spooled files. Any spooled file older than this timestamp is eligible for deletion. The default is CURRENT TIMESTAMP - 3 MONTHS.
p-output-queue-library-name
A character or graphic string that specifies the name of a library. Any spooled file in any output queue in this library is eligible for deletion. The default is *ALL.
p-output-queue-name
A character or graphic string that specifies an output queue name. Any spooled file in this output queue is eligible for deletion. The default is *ALL.
p-user-name
A character or graphic string that specifies the name of a user whose spooled files are to be deleted. Any spooled file with this user name is eligible for deletion. The default is *ALL.
preview
A character or graphic string that indicates whether the identified spooled files should be deleted or returned as a result set.
NO
The spooled files will be deleted. This is the default.
YES
A result set list of spooled files will be returned. No files will be deleted.

Note

This procedure is provided in the SYSTOOLS schema as an example of how to delete spooled files and how to return a result set using an SQL procedure. Similar to other Db2® for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar procedures, or to create a customized version within a user-specified schema.

Services provided in SYSTOOLS have authorization requirements that are determined by the interfaces used to implement the service. To understand the authority requirements, extract the SQL for the service and examine the implementation.

Example

  • List all the spooled files in PRT01 that are older than 30 days.
    
CALL SYSTOOLS.DELETE_OLD_SPOOLED_FILES(DELETE_OLDER_THAN => CURRENT DATE - 30 DAYS, 
                                       P_OUTPUT_QUEUE_NAME => 'PRT01', 
                                       PREVIEW => 'YES');
  • Delete all the spooled files in PRT01 that are older than 30 days.
    
CALL SYSTOOLS.DELETE_OLD_SPOOLED_FILES(DELETE_OLDER_THAN => CURRENT DATE - 30 DAYS, 
                                       P_OUTPUT_QUEUE_NAME => 'PRT01', 
                                       PREVIEW => 'NO');