All Db2 for i / SQL index advises are stored in the SYSIXADV table in the QSYS2 library.
With ACS - ACS Schemas, you can have a look in the advised indexes on Database level, on Schema level and on Table level.
Index Advises can be deleted (if you never had a look at the index adviser, it will include all advises since the index advisor was introduced in release V5R2M0).
The best thing would be to clear the index advisor and then wait for a few weeks.
In ACS you can list all advised indexes and also the condensed indexes.
It is possible to create directly an advised index.
You may also be able to see the SQL Statements that advised the index (as long as there was no IPL or any other event that removes access plans for the SQL plan cache)
an index probe is always better than a table probe
A table probe normally works hand in hand with an index access. It is just the point where the data is read from the database.
Index probe and index scan will only read the keys.
Index probe is better than an Index scan, since, a record can be directly found. In an index scan all keys (may be because of the relevant information is in the second or third key) have to be read (which still can be faster than reading all rows in a table - table scan).
SQL optimization is not as trivial.
Just generating all advised indexes is also not a good idea, since each index costs performance when inserting, updating and deleting rows, since all indexes also must be directly updated.
AFAIK IBM (Kent Milligan) offers a workshop to learn the details.
... and I also offer a workshop (if interested just send me a private eMail)
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 Daniel Gross
Sent: Thursday, 4 September 2025 21:51
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: imbedded sql index suggestions
First - if you are developing those embedded SQL programs yourself (or your team), then make it a habit to try your queries and statements in iACS Run SQL Script (RSS) and use the "Explain while Running" button to analyze the statements with Visual Explain.
Keep an eye on table scans and suggested indexes - an index probe is always better than a table probe - and so on.
Second - try to learn something about database performance and indexes - maybe the source is Markus Winand:
->
https://use-the-index-luke.com/
Third - visit the SQL Performace Center in iACS and find MTIs and advised indexes.
But don't simply create every index advised - better find out, why this index is needed, and if you could change the query to use an existing index.
Improving database performance is like home improvement - it really never ends.
HTH
Daniel
Am 04.09.2025 um 18:46 schrieb stefan@xxxxxxxxxx:
Have a look in the plan cache - Managed Temporary Indexes (MTI) are being built by the DB Manager if found necessary. They will disappear at next IPL.
See if some of those needs to be permanent objects and create them.
Best regards
Stefan
--
No trees were killed in the sending of this message, but a large number of electrons were terribly upset.
Stefan Tageson
+46 732 369934
stefan@xxxxxxxxxx
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
eric bothes
Sent: Thursday, September 4, 2025 6:42 PM
To: Midrange-L <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: imbedded sql index suggestions
We are using a lot more sqlrpgle processes. i know when im in debug mode i can see suggestions of what indexes to build. is there something that will tell me across all applications using embedded sql which suggested access paths should be built?
--
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.
--
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.
midrange.com
