Re: Embedded SQL Performance Issues

Randy,

No we haven't upgraded but plan to in the near future.

You might want to look at the response/suggestions from Gary Lewis to my issue.

These look like they may fix some of these types of issues.

Thanks for responding.

Steve

Steve Prill | I.T. Manager | Rexair LLC |50 W. Big Beaver Rd. Suite 350 | Troy, MI 48084 | 248-816-8627 | fax 248-524-2191 | sprill@xxxxxxxxxxxxx
 


-----Original Message-----
From: system21-bounces@xxxxxxxxxxxx [mailto:system21-bounces@xxxxxxxxxxxx] On Behalf Of Randy Rasp
Sent: Wednesday, April 20, 2011 9:53 AM
To: System 21 Users
Cc: SYSTEM21@xxxxxxxxxxxx
Subject: Re: [SYSTEM21] Embedded SQL Performance Issues

Steve -

Did you just upgrade your operating system to V6R1 by chance?

We never had performance problems in the past, as our AS/400s in recent
years has been oversized.

We upgraded to V6R1 two weekends ago and I am now getting sporadic
reports of jobs that appear to be stuck. When I investigate it's always
a RPG program with embedded SQL. The jobs are running, they are just
VERY slow.

One job in particular is 1/APY - report payments due. It used to take
only 15-20 seconds to complete, and it now takes over 45 minutes. When
this job is running, our CPU jumps to 75+ % utilization. Prior to V6R1,
our AS/400 was rarely above 10%

When this job is running, the program stack indicates that it's called a
program in QSYS called QSQROUTE.

I signed onto Fix Central and we have all of the latest PTFs from IBM.

I am not sure what to do at this point.

Randy Rasp
AER Manufacturing



On Wed, Apr 20, 2011 at 7:25 AM, Prill, Steve wrote:

We are at 3.5.2 SP3. A user complained to me about the performance
when
the quantities were changed on a Bill of Material via menu MDM option
21
(Routes/Structures). This has been occurring forever but we are just
now hearing about it. It was taking about 20 seconds to change the
quantity on one part but when they would change two of more quantities
then press F8 to update it could take 3 or 4 minutes to complete the
update. I had a vendor look at the application and they found an
issue
with the embedded SQL. They found and fixed the issue in program
DB590
and the update is now nearly instantaneous. In this case they had to
create a new logical and modify the SQL.


Today another user complained about the performance, that has been
occurring forever, when using the "Routes/Structures Audit" (MDE
option
8). After a part/route is entered and a change is selected via
"2=Route
Structure Inquiry" and enter is pressed on the next screen, it can
take
up to 5 minutes to get to the screen titled "Process Route Audit
Inquiry
- Inputs". The issue appears to be with various SQL statements in
program DB561.

There seems to be a pattern here and I know there are many other SQL
programs in System 21 and I now wonder if this performance issue is
occurring in other programs but our users have just learned to live
with
it. In the 8/MDE problem above the users have simply chosen not to
use
the option.


Has anyone else experienced similar issues with embedded SQL and maybe
found a fix? Unfortunately, we do not know how to troubleshoot
embedded
SQL issues. I know my vendor can look into it and fix it but wonder
if
there is a general fix for these type of issues with embedded SQL that
my programmers can look into.


Thanks in advance for your input.


Steve Prill


Steve Prill | I.T. Manager | Rexair LLC |50 W. Big Beaver Rd. Suite
350
| Troy, MI 48084 | 248-816-8627 | fax 248-524-2191 |
sprill@xxxxxxxxxxxxx <mailto:sprill@xxxxxxxxxxxxx>


_______________________________________________
This is the System 21 Users (SYSTEM21) mailing list
To post a message email: SYSTEM21@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/system21.

_______________________________________________
This is the System 21 Users (SYSTEM21) mailing list
To post a message email: SYSTEM21@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/system21.