It simply uses the access path of the logical file, but not the logical file
itself, i.e. it ignores the select/omit clause.
We run in the same issue long ago. The problem was: All Joined names were
XXYYYYJ00 while the logical files were XXYYYYL00.
When (re)creating all logical files the join files were created first, while
the normal join files were created after. Because access paths were shared,
the logical files (even the uniquely keyed logicals) shared access path with
the joined logical files.
The query optimizer almost always used joined logicals instead of the
logical.
After having recreated the logicals, i.e. logicals first and joined logicals
after, the "right" logicals were used.
To solve the problem, I'd suggest to create an SQL index with the key fields
specified within the join logical file.
If you rerun the query this index should be used.
After delete and recreate your logical file. It should now share access path
with the SQL index.
In this way only a single access path must be maintained when inserting,
updating or deleting any records out of the base table.
As an aside an SQL index has (per default) a page size of 64 K (while the
default page size for a DDS described logical file is 8K), i.e. performance
may be better.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Robert Clay
Gesendet: Monday, 05.11 2012 20:25
An: midrange-l@xxxxxxxxxxxx
Betreff: 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
--
"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.
midrange.com
