Deterministic Functions in DB2

Question

I have a situation where I need to make My Scalar Function Deterministic so that its not executed every time when used in the same query. I read and learned that we need to use both

DETERMINISTIC

as well as

NO EXTERNAL ACTION

to make the DETERMINISTIC function work so that its not executed everytime in the query. If I am not wrong ? also that the scope of the deterministic behavior is a single query.

The signature of My UDF is as follows :

create or replace FUNCTION my_udf(myLIST VARCHAR(1000)) 
RETURNS integer
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
< code for making HTTP  servlet call >
END

I am using the following query for testing my_udf() function :

select my_udf('admin1,admin2'),my_udf('admin1,admin2') from sysibm.sysdummy1

But I see that the call to my servlet is being made each time and hence the functions is being executed each time.

I need to know if there is something wrong that I am doing regarding implementation of the DETERMINISTIC functions or having wrong idea about their usage/functionality ?

Answer 1

In addition to what others have said about DETERMINISTIC and NO EXTERNAL ACTION. Perhaps you can solve the issue by nesting the function call?

select x, x
from (
    select my_udf('admin1,admin2') as x
    from sysibm.sysdummy1
)

Answer 2

The query optimizer ultimately decides whether it can "optimize away" calls to a deterministic UDF. However, in your case it doesn't matter, because your function is indeed not deterministic and does have external action.