×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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.
midrange.com
