Re: How to find the temporary index being used

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.