Tuesday, June 16, 2020

Deleting spool files using SQL

deleting old spool files using sql procedure

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.

3 comments:

  1. Wow, I didn’t know this. The preview parameter is great!

    ReplyDelete
  2. hi.. can you please explain how to use it? I am doing STRSQL and typing CALL + Procedure name however it is not showing me fields. Only Paramater sequence like 1,2,3 etc. and parallelly there is a value column. TIA

    ReplyDelete
    Replies
    1. I would say use the parameters as I have given above.

      But when I did I found that it does not return any results, I was doing the PREVIEW='YES'.

      I guess it is time to move from STRSQL to a more modern tool. If you are using ACS you can use the "Run SQL statements". If you are still using Client Acccess open the Ops Navigator, select your partition & there is option there to run SQL scripts too.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.