Re: SQL UDF

It's actually using an index..


Message . . . . :   All access paths were considered for file F0005.
Cause . . . . . :   The OS/400 Query optimizer considered all access paths
  built over member F0005 of file F0005 in library PRDCOM736.
    The list below shows the access paths considered. If file F0005 in
library
  PRDCOM736 is a logical file then the access paths specified are actually
  built over member F0005 of physical file F0005 in library PRDCOM736.
    Following each access path name in the list is a reason code which
  explains why the access path was not used.


 A reason code of 0 indicates
  that the access path was used to implement the query.

    PRDCOM736/F0005  0,


 PRDCOM736/F0005L0  4, PRDCOM736/F0005LA  4,
  PRDCOM736/F0005L2  4, PRDCOM736/F0005L1  4.
    The reason codes and their meanings follow:


"Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in
message
news:F520B5C51DB10041B239BC06383A7EDC014C437F@xxxxxxxxxxxxxxxxxxxxxxxxxx
> Wes,
>
> If you want to cut down the time it takes, you need to optimize your
> SQL.
>
> Specifically:
> SELECT DRDL01
> INTO x_Des
> FROM F0005
> WHERE DRSY = x_SY
> and DRRT= x_RT
> and LTRIM(DRKY) = ltrim(rtrim(x_KY))
>
>
> The 'and LTRIM(DRKY) = ltrim(rtrim(x_KY))' line  is going to cause
> problems.  I would bet that you look at the query optimizer messages,
> you'll find out that an index is not being used in your query.
>
> If the line was 'and DRKY = ltrim(rtrim(x_KY))' then you'd be ok.  But
> the LTRIM on the table field usually forces the query optimizer to do a
> full table scan.
>
>
> HTH,
>
>
> Charles Wilt
> --
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On
Behalf Of Wes
> Sent: Friday, October 21, 2005 12:00 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: 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@xxxxxxxxxxx
> e.or
> g
> >
> [mailto:midrange-l-bounces+cwilt=meaa.mea.com-Zwy7GipZuJhWk0Htik3J/w@pub
> lic.
> 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.
> >
> >
>
>
>
> -- 
> 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.
>
>