Re: Advised index not used but still advised -- MIDRANGE-L

Rick,

You've always got access to the statement.

If you know which job was servicing the request, and know that the
request was just issued, there's a "Show Last SQL" statement option
you can use from iNav.

Otherwise, there's the SQL Plan cache or as a last resort, start an SQL monitor.

Once you have the statement displayed, Visual Explain will tell you
all about it including which query engine was used and why it didn't
use the indexes available.

HTH,
Charles

On Mon, Sep 14, 2009 at 11:59 AM, <Rick.Chevalier@xxxxxxxxxxxxxxx> wrote:

Birgitta,

Is there a way to determine which engine was used? Remember, I don't have access to the statement so I can't use debug.

Rick

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Monday, September 14, 2009 10:32 AM
To: 'Midrange Systems Technical Discussion'
Subject: AW: Advised index not used but still advised

CQE may time out and CQE startes searching through the indexes/DDS described logical files beginning with the last logical file index created.
SQE presorts the logical files and indexes according the join criteria and where clauses (and Group by and order By Conditions). The search ends, as soon as an index is more expensive than the previous.

It would be interesting which query engine did the advice. I assume it was the CQE which only works with estimated values and does not interrogate the statistics.
Estimated values are not real values, that's why you may get an advice, but created index is not used.

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 Charles Wilt
Gesendet: Monday, 14. September 2009 16:26
An: Midrange Systems Technical Discussion
Betreff: Re: Advised index not used but still advised

Rick,

Are there lots of indexes/logical files? The SQE may time out and not look at every possible index.

Are there Select/Omit logicals? What's the QAQQINI setting for IGNORE_DERIVED_INDEXES?

Charles

On Mon, Sep 14, 2009 at 10:17 AM, <Rick.Chevalier@xxxxxxxxxxxxxxx> wrote:

Last week I created seven indexes over one of our tables that were
advised

by the index advisor. I got the information from system table SYSIXADV and built the indexes with the key columns advised and installed them to production Friday 9/12. Querying SYSIXADV this morning it shows a last advised date of Saturday 9/13. I have compared the advised key columns and they match exactly to the indexes I installed on Friday. The indexes were created with SQL so the advisor should be happy about that.

I don't have access to the query because it originates outside of the

iSeries from our data warehouse group. The same query is run every night.
The index is requested for record selection. I know there is no guarantee that the optimizer will use the index. I am wondering what would cause the system to keep advising these indexes when they already exist?

Rick Chevalier
IT Software Solutions - Loan Servicing
[http://www.americredit.com/images/acf_email_sig.gif]
817-525-7178 (w)
rick.chevalier@xxxxxxxxxxxxxxx<mailto:rick.chevalier@xxxxxxxxxxxxxxx>

________________________________
Privileged and Confidential. This e-mail, and any attachments there
to, is

intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.

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

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

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

Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.
--
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.