Re: How to find the temporary index being used

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Greg said "after an IPL, the process is very slow for bit. "

That definitely points to a MTI.

Greg, once the process is "fast" again, take a look at the output of Visual
Explain.  It will call out any MTIs it is using.
You'd want to build that index permanently to prevent slowdowns after an
IPL.

Charles



On Tue, Mar 19, 2024 at 7:48 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> If something slows down it cannot be temporary Indexes (MTI = Maintained
> Temporary Index).
> MTIs are not created before they are needed.
> Also a MTI is only generated if there is no other way to execute the query
> (for everything Index Advises are stored).
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
> Modernization – Education – Consulting on IBM i
> Database and Software Architect
> IBM Champion since 2020
>
> "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!"
> "Train people well enough so they can leave, treat them well enough so they
> don't want to. " (Richard Branson)
> "Learning is experience … everything else is only information!" (Albert
> Einstein)
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg
> Wilburn
> Sent: Tuesday, 19 March 2024 14:41
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: How to find the temporary index being used
>
> I have an application that uses quite a few SQL statements... after an IPL,
> the process is very slow for bit.  So I'm guessing the database is building
> a temporary index.
>
> The question I have is how to find the one it is building.
>
> I've tried running the SQL statements in RSS with Explain, but some do
> inserts.
> I've looked through Index Advisor for the libraries involved and can't seem
> to put my finger on it.
>
> What am I missing?
>
> TIA,
> Greg
> [Logo]<https://www.totalbizfulfillment.com/>    Greg Wilburn
> Director of IT
> 301.895.3792 ext. 1231
> 301.895.3895 direct
> gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
> 1 Corporate Dr
> Grantsville, MD 21536
> www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.