Selectively Deleting OS/400 Spool Files
November 30, 2005 Hey, Joe:
My users insist on saving labels and printouts after they are printed. And the worst part is that I can’t just clear the output queues containing these spooled files because the company is asking me to keep the most recent spooled files for 30 days. Do you have any idea how I can selectively delete spooled files in an output queue that are more than 30 days old while leaving more recent printouts intact?
While i5/OS and OS/400 provide options for automatically deleting system joblogs and other system output after a set number of days, there is no option for automatically deleting spooled file output stored in non-IBM output queues. To remedy this failing, I looked at the options and I came up with a quick CL program for automatically putting spooled file attribute information into a work file and then using that file to selectively delete spooled files in an output queue that meet a certain criteria. Here’s how it works.
The key to selectively deleting spooled files is to generate a list of the spooled files included in that output queue and then to copy that information into an OS/400 physical file for automated deletion. To generate my work physical file, I first ran the following Work with Output Queue command (WRKOUTQ).
This command produces a printout with a spooled file name of QPRTSPLQ. This printout contains all the file attributes of all the spooled files residing in the designated output queue. These attributes include the spooled file name, user name, job name, and job number that produced the file, as well as the spooled file number, user data, file status, and the date and time that the file was produced. Once I had that printout, I then used a Copy Spooled File command (CPYSPLF), such as the one shown below, to port that information into an OS/400 file.
CPYSPLF FILE(QPRTSPLQ) TOFILE(LIBRARY_NAME/WRKOUTQPRT)
Although there is no OS/400 file that provides a physical file layout for the WRKOUTQPRT file that I am porting this information to, I created my own layout that codifies the information for each spooled file contained in the QPRTSPLQ report. Here is the layout for the WRKOUTQPRT file used in this article.
A R QPRTSPLQ A FILL0 1A TEXT('FILLER') A SPLFIL 10A TEXT('FILE NAME') A FILL1 1A TEXT('FILLER') A USER 10A TEXT('USER NAME') A FILL2 1A TEXT('FILLER') A USRDTA 10A TEXT('USER DATA') A FILL3 2A TEXT('FILLER') A STATUS 5A TEXT('STATUS') A FILL4 1A TEXT('FILLER') A PAGES 5A TEXT('# OF PAGES') A FILL5 1A TEXT('FILLER') A COPIES 5A TEXT('# OF COPIES') A FILL6 2A TEXT('FILLER') A FRMTYP 10A TEXT('FORM TYPE') A FILL7 1A TEXT('FILLER') A PTY 2A TEXT('PRIORITY') A FILL8 5A TEXT('FILLER') A FILNUM 6A TEXT('FILE NUMBER') A FILL9 5A TEXT('FILLER') A JOB 10A TEXT('JOB NAME') A FILL10 1A TEXT('FILLER') A JOBNUM 6A TEXT('JOB NUMBER') A FILL11 1A TEXT('FILLER') A JOBDTE 8A TEXT('JOB DATE') A FILL12 22A TEXT('FILLER')
Once I worked through the mechanics of creating a spooled file information file and compiled the DDS for the WRKOUTQPRT file, I could then create a CL program that creates and reads through that file and automatically deletes any spool files that meet my criteria. Here’s the code I used to create the program.
PGM PARM(&OUTQNAME &OUTQLIB) DCL VAR(&OUTQNAME) TYPE(*CHAR) LEN(10) DCL VAR(&OUTQLIB) TYPE(*CHAR) LEN(10) DCL VAR(&COUNTER) TYPE(*DEC) LEN(15 5) VALUE(1) DCL VAR(&WORK) TYPE(*CHAR) LEN(1) VALUE('0') DCL VAR(&WORK1) TYPE(*CHAR) LEN(1) VALUE('0') DCL VAR(&ZERO) TYPE(*CHAR) LEN(1) VALUE('0') DCL VAR(&COPIES1) TYPE(*CHAR) LEN(5) VALUE(' ') DCL VAR(&FILNUM1) TYPE(*CHAR) LEN(6) VALUE(' ') DCL VAR(&DELETE) TYPE(*CHAR) LEN(1) DCLF FILE(WRKOUTQPRT) CHKOBJ OBJ(QTEMP/WRKOUTQPRT) OBJTYPE(*FILE) MONMSG MSGID(CPF9801) EXEC(CRTDUPOBJ + OBJ(WRKOUTQPRT) FROMLIB(QGPL) + OBJTYPE(*FILE) TOLIB(QTEMP)) OVRDBF FILE(WRKOUTQPRT) TOFILE(QTEMP/WRKOUTQPRT) + MBR(*FIRST) WRKOUTQ OUTQ(&OUTQLIB/&OUTQNAME) OUTPUT(*PRINT) CPYSPLF FILE(QPRTSPLQ) TOFILE(QTEMP/WRKOUTQPRT) + SPLNBR(*LAST) GETIT: RCVF MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(ENDPGM)) CHGVAR VAR(&COPIES1) VALUE(' ') CHGVAR VAR(&FILNUM1) VALUE(' ') CHGVAR VAR(&COUNTER) VALUE(0) COUNT: IF COND(&COUNTER *LT 6) THEN(DO) CHGVAR VAR(&COUNTER) VALUE(&COUNTER + 1) IF COND(&COUNTER *LT 6) THEN(DO) CHGVAR VAR(&WORK) VALUE(%SST(&COPIES &COUNTER 1)) IF COND(&WORK *EQ ' ') THEN(CHGVAR + VAR(&COPIES1) VALUE(&COPIES1 *TCAT &ZERO)) ELSE CMD(CHGVAR VAR(&COPIES1) VALUE(&COPIES1 + *TCAT &WORK)) ENDDO CHGVAR VAR(&WORK1) VALUE(%SST(&FILNUM &COUNTER 1)) IF COND(&WORK1 *EQ ' ') THEN(CHGVAR + VAR(&FILNUM1) VALUE(&FILNUM1 *TCAT &ZERO)) ELSE CMD(CHGVAR VAR(&FILNUM1) VALUE(&FILNUM1 + *TCAT &WORK1)) GOTO CMDLBL(COUNT) ENDDO IF COND(&COPIES1 *LE '00000') THEN(GOTO + CMDLBL(GETIT)) IF COND(&COPIES1 *GE '99999') THEN(GOTO + CMDLBL(GETIT)) CHGVAR VAR(&DELETE) VALUE(' ') /* INSERT LOGIC TO SET THE DELETE FLAG ON HERE. WHEN A + SPOOLED FILE MEETS THE DELETION CRITERIA, SET THE &DELETE + VARIABLE TO 'Y' */ IF COND(&DELETE *EQ 'Y') THEN(DO) DLTSPLF FILE(&SPLFIL) JOB(&JOBNUM/&USER/&JOB) + SPLNBR(&FILNUM1) ENDDO GOTO CMDLBL(GETIT) ENDPGM: DLTOVR FILE(WRKOUTQPRT) DLTF FILE(QTEMP/WRKOUTQPRT) ENDPGM
Here’s how the code works.
The program is called and submitted to batch with two parameters specifying the name and library that you want to selectively delete spooled output files from (the &OUTQNAME and &OUTQLIB variables).
I declare my variables and I also use the Declare File command (DCLF) to declare that the program is going to use the WRKOUTQPRT file shown above as its input file.
Using the Check Object command (CHKOBJ), I check to see if a copy of the WRKOUTQPRT file already resides in the job’s temporary library, QTEMP. If the file is not there, I use the Create Duplicate Object command (CRTDUPOBJ) to create a copy of WRKOUTQPRT in QTEMP. Then I use the Override with Database File command (OVRDBF) to redirect any calls to the WRKOUTQPRT file to the version of the file that is now residing in my temporary library. I am using the QTEMP version of the file so that I can simultaneously run the program in two job queues without creating a conflict as two jobs try to lock the WRKOUTQPRT file at the same time.
I next populate the WRKOUTQPRT file with spooled file information from my designated job queue by using the WRKOUTQ and CPYSPLF commands as I explained above.
At the GETIT: label, the program enters the first of two loops. The GETIT: loop uses the Receive File command (RCVF) to read one record at a time from the WRKOUTQPRT file. It then performs all the processing to determine if the spooled file represented by that record should be deleted.
Inside the GETIT: loop, there is a second loop that preps two file fields for further processing. Starting at the loop defined by the COUNT: label, the program populates two work variables called &COPIES1 and &FILNUM1 with variations of the same values that are contained in the number of copies field (&COPIES) and the spooled file number fields (&FILNUM) from the WRKOUTQPRT record. The reason we have to make copies of these fields is that, because they originated from a WRKOUTQ printout, these numeric fields had their leading zeroes suppressed in the original printout. In order to again use these fields as numeric values in the program, we first have to zero-fill both fields from the left. And that’s what the loop designated by the COUNT: label in the program does.
At the end of the COUNT: loop, I then use two IF commands (IF) to determine whether or not the record we are processing is a data record containing information about a spooled file in the output queue or whether it is a record containing a header or a blank line from the report it was copied from. I do this by checking to see if the information in the zero-filled &COPIES1 field (number of copies to be printed of the spooled file) is a numeric. If it’s numeric (a value between ‘00000’ and ‘99999’), I let the record through for deletion consideration. If it’s not numeric, I send the program back to the top of the GETIT: loop to retrieve the next record for processing.
The next thing you’ll notice about the code is that I have added a comment designating where you would put the logic to set the delete flag on (designated by the &DELETE variable being set to ‘Y’). For your purposes, you would enter whatever code or program call that would perform your date arithmetic to determine whether &DELETE should be set to ‘Y’. Date arithmetic in CL is its own adventure and that code could easily take up a good part, if not all, of this article. Date arithmetic has also been covered in IT Jungle before in an excellent article, complete with code, called Date Handling in CL Procedures, written by Ted Holt. In Ted’s article, you can get plenty of ideas for how to handle the date manipulation in your program. Also, Bruce Guetzkow has just done another excellent article on date handling issues in this very edition of Four Hundred Guru.
If you wanted to, you could also set the deletion criteria according to a different standard than spooled files that are over 30 days old. My program can easily be modified to delete any spooled files in an output queue that have a status of ‘SAV’, that have a certain form type, or that were produced by a specific user or a specific job. So you have some flexibility in how you want to delete the spooled files.
After determining if the record represents a legitimate spooled file and whether it should be deleted, the Delete Spooled File command (DLTSPLF) will delete it as long as the &DELETE variable is set to ‘Y’.
After the program finishes processing all the records in the WRKOUTQPRT file, it goes to the ENDPGM: label where it performs end of programming housekeeping. This includes deleting the WRKOUTQPRT file override and deleting the QTEMP/WRKOUTQPRT file. And then the program ends.
Hopefully, this program will help you manage your spool files. It’s a home-grown solution, but I think it will effectively solve your problem.