Efficient buffer pool tuning improves performance and saves on expensive mainframe resources. However, buffer pool tuning usually proves to be challenging. In many cases, it is so daunting and labor intensive that it tends to be done as a project and only when ‚needed‘. Seldom is it implemented as an ongoing task. This lack of monitoring leads to I/O problems, avoidable CPU consumption, inefficient storage usage, and might in succession tempt to premature CPU upgrades and memory expansion. BPA4DB2 offers a better and cost effective alternative. Designed to be easy to use, it offers expert system problem detection, problem resolution guidance and automated audit with pro-active notification for impending problems.


Automatic Problem Detection
The question is: »Are there problems with the bufferpools or is the current bufferpool setup already optimal? « In other words, is there tuning potential or not? Can changes to the bufferpool setup improve overall performance or will the changes prove unneeded? How much improvement can be expected? Is it possible to save CPU time? Can access times be shortened, bottlenecks eliminated and related problems avoided?

Revealing problems and tuning opportunities need to be complemented with a metrics system that quantifies the expected improvements. This is particularly important to those responsible for maintaining multiple Db2 subsystems. BPA4DB2 has been designed to provide precise metrics for those who need them.


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 assessment rule is simple:

No ReReads –
leave bufferpools alone and use time more wisely

ReRead Percentage > 10% –
there is an opportunity to enhance performance

Problem Resolution Guidance
BPA4DB2 clearly indicates tuning potential and bufferpool related problems. It describes the problem and its causes. Whether the issue resides with group bufferpools, local bufferpools or a particular object, BPA4DB2 provides the information required to resolve the situation. When it uncovers and describes all feasible solutions to a problem or tuning issue, it does so in plain English phrases: enlarge or decrease the pool size of Bpxx to…, change this threshold to…, re-assign (shift) these objects from BP0x to BP0y.

Valuable DBA time can now be spent on analysis and ’what if‘ reasoning instead of plowing through volumes of raw data. BPA4DB2 generates ALTER statements, offers re-design strategies for the pools and suggests object grouping enhancements. BPA4DB2 justifies the suggested tuning measures and provides information (rich colored charts) to facilitate a visual analysis. It offers a wealth of bufferpool related details, ReReads, Distinct GetPages, DASD-IO, Average-IO, etc. Its greatest value lies in providing detailed guidance for problem resolution and tuning.

Automated Audit
BPA4DB2 makes bufferpool tuning easy. It analyzes measurements, identifies tuning opportunities and provides guidelines and precise instructions for solving problems and tuning the pools. However, maintaining a number of Db2 subsystems under constant observation can occupy the time of one or more DBAs. BPA4DB2 maintains a permanent automated audit. This facility consists of two parts, a host component and a workstation component. The workstation is used to visualize the analysis results, to explain the findings and to present the recommendations for tuning and problem resolution. The host component gathers the metrics for the Db2 subsystems, creates a metrics file and invokes the host examination component (HEC). HEC processes the metrics file and reviews issues including the ReRead percentage (performance indicator), the sequential prefetch threshold (SPTH), the data management threshold (DMTH), the z/OS system paging rate, as well as issues related to data sharing groups like cross invalidation (XMIDIR), SCA fill level, and write failures in Group Bufferpool. Whenever HEC determines that the Db2 system is not adhering to prescribed quality criteria an email alert is sent to the database administration team along with the metrics file. This file gives the DBA access not only to the critical data, but also the ability to use the full analytical power of the local workstation. The tool describes and explains the problem and offers/prepares the best practices counter measures.

The bottom line is BPA4DB2 provides what DBAs require. An interactive webcast demonstration of the product is available to fully explore its many features. Given the tool’s ease of use, it is possible to use data obtained from your site for an online review that you can relate to.

Comprehensive Approach
Based on the analysis of the metrics collected, BPA4DB2 recommends:

• optimal buffer pool sizes
• appropriate assignment of objects to pools
• optimal bufferpool threshold settings

BPA4DB2 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. BPA4DB2 fully supports group bufferpool tuning and allows continuous auditing of the Coupling Facility.

Performance and Efficiency
BPA4DB2 is designed to make bufferpool monitoring and bufferpool tuning simple. It helps to turn an occasional project into an efficient ongoing task. A task that helps save money and avoid problems.