Re: RPG and SQL

"RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx> wrote on 10/19/2018 09:42:53
AM:

----- Message from Doug Englander
<denglander@xxxxxxxxxxxxxxxxxxxxxxxx> on Fri, 19 Oct 2018 13:37:25 +0000

-----

To:

"rpg400-l@xxxxxxxxxxxx" <rpg400-l@xxxxxxxxxxxx>

Subject:

RPG and SQL


We have one RPG program with embedded SQL that processes roughly the
same volume each day. It gets run from the JOBQ. Some days this job
takes minutes to run, while other days it takes many hours. When the
program runs for hours, the call stack indicates the program is
running this SQL [this SQL gets executed for each row the RPG
program selects to be processed]:

Normally I would tell you something to keep in mind is that the SQL
optimizer may change the access plan based on any number of factors which
can include:
- available memory and other system resources
- other system activity
- database statistics (such as number of unique values for fields in any
of your selection fields (i.e., the WHERE clause)
- and possibly other factors, too.

Since your file is not indexed by the selection fields, SQL would
generally have to do a table scan (i.e., reading through all the rows in
the table to find one --the one-- that satisfies the selection criteria).
The other likely difference access would be via a temporary index. Only
the optimizer decides how to access the data. Even when the optimizer
determines the need for a new access plan or temporary index, you can see
varied times because the data needed is already in main storage (i.e.,
memory) or not.

I would look at running Visual Explain and the Index Advisor (as suggested
by Alan and others) and particularly see if you can add any indexing to
help the optimizer choose a simpler and more consistently performing
access plan.

Michael Quigley
Computer Services
The Way International