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

May I blend in, and suggest my favorite SQL-PL statement the "FOR
statement:

1) you don't need to explicitly declare a cursor ( like in RPG)
2) Your variables are "scoped" in the for loop
3) you don't need to explicitly declare a host variables (because of 2)
( like in RPG)

Here you go with my re-written version:

create procedure PURGE_ORDINQ_SPOOL_FILES
language sql
modifies sql data
specific PROC000001
set option DATFMT = * ISO
begin
for
select
SPOOLED_FILE_NAME,
JOB_NAME,
FILE_NUMBER
from
QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
where
USER_NAME = 'ORDINQ'
and CREATE_TIMESTAMP < *now*() - *35* *days*

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* ')';
call QSYS2.QCMDEXC (COMMAND);
end for;

end;



On Mon, Oct 7, 2024 at 1:21 PM Rob Berendt <robertowenberendt@xxxxxxxxx>
wrote:

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;

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.