× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.