× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



We are having the identical issue. On the Production box, I can see
different variables changing and potentially causing a problem. But I am
also seeing it on the Development box. In an effort to deteremine what the
issue is, I create a Test Library that contains only the two tables and the
indexes I created. The table data is not changing, static. I am only
testing the extraction for speed. I have used SQL Monitor and Visual
Explain. When it runs long, it has created a new Plan Cache. I delete all
the Plan Chaches (again, on Development, it is in a dedicated library for
just this single process) and it will run quick again. Sometimes, but *not
always*, this happens after I have run similar SQL scripts for this
library with ACS-Run SQL Scripts. "Best I can tell", it stems from the
Plan Cache. I don't really know, but from my tests and observations, it is
my best guess. Good Luck!!!!!

On Fri, Oct 19, 2018 at 10:05 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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

This mailing list archive is Copyright 1997-2024 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.