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