Hi,
on DB2 Enterprise v8.2 fp11 on Linux I have set DB2 Governor to the following settings:
======
dbname mydb;
interval 1;

time 6:00 22:00 setlimit cpu 5 action priority 20;
time 6:00 22:00 setlimit cpu 600 action force;
======
I have executed "db2 get snapshot for applications on mydb" to check "priority" at which application runs and the result is: "Priority at which application agents work = 20" then I have checked "Coordinator agent process or thread ID = 25625" then this data compared to Linux top command PID and I can see application which priority was lowered to 20 by DB2 Governor is occupying 100% of computer CPU. This application is making problems and should not occupy CPU more then 5% or so.

Did I set DB2 Governor correctly? Do I need to set something else to lower CPU on problematic applications? I would just like to set problematic application to not occupy CPU more then 5%.

Any idea how to set DB2 Governor? Or any other idea how to lower CPU usage.
Regards,
Grofaty

For the Governors I can say anything till its checked. But you can check the following for lowering your CPU. they are very much conventional and widely used:

1> Take out the SQLs for the issue-creating application. Run DB2Expln and DB2Advis on them.

2> See if proper Indexes are used or not. See how much percentage gain you can achieve by putting the Indexes and your CPU timeron is reduced.

3> Use alternative options like using MQTs, Partitioning-key etc to gain better performance.

4> Ensure your hardware is having sufficient bandwidth to support your application load. If the DB size is huge, you need to go for better Disk and more CPUs.

3>

Hi,
@JAYANTA_DATTA, thanks for tips, but I am aware hot to fix performance problem for particular SQL for known application that produces problems. That is not a problem at all - I know how to fix that. I have solved the problem in particular application that was producing performance problem, but in the future I would like to prevent such "applications from hell" to just not eaten 100% of CPU.

What I would like to do is to eliminate any "application from hell" that can in theory hit DB2 server computer at any time. In this purpose I have configured DB2 Governor, but I am surprised to see that DB2 Governor has lowered priority to the lowers possible 20 priority, but CPU is still 100% eaten by "application from hell".

If priority is set to lowest by DB2 Governor it will be the last process to get something to do (this is the way DB2 Governor is handling performance problems), but it still eats whole memory. I would like to set: any application should not eat more then 100% of CPU more then 5 second if it does CPU consumption by this application should change to no more then 5% CPU.

Any help is greatly appreciated.
Regards,
Grofaty

i havent used db2 governor ....but this thing can be done more efficiently with db2 query patroller

In V9.5, work-load manager can do exactly the same as Grofaty is asking for. But I can see his DB is on 8.X. So not much of help with this.

Hi,
I have look into documentation about work-load manager, but can't find out the settings you are talking about. Can you please provide some sample for v9.5? We will migrate to v9.5 in couple of weeks, so I can wait to v9.5 if there is way better solution than in v8.2. Any sample or any good web page?
Regards

I hope this tutorial link will be helpful while implementing WLM:

http://publib.boulder.ibm.com/infoce.../c0053139.html

Jayanta
DB2 UDB Consultant
Morgan-Stanley/Headstrong

JAYANTA_DATTA, thanks for tutorial. If I understand correctly workload manager is used to group together tasks (workloads) and set them priority.

I have quickly read the tutorial, but can't find exactly what I am looking for: omit cpu consumption to no more then 5% by any application. With DB2 Governor I can set priorities, so applications witch is consuming a lot of CPU gets the lowest priority. This works like renice on Linux/Unix, but I need something like cpulimit on operating system level like Linux - so physically no more then 5% CPU.

Can you please provide just a sample that is writing about cpu consumption and how to omit it?
Regards,
Grofaty

Hi Grofaty,
I don't have the exact sample you are asking for where the steps are mentioned one after another.

Have the PDF for complete implentation of WLM which includes almost all the features available in QP. Otherwise if CPU limitation is the only single thing you are mainly looking for, then with Governor available in V9.5, the steps are exactly same like the one available for V8.X.

Thanks,
Jayanta