BPA4DB2 is designed to make bufferpool monitoring and bufferpool tuning simple. It analyzes measurements, identifies tuning opportunities and provides guidelines and precise instructions on how to set up the pools. BPA4DB2 clearly indicates tuning potential and bufferpool related problems whether the issue resides with group bufferpools, local bufferpools or a particular object.
Maintaining a number of DB2 subsystems under constant observation can occupy the time of one or more DBAs. BPA4DB2 helps to turn an occasional project into an efficient ongoing task through a permanent automated audit. Whenever the tool determines that the DB2 system is not adhering to prescribed quality criteria an email alert is sent to the database administration along with a metrics file. This file gives the DBA not only access to the critical data, but also describes and explains the problem and offers/prepares the best practices counter measures.
How can changes to the bufferpool setup improve overall performance? Is it possible to save CPU time? Can access times be shortened, bottlenecks eliminated and related problems avoided? In other words, are there problems with the bufferpools or is the current bufferpool setup already optimal? How to proof tuning potential?
Revealing problems and tuning opportunities need to be complemented with a metrics system that quantifies the expected improvements. We believe that ReReads and the ReRead Percentage are unique indicators of good or bad buffer pool behavior. Distinct GetPage describes the memory capacity requirements. ReReads identify avoidable I/Os while Distinct GetPage stand for the size a buffer pool requires. BPA4DB2’s primary assessment rule is simple:
No ReReads –? leave bufferpools alone and use time more wisely
ReRead Percentage > 10% –? there is an opportunity to enhance performance
Additionally, BPA4DB2 offers a wealth of bufferpool related details, besides ReReads and Distinct GetPages, DASD-IO, Average-IO, etc. Based on the analysis BPA4DB2 generates ALTER statements, recommends optimal bufferpool sizes, appropriate assignment of objects to pools, optimal bufferpool threshold settings and offers guidance for an entire redesign of the current bufferpool setup, if required. It also identifies object related problems as well as difficulties that result from poor SQL. It fully supports group bufferpool tuning and allows continuous auditing of the Coupling Facility. Best of all, BPA4DB2 explains and justifies the suggested tuning measures and provides information (rich colored charts) to facilitate a visual analysis.