RE: RPG and SQL

I would definitely run this thru Visual Explain and create the recommended index(es). It could improve it from minutes/hours to seconds/minutes. Maybe not that much, but it should help.

If the system already has an access path, that can speed up execution. I can't imagine that would cause it to jump, apparently randomly, between minutes and hours to execute.



-----Original Message-----
From: Doug Englander [mailto:denglander@xxxxxxxxxxxxxxxxxxxxxxxx]
Sent: Friday, October 19, 2018 8:37 AM
To: 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]:

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