RE: SQL UDF

Wes,

As I understand it, DETERMINISTIC simply tells the optimizer that it
only has to run the function once for a given set of results.  It in
know way guarantees that it will only run it once.

In any event, is this function really deterministic?  What happens when
a record is added to the table?

My philosophy is that every function that access a table is NOT
DETERMINISTIC.  Only functions that don't access a table, for example a
convert date function, can be DETERMINISTIC.

Perhaps the optimizer feels the same? 


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 
-----Original Message-----
From: midrange-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx
[mailto:midrange-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx] On Behalf Of
Wes
Sent: Wednesday, October 19, 2005 4:02 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL UDF

Hi List,

I have a function that will always return the same result if the input
criteria is the same. After running it and noticing the IO for that file
was
too high, I changed the function to deterministic, hoping it would cache
the
result after the first call. That doesn't seem to be the case...

Here is the function:

CREATE FUNCTION DataWH/CatCodes
(x_SY varCHAR(4),
x_RT varCHAR(2),
x_KY varCHAR(10))
RETURNS varCHAR(40)
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
Begin

DECLARE x_Des varCHAR(50);
DECLARE at_end INT DEFAULT 0;
DECLARE not_found
CONDITION FOR '02000';

DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;

SELECT DRDL01
INTO x_Des
FROM F0005
WHERE DRSY = x_SY
and DRRT= x_RT
and LTRIM(DRKY) = ltrim(rtrim(x_KY));


IF at_end <> 0 THEN
return 'N/A';
else
return ltrim(rtrim(x_KY))||' - '||ltrim(rtrim(x_Des));
END IF;
END

Is there a reason why the table IO still too high?

Thanks, Wes