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.

8 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
  3. This is a very useful tool in my opinion. I've created a CL around this SQL procedure which allows me to pass an user profile and the age of the spooled files. This then provides a way of deleting spooled files based on a specific user profile and the spooled file age. And as the SQL procedure allows a value of *ALL for the user profile I can delete all spooled files over 'n' days from the CL program ... very useful when you have 40+ LPARs and no spooled file management in place!

    ReplyDelete
  4. Guillermo ErrandoneaMay 27, 2021 at 7:35 AM

    Very useful

    ReplyDelete
  5. Hi, is there any way to delete spool file with specific user data

    ReplyDelete
  6. Nevermind, I got this to work... I thinkk there was a problem with the "-" minus sign somehow... Thanks

    --CURRENT_TIMESTAMP replaced with CURRENT DATE also works...
    CALL SYSTOOLS.DELETE_OLD_SPOOLED_FILES(
    DELETE_OLDER_THAN => CURRENT DATE - 3 MONTHS,
    P_OUTPUT_QUEUE_LIBRARY_NAME => 'QUSRSYS',
    P_OUTPUT_QUEUE_NAME => 'HOLD',
    P_USER_NAME => '*ALL',
    PREVIEW => 'YES')

    ReplyDelete

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.