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;