To be DETERMINISTIC or NOT DETERMINISTIC

[Mercury]

My external scalar function is DETERMINISTIC.

From the SQL Redbook DB2 Universal Database for iSeries SQL Reference, that means that the function will always return the same result from successive invocations with identical input arguments. That likely infers a function cache and that the function is not carried out again on successive calls with same args. Okay so,

My function is called today and returns some value. The day after it is called again with identical args. Is the function carried out again the day after ?

For example :

Function "FUNCTION1" is DETERMINISTIC and uses the current date, to get a previous month date.

I call it today and as a result I get say Sep 13 2008. So far so good.

I call it again the day after. What do I get ? Sep 13 or Sep 14 ?

Also can the results change if the function is called from different sql statements the same day and the day after ?

When is the cache cleared ?

Hopefully this post makes sense.

If anybody can shed some light on this, I'd appreciate it.

Thanks.

[B.Hauser]

Re: To be DETERMINISTIC or NOT DETERMINISTIC

Hi,

My function is called today and returns some value. The day after it is called again with identical args. Is the function carried out again the day after ?

The function gets executed as soon as any parameter argument will change.
Assumed you call your function 100x before midnight and always pass Current_Date, it is only executed once. As soon as midnight is passed and your function is called with current_date again, it will be executed to determine the new value. For the rest of the day only this value will be returned.

Birgitta

[Mercury]

Re: To be DETERMINISTIC or NOT DETERMINISTIC

Ok. Thanks for your fast reply. However what's going on if Current_Date is just used in the function and NOT passed as a parameter ? Does it change the day after or not ?

[B.Hauser]

Re: To be DETERMINISTIC or NOT DETERMINISTIC

Does it change the day after or not ?

As long as you call the procedure/function with the same parameter values, the procedure/function does not gets executed again, because only the parameter values are cached and compared.

In this way it is up to you, to decide if Deterministic can be used or not.

Birgitta

[Mercury]

Re: To be DETERMINISTIC or NOT DETERMINISTIC

Many thanks.