DETERMINISTIC vs NON DETERMINISTIC

Hall Preston

DETERMINISTIC vs NON DETERMINISTIC
We are just starting to code stored procedures and UDFs at our shop. Can
anyone tell me what DETERMINISTIC and NON DETERMINISTIC mean? When should I
use DETERMINISTIC and when should I use NON DETERMINISTIC. I have read the
IBM manuals but I don't complete understand the meanings of these terms or
know which one I should use.

Thanks

Preston Hall
John Deere Credit



Phil Grainger

Re: DETERMINISTIC vs NON DETERMINISTIC
(in response to Hall Preston)
DETERMINISTIC means that when you call a function with a given parameter value, the returned result is always the SAME
NON-DETERMINISTIC means it isn't

eg

a random number generating function will (should) always return a different value, even if you call it repeatedly with the same parameter

so.....

When you CREATE a function you need to TELL DB2 whether it is DETERMINISTIC or NOT - this will have an impact down the line in the way DB2 executes and uses your function

Hope this helps

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



-----Original Message-----
From: Hall Preston [mailto:[login to unmask email]
Sent: Monday, June 09, 2003 7:07 PM
To: [login to unmask email]
Subject: [DB2-L] DETERMINISTIC vs NON DETERMINISTIC


We are just starting to code stored procedures and UDFs at our shop. Can
anyone tell me what DETERMINISTIC and NON DETERMINISTIC mean? When should I
use DETERMINISTIC and when should I use NON DETERMINISTIC. I have read the
IBM manuals but I don't complete understand the meanings of these terms or
know which one I should use.

Thanks

Preston Hall
John Deere Credit






Andy Lankester

Re: DETERMINISTIC vs NON DETERMINISTIC
(in response to Phil Grainger)
I think that DB2 may now or in the future be able to cache the
parameter/result pairs of DETERMINISTIC functions to save function
invocation and execution cycles, whereas this obviously cannot be done
for NONDETERMINISTIC ones.

Andy Lankester
CDB Software

> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email] On Behalf Of Grainger, Phil
> Sent: 10 June 2003 13:23
> To: [login to unmask email]
> Subject: Re: DETERMINISTIC vs NON DETERMINISTIC
>
>
> DETERMINISTIC means that when you call a function with a
> given parameter value, the returned result is always the SAME
> NON-DETERMINISTIC means it isn't
>
> eg
>
> a random number generating function will (should) always
> return a different value, even if you call it repeatedly with
> the same parameter
>
> so.....
>
> When you CREATE a function you need to TELL DB2 whether it is
> DETERMINISTIC or NOT - this will have an impact down the line
> in the way DB2 executes and uses your function
>
> Hope this helps
>
> Phil Grainger
> Computer Associates
> Product Manager, DB2
> Tel: +44 (0)161 928 9334
> Fax: +44 (0)161 941 3775
> Mobile: +44 (0)7970 125 752
> [login to unmask email]
>
>
>
> -----Original Message-----
> From: Hall Preston [mailto:[login to unmask email]
> Sent: Monday, June 09, 2003 7:07 PM
> To: [login to unmask email]
> Subject: [DB2-L] DETERMINISTIC vs NON DETERMINISTIC
>
>
> We are just starting to code stored procedures and UDFs at
> our shop. Can anyone tell me what DETERMINISTIC and NON
> DETERMINISTIC mean? When should I use DETERMINISTIC and when
> should I use NON DETERMINISTIC. I have read the IBM manuals
> but I don't complete understand the meanings of these terms
> or know which one I should use.
>
> Thanks
>
> Preston Hall
> John Deere Credit
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://listserv.ylassoc.com. The owners of the > list can be
>
>
> ================
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://listserv.ylassoc.com. The owners of the > list can be
>
>



James Campbell

Re: DETERMINISTIC vs NON DETERMINISTIC
(in response to Andy Lankester)
Obviously, do not use DETERMINISTIC if the SP/UDF does not return
identical results (including any updates to tables that it makes
internally) on identical input.

I believe, on no evidence, that if you specify NOT DETERMINISTIC
there are circumstances where DB2 might be able to avoid re-invoking
the SP/UDF. The documentation implies that this is in situations
where the UDF is specified in a view.

But ... ah, sigh, misery me,
- I have a UDF, definitely DETERMINISTIC
- I coded: SELECT xxx
FROM (SELECT UDF(constant arguments) xxx
FROM SYSIBM.SYSDUMMY1) A, something
- UDF is invoked for every row in something.

Raised a PMR - after all with constant arguments a deterministic
function can only return a constant result, so why keep invoking it?
The response was "you might have lied [OK, it was actually some
waffle about accidently leaving the word NOT off the definition], we
know better than to trust you [or some more waffle about taking the
safe and sure path to a known result]".

(The suggested technique was to store the result of invoking the UDF
in a DGTT, and using that in the SELECT)

So, all in all, DB2 might actually ignore it. Or perhaps one day
IBM will start trusting its customers to not forget their NOTs.

James Campbell

On 9 Jun 2003 at 13:07, Hall Preston wrote:

> We are just starting to code stored procedures and UDFs at our shop. Can
> anyone tell me what DETERMINISTIC and NON DETERMINISTIC mean? When should I
> use DETERMINISTIC and when should I use NON DETERMINISTIC. I have read the
> IBM manuals but I don't complete understand the meanings of these terms or
> know which one I should use.
>
> Thanks
>
> Preston Hall
> John Deere Credit
>
>
>



[login to unmask email]

Re: DETERMINISTIC vs NON DETERMINISTIC
(in response to James Campbell)
One aspect of not deteministic functions is that you cannot sort by them,
for example

"select col1, col2 from yourtable order by yourfunction(col3)"

will only work if yourfunction is determinstic,
else it will give SQLCODE -583.

You can make a quick test producing the -583 by the (nonsense) statement
"select 'dummy' from sysibm.sysdummy1 order by rand()"
whereas
"select 'dummy' from sysibm.sysdummy1 order by date(current timeatmp - 5
days)"
will work.