Re: QAQQINI

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

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