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