We recently set up a brand new Linux CentOS machine with DB2 UDB 8.1 FP 18 64-bit and I wanted to make sure that I have the configuration parameters all optimized. What are the various variables I should be looking at? Or be concerned about? Any help will be appreciated! Thanks!
I guess my question is too generic.. basically I was wondering about Performance Tuning DB2...I know for sure that I need to check the bufferpool size since the new machine has much more memory.. was just wondering what other parameters can be set up to ensure faster speed on the SQL queries / procedures.
Here are some of the other parameters that have been setup on one of our current systems --
If the current application performance does not cause concerns, there is no need to optimize anything.
If, on the other hand, there are specific issues with performance, they need to be analyzed and appropriate actions taken. Use snapshot monitor output to identify problem areas, such as:
- bufferpool hit ratios;
- sort overflows;
- lock escalations and timeouts;
- prefetch performance;
- low query selectivity (rows selected to rows read ratio);
- page cleaner efficiency;
Once you have identified the problem, it will be relatively simple to determine what tuning needs to be done.