Re: RPG and SQL

First, have you run Visual Explain on the statement to see how the system
is processing the statement and what indexes it is recommending.

Second, have you turned on SQL Monitor on days that it is running slow to
see what it is doing on this statement?

Both should give you a better picture as to what is happening.


On Fri, Oct 19, 2018 at 6:37 AM Doug Englander <
denglander@xxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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

exec SQL
Select coalesce(zzn, ' ') into :H2
from hmaint
Where cust# = :mcustomer# and
Acct# = :maccount# and
fieldn = 'M32' and
cdate = :lvcdate and
ctime < :lvctime
order by ctime desc
fetch first row only;

File HMAINT is indexed but not on any of those fields in the where clause.
That file is a maintenance log file and is written to all day via a trigger
on various files. The file has 77,272,773 rows in it, and grows daily. What
would cause this job to run in minutes some days, while taking hours other
days? I cannot see anything in the job log that would indicate any issues.
After many hours, if the job is cancelled and restarted, it completes I
minutes. We experience this same issue with other RPG programs with
embedded SQL [not sure if they are using this file, though]. We are
thinking it has something to do with system workload at the time the job
starts, and when the system is loaded, the SQL Engine allocates a small
amount of resources to the job, as opposed to if the program starts when
the system is not so busy, the engine allocates more resources and the job
runs faster. If that is the case, the system does not seem to allocate more
resources to the job when they becom
e available. Would it have something to do with record/file locks?

I was thinking and index would help, but since the job runs in minutes
sometimes, I do not think that is the total solution. We are on V7R3. Does
anyone know if there is a PTF we need or a setting / configuration value
that needs to be changed so this job in minutes consistently?

Thank you,

Doug

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD