Re: 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-hW6zm9MTFO98UrSeD/[email protected] <mailto:sprill-o28OYk0MVw58UrSeD/[email protected]>
>
>
> _______________________________________________
> This is the System 21 Users (SYSTEM21) mailing list
> To post a message email: SYSTEM21-Zwy7GipZuJhWk0Htik3J/[email protected]
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/system21
> or email: SYSTEM21-request-Zwy7GipZuJhWk0Htik3J/[email protected]
> 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-Zwy7GipZuJhWk0Htik3J/[email protected]
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request-Zwy7GipZuJhWk0Htik3J/[email protected]
Before posting, please take a moment to review the archives
at http://archive.midrange.com/system21.