Focal Point
Multiple Concurrent DB2 Requests

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5491000132

June 04, 2007, 03:11 PM
jmdoyl1
Multiple Concurrent DB2 Requests
We had a situation this morning where 8 long running (3+ minutes) request tied up 100% of the CPU on the database server. The dba told us that there is no way of throttling a process to only use x % of the CPU. We are on DB2 8.1.6 and the database runs on AIX 5.2. The reporting servers are on their own AIX box. We connect from the WF Server to the database server via DB2 8 Connect.

We have the DB2 Governor turned on to cut off long running requests. We also leverage Report Scheduling but we can't force our external end users to use it. However they do catch on and schedule the long running reports.

Any thoughts on this?

Jon


WebFOCUS 7.1.3 Windows Client AIX 5.2 Report Servers
June 04, 2007, 03:36 PM
TexasStingray
With WebFOCUS Resource Governor you can force users to do just that.




Scott

Your dba prob fibbing. Most rdbms have throttle facility - I know Oracle does. Have a look at the docs for db2.

Having had a long experience with dba s I know what they are like! They are the God user so they think they should act like God!



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
8 queries that take over 3 minutes each to run?

There's some great technology that DB2 has that other databases do not, for example their query plan tool is the best I've seen. Your DBA should be your goto on query planning and optimization, but you should have very very few queries that clock in over a minute. It's just a matter of proper indexing and usage of IBM's materialized query tables to facilitate better performance.

The IBM p-series is a solid platform with AIX, but I've seen some fairly old hardware running in production before. You may want to ask your DBA or systems engineers about the hardware itself before tuning your queries.

Feel free to contact me via personal message if you'd like to know more about query optimization or DB2 in general.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
Thanks for the input so far. The database server is getting upgraded in the near future. So hopefully that will help. We not in a position to use the Resource Governor Silver Bullet. I will look into any throttle options for DB2.


WebFOCUS 7.1.3 Windows Client AIX 5.2 Report Servers
Hey Jon,

I had our DBA look into this and he found a DB2GOV command that he thinks will do what you need. I don't know too much about it, or even if it's a separate utility or what. You can probably get more information from IBM's website.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat