Re: Calling CL program to delete .ZIP file in IFS as part of an SQL Delete

Charles Wilt wrote:

<<SNIP>>

One of the other developers came to me and asked if there was a
way to call a CL program that will delete a .ZIP file with a
naming convention that includes the date for any dates being
deleted from the table. Example: MY_ARCHIVE_yyyyy-mm-dd.ZIP

The developer already had a CL program that when passed the date,
would delete the .ZIP.

My first thought, sure we can create a user defined function to
call the CL during the execution of the SQL statement...

I had used SQL UDF many times with success, to implement various maintenance activities.

delete from mytable
where datefld <= CURRENT_DATE - 1 month
and Delete_Zip_File(char(datefld,ISO)) = '0';

Anybody see a problem with this? (hint consider non short circuit
evaluations ;)

I have never used a function in selection, in the WHERE clause, to implement the desired EXTERNAL ACTION. I have only used such a function for the actual selected rows; i.e. SELECT UDFname().

AFaIK there is no means to ensure that a function would not be called in order to evaluate the predicate for every row; i.e. in the case of deleting a file, I was never confident that any additional selection within the same statement would preclude /illogical/ invocations of the function.

Then I thought well we'll just run two statements:
with tbl as (select distinct datefld
from mytable
where datefld <= CURRENT_DATE - 1 month
)
select Delete_Zip_File(char(datefld,ISO))
from tbl;

delete from mytable
where datefld <= CURRENT_DATE - 1 month;


But the compiler doesn't like me doing a SELECT instead of a
SELECT INTO...

Of course same reason any standalone SELECT would not be allowed, but I suspect you were aware of that. As for resolution, the SELECT can be part of a CREATE TABLE or INSERT INTO, or to effect just one row for a VALUES INTO just make the statement select the SUM of the function result against each selected row.

Anyway, the developer is looking at just using RPG RLA to delete
the rows, calling the CL program normally when the date changes.

Nothing wrong with that. However why call a CLP to invoke a CL command which [effectively] just calls the API to effect the remove\delete? Just invoke the API to delete the file.

But I thought it's an interesting issue and I figured I thought
it out here to see if anybody had any better ideas.

If CL requests are acceptable or even desirable... I had also often used the SQL to generate a CL or REXX script. The generated list of CL command strings could be reviewed first, and the joblog from running them as a script or as a compiled CLP, shows any error for each invocation.

Regards, Chuck