Delete Old Spooled files from SQL - IBM i

 

Spooled Files

Spooled files are generated when when system writes (prints) the data to an Output Queue. These could be Reports generated by the programs using Printer File (PRTF), System dumps or Job logs. 

Spool files usually doesn't occupy much system space and often ignored. But, they could occupy the considerable amount of time if the spool files hasn't been cleared for long time. 

Spool files can be deleted by using DLTSPLF (Delete Spooled File) command. 

One other way of deleting spool files is from SQL by using DELETE_OLD_SPOOLED_FILES procedure. This procedure is present in SYSTOOLS library.

One good thing about this procedure is there is an option to preview. 

Let's have a look at how this works. 

In the above procedure call, we are only passing one parameter. 

• 'PREVIEW' - By passing 'YES', this procedure won't delete any spool file and returns the list of spooled files matching with the selection. 

We have not passed any selection to delete the spooled files. So, procedure would consider the default parameters to delete or preview the spool files to be deleted. 

Below are the other parameters for this procedure. 

DELETE_OLDER_THAN -  Timestamp needs to be passed as a parameter. This would be considered as the starting point for deleting the spool files. Any spool file older than this timestamp would be considered for deletion. 

Default value for this parameter is 'CURRENT TIMESTAMP - 3 MONTHS'. If we don't pass this parameter all the spool files older than 3 months would be considered for deletion. 

P_OUTPUT_QUEUE_NAME - Name of the Output Queue. Spool files from this OUTQ would be considered for deletion. 

Default value for this parameter is '*ALL'. If we don't pass this parameter spooled files from all the OUTQs would be considered for deletion. 

P_OUTPUT_QUEUE_LIBRARY_NAME - Name of the library Output Queue is present in. Spooled files from all the output queues from this library would be considered for deletion (If OUTQ name has been passed only that specific OUTQ would be considered). 

Default value for this parameter is '*ALL'. If we don't pass this parameter spooled files from the OUTQs in all the libraries would be considered for deletion. 

P_USER_NAME - Name of the user whose spooled files are to be deleted. All the spooled files for this user would be considered for deletion.

Default value for this parameter is '*ALL'. If we don't pass this parameter spooled files for all the users would be considered for deletion. 

Let's have a look at an example by passing these parameters. 

In the above example, we are passing all the parameters and procedure would return the list of spooled files matching with the parameters passed. We can just pass the parameters that are required and leave the remaining to default. 

This would return the list of spooled files that are considered for deletion (PREVIEW => YES). 

Passing 'NO' to PREVIEW or removing PREVIEW parameter would delete these spool files. 

One thing to note is that, User should have sufficient authority to the spooled files to be deleted. 

• User should have authority to the DLTSPLF command. 
• User should have authority to the QSYS2/OUTPUT_QUEUE_ENTRIES_BASIC view.