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

[Rob Berendt <robertowenberendt@xxxxxxxxx>]

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;