RE: RPG/SQL indexing...

["Andrew Lutz" <andrew.lutz@xxxxxxxxxxxx>]

Well, I can tell you now the like with a wildcard on the beginning will
kill you every time.

The system will analyze every single record to determine if it has a
match.  Then index the temporary file.




-----Original Message-----
From: dmosley@dancik.com [mailto:dmosley@dancik.com]
Sent: Friday, October 18, 2002 10:35 AM
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.