Re: Embedded SQL Performance Issues
"Janith Wanigasekara" <[email protected]> Sat, 23 Apr 2011 12:06:36 +1200
| Newsgroups | gmane.comp.systems.as400.jbausers |
|---|---|
| Message-ID | <[email protected]> |
Hi Randy Upgrade was done with no issues so far. I ran the 1/APY and it only took 30 seconds. We haven’t done any changes to the object PL431 so the standard version in AULPLP1 works ok. I imagine standard object PL431 in AULPLP1 has the same issue in your case Thanks for your input Regards Janith -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Randy Rasp Sent: Friday, 22 April 2011 2:24 a.m. To: System 21 Users Subject: Re: [SYSTEM21] Embedded SQL Performance Issues I was/am in your shoes. We are using Aurora 1.2 (regular) and just upgraded from V5R4 to V6R1 about two weeks ago. We ran IBM's tool and it identified a handful of programs that would not be compatible, but the only JBA programs were in the PTF manager libraries, which we haven't used in a while. We have a handful of programs that have SQL embedded and everything seems to be fine with one exception. For some reason, 1/APY (PL431) runs agonizingly slow. Hudson gave us a suggestion of creating a couple of new logicals to speed it up, which I have done, but won't know until later this afternoon if it works. The only other issue we had was with our e-comm website. Our site has ASP code with SQL that queries the AS/400 and a couple of our SQL's with JOINS fell over. All I had to do to make them work was change the order of the files at the beginning of the SQL statement and now they work fine. Apparently IBM tightened up on the syntax of SQL when accessing the AS/400 through ODBC, and our old code wasn't compliant with today's standards. Good luck! Randy Rasp AER Manufacturing On Wed, Apr 20, 2011 at 5:02 PM, Janith Wanigasekara wrote: > 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: [email protected] > [mailto:[email protected]] > 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: [email protected] > [mailto:[email protected]] > 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 | > [email protected] > > > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] > 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: [email protected] > [mailto:[email protected]] > 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 | >> [email protected] >> >> >> >> -----Original Message----- >> From: [email protected] >> [mailto:[email protected]] 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: [email protected] >> [mailto:[email protected]] On Behalf Of Randy Rasp >> Sent: Wednesday, April 20, 2011 9:53 AM >> To: System 21 Users >> Cc: [email protected] >> 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 | >>> [email protected] <mailto:[email protected]> >>> >>> >>> _______________________________________________ >>> This is the System 21 Users (SYSTEM21) mailing list To post a >>> message >>> email: [email protected] To subscribe, unsubscribe, or change >>> list options, >>> visit: http://lists.midrange.com/mailman/listinfo/system21 >>> or email: [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: [email protected] To subscribe, unsubscribe, or change >> list options, >> visit: http://lists.midrange.com/mailman/listinfo/system21 >> or email: [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: [email protected] To subscribe, unsubscribe, or change >> list options, >> visit: http://lists.midrange.com/mailman/listinfo/system21 >> or email: [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: [email protected] To subscribe, unsubscribe, or change >> list options, >> visit: http://lists.midrange.com/mailman/listinfo/system21 >> or email: [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: > [email protected] To subscribe, unsubscribe, or change list > options, > visit: http://lists.midrange.com/mailman/listinfo/system21 > or email: [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: > [email protected] To subscribe, unsubscribe, or change list > options, > visit: http://lists.midrange.com/mailman/listinfo/system21 > or email: [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: > [email protected] To subscribe, unsubscribe, or change list > options, > visit: http://lists.midrange.com/mailman/listinfo/system21 > or email: [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: [email protected] > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/system21 > or email: [email protected] > 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: [email protected] > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/system21 > or email: [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: [email protected] To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/system21 or email: [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: [email protected] To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/system21 or email: [email protected] Before posting, please take a moment to review the archives at http://archive.midrange.com/system21.