Re: RPG and SQL

On 10/19/2018 5:17 PM, Doug Englander wrote:

I did not know that the Optimizer changes the plans based on those factors. That could explain the issue. Is that documented anywhere that you know of in an IBM manual?

Here's one I recently stubbled across by searching.

IBM i 7.3 > Connecting to your system > IBM i Access > IBM i Access
Client Solutions: Application Packages > Windows Application Package >
Programming > Database programming > IBM i Access ODBC > ODBC
performance > SQL performance > Optimizer

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzaik/costest.htm

I'm normally using the Table of Contents and Index. The Database topic
is where I'd start:
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqkickoff.htm
The topic 'Access plan validation' has more useful details:
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/apvalid.htm
specifically:

'To improve performance, an access plan is saved once it is built (see
following exceptions), to be available for potentially future runs of
the query. However, the optimizer has dynamic replan capability. This
means that even if a previously built (and saved) plan is found, the
optimizer could rebuild it if a more optimal plan is possible. This
process allows for maximum flexibility while still taking advantage of
saved plans.'

Also, see 'Single table optimization':
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/decmakrules.htm