RE: RPG and SQL

I would recommend looking at the index advisor for that file. I have create quite a few different "advised" indexes over larger files, and the results are sometimes very dramatic.

My guess is that when it runs quickly, it's using a temporary index that the DB has already created.

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Campin
Sent: Friday, October 19, 2018 9:42 AM
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Subject: 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

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