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



Luis

Yeah, I'm using *LANGIDSHR in one of our products to get case-insensitive searching - it does need a full set of indexes behind it, though. I was confused when looking at Visual Explain, that it went to CQE because "Translation required", even when I'd made all the files that participated in a JOIN to have shared-weight indexes. Then I asked an IBM DB expert, and he said that use of non-*HEX sort sequences automatically forced CQE, even if indexes matched.

So one has to get to v6r1, and maybe v5r4 - I forget right now.

Back to work!
Vern

Luis Rodriguez wrote:
Vern,

Thanks for the info. We are at V5R3 and I seemed to remember an issue
with *LANGIDSHR and CQE, so that is why I mentioned it (seeing that
the OP's SQL sentence had JOINs and selections with alpha fields).

Also, thanks for the link to the builder. It works ok with V5R3...

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries



On Thu, Nov 12, 2009 at 12:09 PM, Vern Hamberg <vhamberg@xxxxxxxxxxx> wrote:
Derived index is a select/omit index

*LANGIDSHR is not related to this - it is a separate issue - at V6R1 and
maybe V5R4, this is now handled by SQE. Before that, even if you had
indexes in both tables being JOINed that used *LANGIDSHR, it went to
CQE, just because *LANGIDSHR was specified somewhere.

Shared sort sequence is fine - no reason not to use it - just have all
the indexes you need to support it.

Vern

Luis Rodriguez wrote:
Davi
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries

1. AFAIK, the recommend procedure is to leave QSYS's QAQQINI intact
and use it in another lib.
2. CHGQRYA

. IIRC, derived key means that the key was the result of an
expression. Sometimes it depends (I think) of the sort sequence either
of your files and in your STRSQL command (SRTSEQ). Again, IIRC, avoid
using *LANGIDSHR.

Regards,

Luis Rodriguez

On Thu, Nov 12, 2009 at 9:31 AM, David FOXWELL <David.FOXWELL@xxxxxxxxx> wrote:

All,

Having some performance problems :

Visual explain says CQE used because of derived key or select/omit.
Got rid of all select/omit LFs.
Created all advised indexes.

In STRSQL I get the message in the log <impossible to extract the request options file>

QAQQINI only exists in QSYS. The message says to create the file in QUSRSYS or indicate where to find it.

1. I can't copy from QSYS because of authority.
2. How do I indicate another library?
3. Does not having the file result in IGNORE_DERIVED_INDEX *yes or *no ?

Here's my request :

SELECT * FROM
clients join clientaddress on clients.client_id = clientaddress.client_id
Join
addresses on clientaddress.address_id=addresses.address_id

WHERE avil like 'PARIS%'


Could someone explain exactly what is meant by <derived> index?

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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.