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

Wups - I was a little to fast,

need to declare the command - and don't cast number to chars like in the
old days:

version 2 .. is tested on my system:


create or *replace* procedure delete_old_spool_files
language sql
modifies sql data
specific PROC000001
set option DATFMT = * ISO
begin
declare command *varchar*(*256*);
for
select
SPOOLED_FILE_NAME,
JOB_NAME,
FILE_NUMBER
from
QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
where
USER_NAME = 'NLI'
and CREATE_TIMESTAMP < *now*() - *3* *days*

do
set COMMAND = 'DLTSPLF FILE(' *concat* SPOOLED_FILE_NAME *concat*
') JOB(' *concat* JOB_NAME *concat* ') SPLNBR(' *concat* *trim*
(FILE_NUMBER) *concat* ')';
call QSYS2.QCMDEXC (COMMAND);
end for;

end;

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

Very nice!
Notice: Something in the thread did the "add asterisks" thing again.

On Mon, Oct 7, 2024 at 9:17 AM Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>
wrote:

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;

--
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.