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

So true Rob:


* I will warn you, once you get started with stored procedures you'll
start wondering why you still write CL*

So I created a wrapper where we still use CL while transforming code to
SQL-PL. We simply need commands to invoke ( and return values ) to CL.

This open source git project is about giving SQL procedures an OS command
so you can leverage new procedures in the CL programming language from the
command line without creating all the boilerplate - more sophisticated than
just RUNSQL

SQL procedures "just" need an OS command and integration is perfect.

Rob ( and others with the same mindset) - this i for you:

https://github.com/NielsLiisberg/command-for-SQL

Let me hear what you think




On Thu, Oct 3, 2024 at 1:43 PM Rob Berendt <robertowenberendt@xxxxxxxxx>
wrote:

Yes. But it would require rewriting that as a stored procedure with a
cursor. I could send you a sample. I will warn you, once you get started
with stored procedures you'll start wondering why you still write CL. You
strike me as someone who has learned multiple languages over the years and
could pick up SPL quite easily.

However, prior to IBM providing QCMDEXC as a function, some of us wrote our
own QCMDEXC as a function. I can't find my original source but here is a
snippet from Generate SQL
CREATE FUNCTION ROUTINES/QCMDEXC (
CMD *VARCHAR*(*32702*) )
RETURNS *CHAR*(*256*)
LANGUAGE SQL
SPECIFIC ROUTINES/QCMDEXC
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
NOT FENCED
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *YES ,
COMMIT = *NONE ,
DECRESULT = (*31*, *31*, *0**0*) ,
DFTRDBCOL = *NONE ,
DLYPRP = *NO ,
DYNDFTCOL = *NO ,
DYNUSRPRF = **USER* ,
RDBCNNMTH = *RUW ,
SRTSEQ = **HEX*
BEGIN DECLARE RETCMD *CHAR* ( *256* ) ; DECLARE *SQLCODE* *INTEGER* ;
DECLARE CONTINUE HANDLER FOR *SQLEXCEPTION* BEGIN SET RETCMD =
'ERROR SQLCODE=' || *TRIM* ( *CAST* ( *SQLCODE* AS *CHAR* ( *10* ) ) ) ||
' ' || CMD ; END ; BEGIN DECLARE CMDLEN *DEC* ( *15* , *5* ) ; SET
RETCMD = CMD ; SET CMDLEN = *LENGTH* ( *TRIM* ( CMD ) ) ; CALL
QCMDEXC ( CMD , CMDLEN ) ; END ; RETURN ( RETCMD ) ; END ;

On Wed, Oct 2, 2024 at 3:28 PM James H. H. Lampert via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

On 10/2/24 11:28 AM, Bryan Dietz wrote:

looking here:
https://www.ibm.com/support/pages/node/1119123

scan for "QCMD"

scalar func = SF99704 Level 13
proc = *base

so you may have to change your SQL until they get a later DB group
installed

Thanks.

Fascinating. Can anybody suggest a way to change

===> select spooled_file_name, job_name, file_number,
'DLTSPLF FILE(' CONCAT SPOOLED_FILE_NAME CONCAT ') JOB('
CONCAT JOB_NAME CONCAT ') SPLNBR(' CONCAT FILE_NUMBER
CONCAT ')' as cmd, qcmdexc('DLTSPLF FILE(' CONCAT
SPOOLED_FILE_NAME CONCAT ') JOB(' CONCAT JOB_NAME
CONCAT ') SPLNBR(' CONCAT FILE_NUMBER CONCAT ')') as
cmd_result from qsys2.OUTPUT_QUEUE_ENTRIES
where job_name like '%/WTCPVERIFY' and
spooled_file_name = 'QPRINT'

so that qcmdexc is called as a procedure, rather than as a function?

--
JHHL

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

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