Re: Query Optimizer selection incorrect access path

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Either
1) You've found a bug
2) What you think you are doing and/or what is happening isn't what you
think..

The only reason the QE would use a select/omit logical is that it was
specified in the query or when IGNOR_DERIVED_INDEX=*NO and the select/omit
criteria matched the WHERE clause of the query.

Regardless of rather or not a select/omit logical is used, the results of
the query should remain the same.

If that's really not the case, you should open a PMR with IBM.  But I'd
take a very hard look at your query and the output of Visual Explain first.

It sounds like to me that you might be getting tripped up by a duplicate
object name in your library list; you're not reading the file you think you
are.

Charles


On Mon, Nov 5, 2012 at 2:25 PM, Robert Clay <zreclay@xxxxxxxxx> wrote:

> This is my first post and I'm not sure that this is the correct forum.
>
> My question is for a 6.1.0 system with mostly up-to-date PTFs including
> Groups.
>
> I'm trying to join a data table with two additional master tables. The
> issue is that Visual Explain says that the optimizer is selecting an
> access path for one of the master tables that is an old (but still
> utilized) select/omit logical file that uses the master file that I'm
> after as a join to other master files.  Using it is illogical because
> quite a few of the rows from the master file that I need are excluded by
> definition.
>
> My understanding was that at version 6.1, the options in the query
> option file were changed to default *YES for the IGNORE_DERIVED_INDEX
> value .  I have this confirmed from IBM in a PMR.
>
> Yet, I have also tried it with a QAQQINI file that specifically states
> IGNORE_DERIVED_INDEX value = '*YES' but still no joy.
>
> I do not want this SQL statement to utilize this particular LF because
> there will be rows with no matches due to the nature of the select/omit
> values in it.
>
> Does anyone have a clue on how to correct this?
>
> Thanks in advance,
> Robert Clay
>
> --
> "Contrariwise, if it was so, it might be; and if it were so, it would
> be; but as it isn't, it ain't. That's logic."--Tweedledee
> --
> 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.