Re: SQL Index builds

This is the time to just admit that ACS is a must have tool, and get it loaded. When you compare ACS and the new Navigator, you can see the direction IBM is taking. ACS will be the centerpiece of the new approach.

All the tooling you’ll need to diagnose and correct the indexing issue (very likely not LF, which is a view/index hybrid) can be found in ACS.

Jim Oberholtzer
Agile Technology Architects

On Jan 31, 2022, at 8:56 AM, Vern Hamberg via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx> wrote:

This can give some information. Best is to use a combination of the tools in Access Client Solutions, as others have suggested a couple of.

In ACS is the SQL Performance Center - you can see SQL statements that were used in jobs - then you can run Visual Explain over the statement, VE will give you ever so much more information than STRDBG can. And Index Adviser was suggested - you should not automatically create them all, every index you create means more time to execute database I/O, since the index has to be updated for inserts, writes, updates, deletes, all that.

If you don't have ACS, many of the tools are available in the previous Client Access or Access for IBM i, in Navigator there you will see several actions you can take, in a section near the bottom.

HTH
Vern

On 1/31/2022 8:40 AM, Peter Colpaert wrote:
I think you can run your program in debug (STRDBG) and then view the temp
indexes in your joblog.
--

Peter Colpaert

Peter.Colpaert@xxxxxxxxx


Op ma 31 jan. 2022 om 15:39 schreef Sean Courtney <scourtney@xxxxxxxxxxx>:

Hello everyone,



maybe someone else also had similar issues.



Sometimes when I join tables in SQL a temporary index is built during
runtime.



I tried to counteract this by adding new LF’s and indexes … sometimes it
works and sometimes it doesn’t…



Is there a way to find out how sql builds these indexes ??



If yes could someone please give we a quick update on how to find the index
definition. If I were to create these indexes permanently then I would
probably

Improve runtime performance considerably ..







Mit freundlichen Grüßen / Kind regards / Bien à vous,



Sean

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com