|
Hi, If a query/400 or an SQL query consumes a lot of CPU, normally a table scan is performed (either to read all records or to build a temporary index). Just start STRDBG before you run your queries. If you use STRDBG just look at the joblog messages. If indexes are adviced, you'll find it in the joblog. Try to create these additional indexes. Using DBMON, will provide much more information. Just have a look at the following Redbook you'll find a lot of tips how to use SQL Diagnostic tools: SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries http://www.redbooks.ibm.com/abstracts/sg246654.html?Open Mit freundlichen Grüßen / Best regards Birgitta Hauser "Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown) "If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!" -----Ursprüngliche Nachricht----- Von: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Johan Vangilbergen Gesendet: Thursday, February 15, 2007 09:05 An: Midrange Systems Technical Discussion Betreff: RE: Query using (too) much CPU In our application, I've put a command before the query to change the job to a lower priority. This seems to work. We have also a pc-based query tool, which uses ODBC to connect to the AS400 database. Do you have any idea how I can limit the priority of these jobs ? I'm not quite sure which are the jobs on the AS400 who "do" the ODBC-connection or in which subsystem they run. Johan Vangilbergen YUSE Phone : +32 16 74 50 80 Fax : +32 16 74 50 89 Email : jvg@xxxxxxx Web : WWW.Yuse.be Address : Bondgenotenlaan 138, B-3000 Leuven, Belgium -----Original Message----- From: midrange-l-bounces+jvg=yuse.be@xxxxxxxxxxxx [mailto:midrange-l-bounces+jvg=yuse.be@xxxxxxxxxxxx] On Behalf Of Willie J. Moore Sent: dinsdag 13 februari 2007 18:37 To: Midrange Systems Technical Discussion Subject: RE: Query using (too) much CPU John, Try the CHGJOB RUNPTY(25) command. Set the priority to a low level. We do this in a CL that the user calls. It did seam to help. The queries run a little slower but not as big an impact on system. Willie.. -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of DeLong, Eric Sent: Tuesday, February 13, 2007 9:31 AM To: Midrange Systems Technical Discussion Subject: RE: Query using (too) much CPU Johan, SQL runtime performance can be tuned in many ways, though I can't think of a way to truly limit a query to run at low priority... Honestly, you'd be better off getting the query optimizer to help you build index objects that could speed up your queries. Run your job in debug (update production data *yes) and review the joblog for index recommendations. hth, Eric -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Johan Vangilbergen Sent: Tuesday, February 13, 2007 9:47 AM To: midrange-l@xxxxxxxxxxxx Subject: Query using (too) much CPU On our 810 V5R2M0, we're having some problems with queries consuming a lot of CPU, and slowing down all other tasks. These queries originate mainly from 2 sources : 1. Programs with embedded SQL, which produce statistics over a specified period of time 2. An SQL-tool, running on a PC-client, which does inquiries on the AS400 database through ODBC. The fact now is, that when a user starts a query (which he can define himself) the system becomes very slow and the query consumes a lot of CPU, killing all other jobs. My question is : is there a way to prevent these tasks from putting the AS400 to his knees. I still want these tasks to be executed, eventually at a slower rate. But the essential is that the other jobs should not suffer under this query. Can anyone point me in the right direction ? Johan Vangilbergen YUSE Phone : +32 16 74 50 80 Fax : +32 16 74 50 89 Email : jvg@xxxxxxx <mailto:jvg@xxxxxxx> Web : WWW.Yuse.be <http://www.Yuse.be> Address : Bondgenotenlaan 138, B-3000 Leuven, Belgium
As an Amazon Associate we earn from qualifying purchases.
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.