RE: Query Optimizer selection incorrect access path

["Stone, Joel" <Joel.Stone@xxxxxxxxxx>]

You may be looking for a solution to a problem that doesn't exist.

What is the problem?  It was not stated.  

Is the query taking longer than expected?  Are rows being omitted by mistake?  Or are you simply anticipating that rows may be omitted by mistake (without actually testing this)?

Since the SQL engine is used so heavily by so many, it is unlikely that there is a problem.

DB2/400 shares views and LFs in interesting and hopefully genius ways.  It may even be reporting a logical incorrectly, and then using the underlying physical file??

Force what you think is a no-match selection and see if there really is a problem.  I am guessing the records omitted in the LF will appear in the SQL result.




-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Clay
Sent: Monday, November 05, 2012 1:25 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Query Optimizer selection incorrect access path

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