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.