Run it in debug to see better diagnostic messages...
Where is your QAQQINI? If in QUSRSYS, then it will be used automatically. If in any other library, you can CHGQRYA QRYOPTLIB(ALIB) to make your job use this options file.
Check Index Advisor in IBM i Navigator Database tooling. Index Advisor may have cached information that could help you resolve this.
Visual Explain should be able to generate a map of its access plan, which can be interesting and informative, but may not help you as much as you might think...
Hth,
-Eric DeLong
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of franz400@xxxxxxxxxxxx
Sent: Tuesday, July 26, 2011 12:16 PM
To: midrange-l@xxxxxxxxxxxx
Subject: sql not using existing indexes v6r1
Select acct,chk#,chkdt, chkseq, svcfrom,svcto from checkhist
where acct=8111222 order by svcfrom,svcto,chk#
File has 10 million + records
There are derived indexes, but non-derived with acct as 1st key field.
This account had 45 records
Executed a table scan
There is a QAQQINI in libl, left from V5R4 with Ignore_Derived_Index = *Yes
I had expected it would see the few records with this acct, build a temp table.
Is there something in the select stmt to avoid this behavior?
Jim Franz
--
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
