Re: SQL UDF

Hi Wilt,

I understand and agree with your point. I'm using the function to build a
data extract table for a data warehouse project. When we are using that
function, the table doesn't change. The table has about 67000 rows but it
central for all our user defined values. During the data extract the table
IO for that table is around 5million and it adds about 2hrs processing time
so you can see there are a lot of mutiple reads going on for the same
criteria. I was hoping to reduce the IO to around 2x the table size and
figured deterministic would do the trick.

"Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in
message
news:F520B5C51DB10041B239BC06383A7EDC014C411F@xxxxxxxxxxxxxxxxxxxxxxxxxx
> 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-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxx
g
>
[mailto:midrange-l-bounces+cwilt=meaa.mea.com-Zwy7GipZuJhWk0Htik3J/w@public.
gmane.org] 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
>
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>