BPA4DB2 / Improve Performance and Lower Costs

The question is: Are there problems with the bufferpools or is the bufferpool setup 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. BPA has been designed to provide precise metrics for those who need them.

ReReads together with 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 stands 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

BPA4DB2 indicates tuning potential and bufferpool related problems. 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.

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 regular 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 Work station. 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 tools ease of use, it is possible to use data obtained from your site for an online review that you can relate to.

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 diffi culties that result from  poor SQL. BPA4DB2 fully supports Group Bufferpool Tuning and allows continuous auditing of the Coupling Facility.

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.

Measurements are scheduled once a day or every x hours. The host based routines analyze each measurement file and check for preset quality criteria sending an email alert if appropriate. Database administration is freed from the tedious observation of I/O performance and related issues. It gets all the tools needed for the analysis and assessment of the metrics gathered.