Thanks for the Input Steve. Really appreciate it.
Regards
Janith
-----Original Message-----
From: system21-bounces@xxxxxxxxxxxx [mailto:system21-bounces@xxxxxxxxxxxx]
On Behalf Of Prill, Steve
Sent: Thursday, 21 April 2011 11:26 p.m.
To: System 21 Users
Subject: Re: [SYSTEM21] Embedded SQL Performance Issues
Janith,
Go to
http://archive.midrange.com/system21 and type V6R1 in the search.
There are a couple of previous discussions that may answer your question.
Also, there is a System 21 group on Linkedin that had a discussion about
V6R1.
I don't remember if any address V6R1 and SQL in particular.
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 Janith Wanigasekara
Sent: Wednesday, April 20, 2011 6:03 PM
To: 'System 21 Users'
Subject: Re: [SYSTEM21] Embedded SQL Performance Issues
Hi Everyone
We are at Aurora 1.2 (Style) and about to upgrade the OS from v5r4 to v6r1
(This weekend). We use embedded SQL in lot of the programs.
I would like to know if anyone using Aurora style has done the above upgrade
before and come across any issues at all.
Thanks in advance
Janith
-----Original Message-----
From: system21-bounces@xxxxxxxxxxxx [mailto:system21-bounces@xxxxxxxxxxxx]
On Behalf Of Hudson McVay
Sent: Thursday, 21 April 2011 6:31 a.m.
To: 'System 21 Users'
Subject: Re: [SYSTEM21] Embedded SQL Performance Issues
Great!
Glad it worked.
As for the users, you are on your own.
Hudson
-----Original Message-----
From: system21-bounces@xxxxxxxxxxxx [mailto:system21-bounces@xxxxxxxxxxxx]
On Behalf Of Prill, Steve
Sent: Wednesday, April 20, 2011 1:50 PM
To: System 21 Users
Subject: Re: [SYSTEM21] Embedded SQL Performance Issues
Hudson,
WOW, WOW, WOW!
I typed STRDBMON on an iSeries command line, prompted it (F4), and entered a
file and library on the OUTFILE parameter and left the default for all of
the other parameters. I then ran the program with the SQL performance
problem and when it was done did ENDDBMON. I then used the RUNQRY command
over the file I entered on the STRDBMON command. The query output shows the
"Advised Keyed Columns" (the logicals that you need to create) in column 920
of the file. The file used can also be seen in position 455 of the query.
I created the logicals suggested in the query and the program with the
performance problem runs almost instantly now.
I appreciate all of the help with this.
You people are the best and there is always someone willing to help.
Now if I could just get the users out of this "it's always been that way"
mode.
Any suggestions? Just kidding.
Thank you-------
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 Hudson McVay
Sent: Wednesday, April 20, 2011 11:08 AM
To: 'System 21 Users'
Subject: Re: [SYSTEM21] Embedded SQL Performance Issues
Whatever I used came with the system.
Either in Navigator or STRDBMON.
A google search will bring up some redbooks on performance tuning, that's a
place to start.
I believe in was in Navigator where it suggested what views to build.
I just don't remember what I did or how I got there.
You should just have to create the logicals (views) and at run time the sql
optimizer will use the new view.
You should notice the speed increase the next time you run payments.
I did not make any changes to PL431 but I have recomplied PL431 for another
different mod.
Hudson
-----Original Message-----
From: system21-bounces@xxxxxxxxxxxx [mailto:system21-bounces@xxxxxxxxxxxx]
On Behalf Of Randy Rasp
Sent: Wednesday, April 20, 2011 10:44 AM
To: System 21 Users
Subject: Re: [SYSTEM21] Embedded SQL Performance Issues
I am very anxious to try Hudson's suggestion.
Do I just create these logicals over PLP15? Do I need to change PL431 to
use the new logical? PL431 currently uses PLP15 in the declare code.
Will SQL figure out that it has a better access path and start using it?
Thanks everyone!
On Wed, Apr 20, 2011 at 9:38 AM, Prill, Steve wrote:
Hudson,
Are the SQL monitors already on the system? How do you use them?
Thanks----
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 Hudson McVay
Sent: Wednesday, April 20, 2011 10:09 AM
To: 'System 21 Users'
Subject: Re: [SYSTEM21] Embedded SQL Performance Issues
Does sound like an index problem.
I had the same issues a few years ago with 1/APY - report payments due
also, went from seconds to hours to run I used one of the sql monitors
to view what was being processed, it suggested what views to create.
Created the views and that did fixed it.
For the 1/APY issue I created 2 views,
R PLR15 K LOGI15 K CONO15 K SUPN15 K
CURN15 K PDUE15 K ETYP15 K LREF15
And
R PLR15 K LOGI15 K CONO15 K
SUPN15 K RSTS15
Worked for me, your milage may vary.
Hudson McVay
-----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.
_______________________________________________
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.
_______________________________________________
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.
_______________________________________________
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.
<p style="font-size:10px;">
We are committed to protecting the privacy of the personal information you
may provide to us by ensuring your e-mail address and any other personal
information about you will only be disclosed to relevant staff and
contractors and used for the purpose of responding to a request or to carry
out instructions. We will not sell or give your e-mail address to anyone
else. Please be aware that e-mail is not necessarily secure against
interception so you should only send information necessary for us to provide
you with the assistance you require.
This transmission or any part of it is intended solely for the named
addressee. It is confidential and may contain legally privileged
information. The copying or distribution by any person other than the named
addressee is prohibited. If you have received this transmission in error,
please reply by e-mail to the sender.
This email message has been swept for the presence of computer viruses.
</p>
_______________________________________________
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.
midrange.com
