Re: Using sql to delete files in a lib? -- MIDRANGE-L

I always forget about the scalar function. Thanks for reminding me.

On Tue, 2021-09-28 at 14:43 +0000, Rob Berendt wrote:
Not only does IBM have a stored procedure for QCMDEXC but now they also have a scalar function for QCMDEXC just so you can do stuff like create the strings in Kevin's example, and then execute them.

Here's an example of it in use:
https://www.ibm.com/docs/en/i/7.4?topic=services-qcmdexc-scalar-function

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Buck Calabro
Sent: Tuesday, September 28, 2021 10:34 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Using sql to delete files in a lib?

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

On 9/28/2021 9:55 AM, frank boke wrote:
Hi Folks, is there any way to do this in sql?

I have files created on 9/2/21 in lib fbtemp and I want to use sql to
delete the files dated 9/2/21. How can I use sql to delete the files based
on the date or must I use qsh? I don't want to use cl if I don't have to
because it'd be easier using sql.

Thanks, Frank

One way is to write a general-purpose SQL User Defined Function.

CREATE FUNCTION EXECUTE_CL_COMMAND (
COMMAND_STRING VARCHAR(4096) )
RETURNS CHAR(1)
LANGUAGE SQL
SPECIFIC NYSLIB1.EXECUTE_CL_COMMAND
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
-- trap errors, return '0' if error occurs
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION , SQLWARNING BEGIN RETURN '0'
; END ;

CALL QCMDEXC ( COMMAND_STRING ) ;
RETURN '1' ;
END ;

Then, using the general idea from Kevin's reply, you build the command
you want to execute. Something like (untested):

SELECT
execute_cl_command('drop table kevin.' concat trim(OBJNAME))
FROM TABLE(QSYS2.OBJECT_STATISTICS('KEVIN', '*FILE'))
where date(objcreated) >= '2010-08-03';

A more robust solution is to write a function that does not execute a
raw CLP, but rather calls a CLP with the same input (the command to
execute). That CLP can do all the MONMSG and logging that you may want
to have in a production quality function.

--
--buck

http://wiki.midrange.com
Your updates make it better!

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto: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<mailto: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<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

[https://www.medtronsoftware.com/img/MedtronMinilogo.bmp]
Kevin Bucknum

Senior Programmer Analyst

MEDDATA / MEDTRON

120 Innwood Drive
Covington LA 70433
Local: 985-893-2550<tel:985-893-2550>
Toll Free: 877-893-2550<tel:877-893-2550>
https://www.medtronsoftware.com

CONFIDENTIALITY NOTICE

This document and any accompanying this email transmission contain confidential information, belonging to the sender that is legally privileged. This information is intended only for the use of the individual or entity named above. The authorized recipient of this information is prohibited from disclosing this information to any other party and is required to destroy the information after its stated need has been fulfilled. If you are not the intended recipient, or the employee of agent responsible to deliver it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of these documents is STRICTLY PROHIBITED. If you have received this email in error, please notify the sender immediately to arrange for return or destruction of these documents.