Re: Quick question: mass-delete of spool files?

My 2 cnts:

You could use the already invented wheel ,
namly SYSTOOLS.DELETE_OLD_SPOOLED_FILES .
See here
https://www.rpgpgm.com/2020/06/deleting-spool-files-using-sql.html
and here
https://www.ibm.com/docs/en/i/7.5?topic=services-delete-old-spooled-files-procedure

I use a CL wrapper that receives an age of spool files in days parameter ,
constructs a DELETE_OLDER_THAN => CURRENT_TIMESTAMP – days_Param DAYS

and then execute the whole thing using RUNSQL

Gad

from: Rob Berendt <robertowenberendt@xxxxxxxxx>
subject: Re: Quick question: mass-delete of spool files?

James,
Again I strongly advocate learning Stored Procedure Language or SPL.
Paste the following into Run SQL Scripts for helpful coloring.


CREATE PROCEDURE PURGE_ORDINQ_SPOOL_FILES
LANGUAGE SQL MODIFIES SQL DATA
SPECIFIC PROC000001
SET OPTION DATFMT = *ISO
P1: BEGIN
DECLARE w_SPOOLED_FILE_NAME *CHAR*(*10*);
DECLARE w_JOB_NAME *CHAR*(*28*);
DECLARE w_FILE_NUMBER *INT*;
DECLARE COMMAND *CHAR*(*200*);
*-- Ensure this is large enough.* DECLARE END_TABLE *INT* DEFAULT
*0*;
DECLARE C1 CURSOR FOR

SELECT
SPOOLED_FILE_NAME,
JOB_NAME,
FILE_NUMBER
FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
WHERE USER_NAME='ORDINQ'
AND *DAYS*(*CURRENT_TIMESTAMP*) - *DAYS*(CREATE_TIMESTAMP) >
*35*;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET END_TABLE = *1*;

*-- DECLARE EXIT HANDLER FOR SQLEXCEPTION*
*-- SET DEPT_SALARY = NULL;* OPEN C1;
FETCH C1 INTO w_SPOOLED_FILE_NAME, w_JOB_NAME, w_FILE_NUMBER;

WHILE END_TABLE = *0* DO
SET COMMAND = 'DLTSPLF FILE(' *CONCAT* w_SPOOLED_FILE_NAME
*CONCAT* ') JOB(' *CONCAT* w_JOB_NAME *CONCAT* ') SPLNBR(' *CONCAT*
*TRIM*(
*CHAR*(w_FILE_NUMBER)) *CONCAT* ')';

*-- set command = 'sndmsg msg(' concat x'7D' concat command concat
x'7D' concat ') tousr(rob)';*
CALL QSYS2.QCMDEXC(COMMAND);
FETCH C1 INTO w_SPOOLED_FILE_NAME, w_JOB_NAME, w_FILE_NUMBER;
END WHILE;
CLOSE C1;
END P1
;
CALL ROB.PURGE_ORDINQ_SPOOL_FILES;