Re: SQL query results in CPI432F

Dennis,

Just out of idle curiosity, I did the following tests (running V5R3, with
the latest PTFs):

1.- Created a table, no index, SRTSEQ(*HEX). Ran a query against field F1.
System suggested to create an index for F1
2.- Created (SQL) index for F1. Ran query again. System used index F1.

3. Created another table, this time with SRTSEQ(*LANGIDSHR). Ran the same
query. System suggested (again) to create an index for field F1.
4. Created suggested index. Ran query again. This time, the system told me
that it had examined available indexes, but it couldn't use it due to reason
18 (msgid CPI432D, which states:

<QUOTE/>
18 - The left-most key of the access path matched a field specified for the
selection criteria. However, the sequence table associated with the access
path did not match the sequence table associated with the query. Therefore,
key row positioning could not be performed, making the cost to use this
access path higher than the cost associated with the chosen access method.
</QUOTE>

I'm afraid I'm out of additional ideas about this one. Nevertheless, if you
find the answer to your problem, it would be nice to know about it.

Regards,


Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Mon, Feb 1, 2010 at 10:37 AM, Dennis Lovelady <iseries@xxxxxxxxxxxx>wrote:

What happens if you create a SQL index? Same message? Also, I seem to
remember that sometimes the optimizer "times out" and does not have the
time
to examine every index, so it can advise an already created index (at
least,
this seems to apply to the Index Advisor in Visual Explain)

Same thing happens after:
CREATE INDEX lib/TAPTALTC9
ON lib/TAPTC
(TCER, TCSTGE, TCEN, TCDTH)

(Oddly, it took as long to create this index as it did to create the LF
even
though the LF still existed at the time.)

If this were due to timeout, would the newly-created index appear in the
list of "considered access paths?" (CPI432C) It does in fact. Rejected
for
reason 5.

It was a matter of curiosity - there are plenty of other reasons this
particular SQL will operate inefficiently. Now my interest is in whether
we
should pursue some PTF at V5R3 for this, or it it's expected under certain
(what?) circumstances.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"If living conditions don't stop improving in this country, we're going to
run out of humble beginnings for our great men."
-- Russell P. Askue



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