CODEWITHSUNDEEP
db2deterministicudf
Arpit
Arpit

Reputation: 53

Deterministic Functions in DB2

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 ?

Upvotes: 2

Views: 1365

Answers (2)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

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
)

Upvotes: 0

mustaccio
mustaccio

Reputation: 19001

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.

Upvotes: 4

Related Questions