RE: How to find the temporary index being used

Have you had a look at the MTI_INFO documentation?
https://www.ibm.com/docs/en/i/7.5?topic=services-mti-info-table-function

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 Don
Brown via MIDRANGE-L
Sent: Wednesday, 20 March 2024 02:59
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Don Brown <DBrown@xxxxxxxxxx>
Subject: RE: How to find the temporary index being used

I just ran this query and am trying to understand the results.

The output is indicating that MTI's have been created for the physical file
listed using the key_definition column.

And the reference count is the number of times the MTI has been accessed ?

And I have statuses if VALID and CREATED ?

So is there any rule of thumb as to when an access path should be created, a
reference count in the hundreds or ???.

Thanks

Don





From: <stefan@xxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxxxxxxxx>
Date: 20/03/2024 12:30 AM
Subject: RE: How to find the temporary index being used
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

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.

Or use:

SELECT * FROM TABLE(QSYS2.MTI_INFO())
ORDER BY TABLE_SCHEMA,TABLE_NAME;

or
' SELECT * FROM TABLE(QSYS2.MTI_INFO())
ORDER BY REFERENCE_COUNT desc;

Best regards

Stefan