Re: Using sql to delete files in a lib?

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.