Deleting all spool files with a certain form type

The idea for this post came from a question I was asked in a presentation last month. The more I think about the solution I developed I am of the opinion that many of you might find this useful.

The question was: Is there a way, using SQL, to delete all the spool files that have a particular form type?

I have been using the DELETE_OLD_SPOOLED_FILES SQL procedure to delete old spool files on my partition. Alas, the Procedure does not have a parameter for form type.

Another idea sprung to mind, I could use the OUTPUT_QUEUE_ENTRIES_BASIC SQL View to select the spool files I want to delete, and do the deleting in the same statement using the appropriate CL command in the QCMDEXC Scalar function. I am sure some of you are wondering why I picked OUTPUT_QUEUE_ENTRIES_BASIC View rather than OUTPUT_QUEUE_ENTRIES View. The "basic" version has less columns than the "full" version, which makes it faster to return results.

The crucial part of my design is that I must get the selection criteria correct when I use the OUTPUT_QUEUE_ENTRIES_BASIC.

In my output queue, MYOUTQ in MYLIB, I have a number of spool files with the same name:

01 SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER, 02 FORM_TYPE 03 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 04 WHERE OUTPUT_QUEUE_NAME = 'MYOUTQ' 05 AND OUTPUT_QUEUE_LIBRARY_NAME = 'MYLIB'

I think the column names, above, explain the content of each column I selected. The results of the above statement are:

SPOOLED_FILE FILE_ FORM_ _NAME JOB_NAME NUMBER TYPE ------------ ----------------------- ------ ------ QPQUPRFIL 133240/SIMON/QPADEV0001 1 *STD QPQUPRFIL 133240/SIMON/QPADEV0001 2 FORM_1 QPQUPRFIL 133240/SIMON/QPADEV0001 3 *STD QPQUPRFIL 133240/SIMON/QPADEV0001 4 *STD QPQUPRFIL 133240/SIMON/QPADEV0001 5 FORM_1 QPQUPRFIL 133240/SIMON/QPADEV0001 6 *STD

There are two spool files with the form type of "FORM_1". Before I delete these files I want to check if there are any other spool files with that form type on this partition. I can adapt my previous SQL statement to retrieve that information:

01 SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER, 02 FORM_TYPE 03 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 04 WHERE FORM_TYPE = 'FORM_1'

Line 4: The Where clause is where I said I wanted on spool files with the form type of "FORM_1".

My results are:

SPOOLED_FILE FILE_ FORM_ _NAME JOB_NAME NUMBER TYPE ------------ ------------------------ ------ ------ QPQUPRFIL 133240/SIMON/QPADEV0001 2 FORM_1 QPQUPRFIL 133240/SIMON/QPADEV0001 5 FORM_1

I only have spool files with that form type in my output queue.

I am going to use the CL command Delete Spool File, DLTSPLF, to delete any spool files that fit the criteria I select. I wanted to test that I built the command correctly. I used the following statement:

01 SELECT 'DLTSPLF FILE(' || SPOOLED_FILE_NAME || 02 ') JOB(' || JOB_NAME || 03 ') SPLNBR(' || FILE_NUMBER || ')' 04 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 05 WHERE OUTPUT_QUEUE_NAME = 'MYOUTQ' 06 AND OUTPUT_QUEUE_LIBRARY_NAME = 'MYLIB' 07 AND FORM_TYPE = 'FORM_1'

Lines 1 – 3: The DLTSPLF command needs the spool file name, job name, and spool file number, all of which are found as columns in OUTPUT_QUEUE_ENTRIES_BASIC View. The double pipes ( || ) are the shortcut to replace CONCAT or CONCATENATE.

Lines 5 and 6: I am going to perform the test sing the spool files in my output queue, as I have two eligible spool files.

The results are:

00001 --------------------------------------------------------------- DLTSPLF FILE(QPQUPRFIL) JOB(133240/SIMON/QPADEV0001) SPLNBR(2) DLTSPLF FILE(QPQUPRFIL) JOB(133240/SIMON/QPADEV0001) SPLNBR(5)

I have two valid commands there.

In this statement I am going to delete all spool files with form type of "FORM_1" in any output queue:

01 SELECT QSYS2.QCMDEXC('DLTSPLF FILE(' || SPOOLED_FILE_NAME || 02 ') JOB(' || JOB_NAME || 03 ') SPLNBR(' || FILE_NUMBER || ')') 04 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 05 WHERE FORM_TYPE = 'FORM_1'

Lines 1 – 3: The only thing needed in the columns part of the statement is the QCMDEXC Scalar function.

Line 5: I am just selecting any spool file that has the form type of "FORM_1".

The results are the return code from the QCMDEXC Scalar function:

0001 ----- 1 1

As both the return codes are "1" it means that the CL command completed successfully.

I can use the first SQL I gave in this post to check that the spool files were deleted:

POOLED_FILE FILE_ FORM_ _NAME JOB_NAME NUMBER TYPE ------------ ------------------------ ------ ------ QPQUPRFIL 133240/SIMON/QPADEV0001 1 *STD QPQUPRFIL 133240/SIMON/QPADEV0001 3 *STD QPQUPRFIL 133240/SIMON/QPADEV0001 4 *STD QPQUPRFIL 133240/SIMON/QPADEV0001 6 *STD

Spool files with the file numbers 2 and 5 are no longer present as they were the ones deleted.

I would consider the statement to delete any spool file with the form type of "FORM_1" as rather dangerous. I am more likely to have all of the spool files with the that form type in one output queue. I modified the Where clause in my SQL statement to select my output queue:

01 SELECT QSYS2.QCMDEXC('DLTSPLF FILE(' || SPOOLED_FILE_NAME || 02 ') JOB(' || JOB_NAME || 03 ') SPLNBR(' || FILE_NUMBER || ')') 04 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 05 WHERE OUTPUT_QUEUE_NAME = 'MYOUTQ' 06 AND OUTPUT_QUEUE_LIBRARY_NAME = 'MYLIB' 07 AND FORM_TYPE = 'FORM_1'

Lines 5 and 6: This is where I narrow down the selection to my output queue only.

I am not going to show the results as it is not possible to show something that is not there.

I still think that criteria is too wide. I want to keep recent spool files, just delete the ones that are older than two weeks. A simple addition to the Where clause accomplishes that:

01 SELECT QSYS2.QCMDEXC('DLTSPLF FILE(' || SPOOLED_FILE_NAME || 02 ') JOB(' || JOB_NAME || 03 ') SPLNBR(' || FILE_NUMBER || ')') 04 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 05 WHERE OUTPUT_QUEUE_NAME = 'MYOUTQ' 06 AND OUTPUT_QUEUE_LIBRARY_NAME = 'MYLIB' 07 AND FORM_TYPE = 'FORM_1' 08 AND DATE(CREATE_TIMESTAMP) < CURRENT_TIMESTAMP - 14 DAYS

Line 8: I use the Date Scalar function to extract the date from the CREATE_TIMESTAMP column. And I comparing it to the today's day minus 14 days.

I think this statement could be refined. What about spool files that were held or are in a saved status? Do I really want to keep those? No. I make another addition to the Where clause:

01 SELECT QSYS2.QCMDEXC('DLTSPLF FILE(' || SPOOLED_FILE_NAME || 02 ') JOB(' || JOB_NAME || 03 ') SPLNBR(' || FILE_NUMBER || ')') 04 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 05 WHERE OUTPUT_QUEUE_NAME = 'MYOUTQ' 06 AND OUTPUT_QUEUE_LIBRARY_NAME = 'MYLIB' 07 AND FORM_TYPE = 'FORM_1' 08 AND DATE(CREATE_TIMESTAMP) < CURRENT_TIMESTAMP - 14 DAYS 09 AND STATUS IN ('SAVED','HELD')

Line 9: Will only delete spool files with the status of saved or held.

If I wanted to run statement I am going to need to put the resulting return codes somewhere, here I am going to place the return codes into a Table. This is the statement I used:

01 CREATE TABLE QTEMP.OUTPUT 02 (SPLFNAME,JOBNAME,FILENBR,RTNCODE) AS 03 (SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER, 04 QSYS2.QCMDEXC('DLTSPLF FILE(' || SPOOLED_FILE_NAME || 05 ') JOB(' || JOB_NAME || 06 ') SPLNBR(' || FILE_NUMBER || ')') 05 WHERE OUTPUT_QUEUE_NAME = 'MYOUTQ' 06 AND OUTPUT_QUEUE_LIBRARY_NAME = 'MYLIB' 07 AND FORM_TYPE = 'FORM_1' 08 AND DATE(CREATE_TIMESTAMP) < CURRENT_TIMESTAMP - 14 DAYS 09 AND STATUS IN ('SAVED','HELD')) 10 WITH DATA

Line 1: My table is going to be called OUTPUT and will be created in QTEMP.

Line 2: As the SQL Statement used to create this table is creating a new column, the return code from the QCMDEXC Scalar function, I need to provide a list of names of the columns that will be in the Table.

Lines 3 – 9: This is the same statement as before.

Line 10: I want to place the results, data, into the Table when the statement is executed.

After executing the statement I can use the following to show the data in the Table:

01 SELECT * FROM QTEMP.OUTPUT

Which returned the following result:

SPLFNAME JOBNAME FILENBR RTNCODE --------- ----------------------- ------- ------- QPQUPRFIL 133240/SIMON/QPADEV0001 7 1

I then took that statement and placed it within a RPG program:

01 **free 02 exec sql 03 CREATE TABLE QTEMP.OUTPUT 04 (SPLFNAME,JOBNAME,FILENBR,RTNCODE) AS 05 (SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER, 06 QSYS2.QCMDEXC('DLTSPLF FILE(' || SPOOLED_FILE_NAME || 07 ') JOB(' || JOB_NAME || 08 ') SPLNBR(' || FILE_NUMBER || ')') 09 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 10 WHERE OUTPUT_QUEUE_NAME = 'MYOUTQ' 11 AND OUTPUT_QUEUE_LIBRARY_NAME = 'MYLIB' 12 AND FORM_TYPE = 'FORM_1' 13 AND DATE(CREATE_TIMESTAMP) < CURRENT_TIMESTAMP - 14 DAYS 14 AND STATUS NOT IN ('SAVED','HELD')) 15 WITH DATA ; 16 *inlr = *on ;

Lines 3 – 15: Are the same as the previous statement.

After compiling this program, I called it, and checked the contents of the OUTPUT Table using the same statement as I did before. The result is:

SPLFNAME JOBNAME FILENBR RTNCODE --------- ----------------------- ------- ------- QPQUPRFIL 133240/SIMON/QPADEV0001 8 1

As you see deleting spool files for something that is not a parameter in DELETE_OLD_SPOOLED_FILES SQL procedure is easy. All I have to is to make sure that my selection of results from OUTPUT_QUEUE_ENTRIES_BASIC is what I truly desire. There is no "Preview", as DELETE_OLD_SPOOLED_FILES has, so I must make sure I test my OUTPUT_QUEUE_ENTRIES_BASIC statement for every possible scenario before I place the QCMDEXC Scalar function in it.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.