Re: RPGLE Converted into SQL having performance issue

Ashwani

To add to Frank's suggestion, I think he is talking about Visual Explain and the performance monitors - I've not heard of the term "SQL logs" myself, but that's what I think he means.

Your job log messages are what you see when you use STRDBG and then run the program. It no longer gives you all the information you can get, but it can be a start. At any rate, you have the last 3 messages saying an index was created, that index-only access (a very efficient choice of the optimizer) is used, and that the index just created was used - probably, because we don't see the 2nd-level message information - that is critical to understand these messages.

Index creation can be pretty expensive as to time, so you really want to avoid that. But the SQL4009 says an index was created -

This link gives a good explanation of temporary index - https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqtempinscan.htm - the optimizer probably decided that it would take longer to retrieve the values with probably a scan of the table than to take the time to create the index and get the values out of the key values.

SQL4008 mentioned MTI(16) - MTI means "materialized temporary index".

So look at the 2nd-level detail, if necessary, show it to us - when doing tech support on IBM i issues, the top-level message text is ___never___ enough!  :)

BTW, just in case, do not use LFs as the file in a SELECT statement - the optimizer ignores it and has to find the parent PF anyway, then determine the best plan.

Regards
Vern

On 12/15/2020 5:40 AM, Frank Kolmann wrote:

Hi Ashwani

Have you checked the SQL logs and created the suggested indexes?

Regards
Frank


On 15/12/2020 7:33 pm, Singh, Ashwani (Lake Mary) via RPG400-L wrote:

Hi Guys,



We converted our one of the main module for transaction processing from RPGLE to SQLRPGLE and we are getting a lot latency and low performance issue.

--
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