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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2021 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.