Deterministic vs Not Deterministic

[Viking]

Years ago, omeone asked a question that is pretty much exactly my question:
http://www.code400.com/forum/forum/i...-deterministic

Birgitta answered the question, but I'm not sure if I completely understand the answer about how long the cached answer is used... her answer almost seems to imply that it's forever until the parameter changes in the call.

I have an SQL UDF that accepts one parameter StoreNumber and uses Current_Date within the function to figure out the rolling 12-month sales for that store. So this week I might call the function with SALES12(123) to get the last 12 months' sales for store 123. Next week, if I make that exact same call for store 123, will I still get the same answer? How long is the cached answer kept until the function finally actually goes out and figures out a new answer? I don't mind getting the same answer back all day long, but I can't get the same answer back days and weeks later. But setting the function to NOT DETERMINISTIC seems to have a noticeable negative impact on performance.

Your thoughts?

[TedHolt]

I would code such a function as not deterministic, even if performance suffered. The integrity of the data is what matters.

[Viking]

Yes, it is currently coded as NOT DETERMINISTIC. However, I still wonder how long the system will keep using the cached answer for functions that are DETERMINISTIC.

[Scott Klement]

I haven't been able to get an answer from IBM on how long it caches these. I think IBM doesn't want to say because they might change it at some point, and they don't want people to rely on how long the time is.

For example, if they say "its reset every time the activation group ends" (hypothetically) and then I write my programs to rely on that behavior... then when they change it to be reset only during IPLs, or even less frequent (again, that's purely hypothetical) it would break my programs. Since they don't want to run into that situation, they don't want to tell you how the caching works. You should just assume that as long as you have "deterministic" it'll cache forever. (Even though I know that's not really the case... it's the safest assumption to make.)

[Jim_IT]

I'll be at LUG in Rochester in a couple of months and I'll ask about this. I've asked informally before but I will ask again in front of the group at the next upcoming meeting.

Jim

[Viking]

Scott, thanks, I thought everyone was just keeping the answer from me! :-)

Jim, thanks, I'll look forward to hearing their answer.

Maybe I'll code these to accept the date as a parameter in addition to the store # rather than having the function use Current_Date internally. That way, I would pass it the current date and it would give me the same answer all day, but be forced to give me a different answer tomorrow. Then I could have it be DETERMINISTIC and likely have better performance all day after the first call.

[JonBoy]

Scott, thanks, I thought everyone was just keeping the answer from me! :-)

Jim, thanks, I'll look forward to hearing their answer.

<snip>

Me too. Last time I got an answer on the topic (many years ago) was that it wasn't actually implemented - just there for compatibility and in case they added it in future. Any time I have tried to test it (admittedly a good few years ago now) I could never get it to produced an "old" result.