RE: RPG/SQL indexing...

David,

I ran into this same problem quite some time ago and using ALWCPYDTA *YES 
solved it for me as well. The IBM SQL books go into great detail about why the 
optimizer does what it does. They are well worth the time to read through.

Matt

-----Original Message-----
From: dmosley@dancik.com [mailto:dmosley@dancik.com]
Sent: Friday, October 18, 2002 1:35 PM
To: rpg400-l@midrange.com
Subject: Re: RPG/SQL indexing...



Wow, OK sorry for being viege..

Well, I'm doing an SELECT * FROM ITEMFILE WHERE field1 LIKE '%VALUE%'  or
field2 LIKE '%VALUE%'  ORDER BY field2.
And I do have a logical with FIELD2 as the single key, over "ITEMFILE".
After viewing the Optimizer's Debug Messages, I could see that a Temp
Access Path was built over "FIELD2".
When viewing the "Consideration" details, the reason code for not using the
logical, was "4", which states.
       4 - The cost to use this access path, as determined by the
optimizer, was higher than the cost associated with the chosen access
method.

Which doesn't make sense.
But, to add to my mystery, is that I have this SQL running on two identical
systems.   One is picks up the logical, the other doesn't.

Now, what I did try was changing the ALWCPYDTA parameter from *OPTIMIZE to
*YES,  and it then picked up the logical correctly.

So, now I'm torn between the ALWCPYDTA parameter value.

Thanks for the reply, Bruce

David L. Mosley, Jr.
Systems Analyst
2000 CentreGreen Way
Suite 250
Cary, NC 27513



                    "R. Bruce
                    Hoffman, Jr."            To:     <rpg400-l@midrange.com>
                    <rbruceh@attgloba        cc:
                    l.net>                   Subject:     Re: RPG/SQL 
indexing...
                    Sent by:
                    rpg400-l-admin@mi
                    drange.com


                    10/18/02 01:23 PM
                    Please respond to
                    rpg400-l






----- Original Message -----
From: <dmosley@dancik.com>
To: <rpg400-l@midrange.com>
Sent: Friday, October 18, 2002 12:57 PM
Subject: RPG/SQL indexing...


> I've created a Dynamic SQL, and when executed, I can see that it creating
> an index, that already exists..
> Would anyone know why this is happening, and how I can get it to
> acknowledge the logical file with the index that it creates.


Why? Of course. The optimizer said don't use it.

For the details, well, we need a lot more information, what is the AP you
think it should use _really_ look like? What's the statement? Not a part of
it, the whole thing. What are the selection fields? Is there an order by? A
join? What makes you think it should use your index? What other indexes are
there? What's the data in the file look like? Whats the cardinality of the
fields in the select? etc, etc.



===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - iSeries Administrator
 -- IBM Certified Specialist - RPG IV Developer

"If you pick up a starving dog and make him prosperous,
  he will not bite you. This is the principal difference
  between a dog and a man."

    - Mark Twain

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.