Re: SQL UDF

[vhamberg@xxxxxxxxxxx]

I suspect the SELECT statement with the functions in the WHERE clause - these 
almost guarantee a table scan, no matter what, because there is no index that 
can handle expressions. To see what is being used by the optimizer, turn on 
debug in the job and after running a statement with the UDF, check the job log 
for messages from the optimizer. Some will tell you what was done and why, to a 
degree.

HTH
Vern
-------------- Original message -------------- 

> 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. 
>