SQL0374N and SQL0628N

I have the following build problem with UDF calling a Stored Procedures the following. The simplified codes does the following:

CREATE FUNCTION MY_UDF(date1 DATE, date2 DATE)
RETURNS DOUBLE
LANGUAGE SQL
EXTERNAL ACTION
BEGIN ATOMIC
DECLARE out_val DOUBLE;
CALL MY_SP(date1, date2, out_val);
RETURN out_val;
END


CREATE PROCEDURE MY_SP(date1 DATE, date2 DATE, out_val DOUBLE)
LANGUAGE SQL
EXTERNAL ACTION
-- logic omitted...
-- Briefly it loops through a set of values over the date range
-- in particular tables. This is done via User Defined Table.
-- Intermediate result are organized and stored in a GLOBAL TEMPORARY
-- TABLE. This information is then further processed using UPDATES.
-- The final result is single double "out_val". Like an average.

MY_SP is working fine.

But when I try to build MY_UDF, I get a SQL0374N which suggest I add in the MODIFIES SQL DATA clause. But when I do that, I get the SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA".

What should we do? We need MY_UDF because it is called by other SP for the scalar result. (FYI - We also call this from our J2EE application.)

What is a possible solution to this?

JJ

chee.seong.tan-at-cslucas.com.sg wrote:

> I have the following build problem with UDF calling a Stored Procedures
> the following. The simplified codes does the following:
>
> CREATE FUNCTION MY_UDF(date1 DATE, date2 DATE)
> RETURNS DOUBLE
> LANGUAGE SQL
> EXTERNAL ACTION
> BEGIN ATOMIC
> DECLARE out_val DOUBLE;
> CALL MY_SP(date1, date2, out_val);
> RETURN out_val;
> END
>
>
> CREATE PROCEDURE MY_SP(date1 DATE, date2 DATE, out_val DOUBLE)
> LANGUAGE SQL
> EXTERNAL ACTION
> -- logic omitted...
> -- Briefly it loops through a set of values over the date range
> -- in particular tables. This is done via User Defined Table.
> -- Intermediate result are organized and stored in a GLOBAL TEMPORARY
> -- TABLE. This information is then further processed using UPDATES.
> -- The final result is single double "out_val". Like an average.
>
> MY_SP is working fine.
>
> But when I try to build MY_UDF, I get a SQL0374N which suggest I add in
> the MODIFIES SQL DATA clause. But when I do that, I get the SQL0628N
> Multiple or conflicting keywords involving the "MODIFIES SQL DATA".
>
> What should we do? We need MY_UDF because it is called by other SP for the
> scalar result. (FYI - We also call this from our J2EE application.)
>
> What is a possible solution to this?

You say that your stored procedure inserts data in a table (a temp table).
Therefore, your procedure is automatically defined as MODIFIES SQL DATA.

UDFs must not be defined with MODIFIES SQL DATA, except for table functions.
Otherwise, you suddenly have side-effects if you call the UDF in a SELECT
statement because such a SELECT starts to modify data.

We don't have enough details on the procedure. But what may be an option is
to hold all necessary data in local variables and do the calculation there.
Alternatively, you can resort to external procedures and manage any
temporary data there. And yet another question is if you could do away
with the UDF and call the procedure directly or from a trigger.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

We got around this problem by doing away with the store procedures and work with just the UDF using temporary table along the line of with t1 (...), t2 (...). In this case it works fine.

However, quite a number of our analytic requires us to "park" intermediate results and combine with other intermediate result. And I am afraid that what works now may not work later. Furthermore we need the access point for logic in a scalar UDF because we use them in other store procedures. Would welcome your suggestion on other approach (and reference to sample codes if possible).

JJ

chee.seong.tan-at-cslucas.com.sg wrote:

> We got around this problem by doing away with the store procedures and
> work with just the UDF using temporary table along the line of with t1
> (...), t2 (...). In this case it works fine.
>
> However, quite a number of our analytic requires us to "park" intermediate
> results and combine with other intermediate result. And I am afraid that
> what works now may not work later.

A specific example showing such a problem would be helpful. Generally: SQL
is a computationally complete programming language, so you should be able
to express anything with it. (Question is if it makes sense or not.)

> Furthermore we need the access point
> for logic in a scalar UDF because we use them in other store procedures.
> Would welcome your suggestion on other approach (and reference to sample
> codes if possible).

You can call a stored procedure from another procedure. So there is no
reason to stick to UDFs in that situation.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany