The strength of BPA4DB2 is based on five unique characteristics:
Automatic Problem Detection
The first questions are always, are there problems with the buffer pools or is the current buffer pool setup optimal, in other words, is there tuning potential or not? Can changes of the buffer pool setup really improve the overall performance of the system, i.e. save CPU time, shorten response time, or will all my tuning efforts are a waste of time? A clear indication for GOOD or BAD (improvable) is needed, the more so, as database administration has to control not only one but many DB2 subsystems. Briefly, it is crucial to detect problems (and tuning opportunities) at a glance. This offers BPA4DB2 with its unique display of ‘Number of ReReads’ and ‘ReRead Percentage’. The ReReads represent avoidable IO, ReRead_% is a clear indicator, a perfect measure for the assessment of the pools buffer quality. With BPA4DB2 the assessment rule is simple: No ReReads – no problem; leave it alone, you waste your time.
Clear Briefings for Problem Solution
BPA4DB2 indicates clearly any tuning potential and each buffer pool related problem. Not only this, it also describes the reason for the problem, be it with the group buffer pools, the local buffer pools or with a particular object. And most important, it reveals and describes all possible solutions for a problem or tuning issue in plain English with phrases like, enlarge or decrease the pool size of Bpxx to .. , change this threshold to .., Re-allocate (shift) these objects from BP0x to BP0y. Hence the tool saves valuable DBA time that usually spent for analysis and ‘what if’ reasoning. BPA4DB2 generates the required ALTER statements and offers on demand an entire re-design of the pools setup including an object grouping proposal. BPA4DB2 justifies clearly its proposed measures using colored charts on measured values like ReReads, Distinct GetPages, DASD-IO, Average-IO, etc. Although BPA4DB2 offers the finest granularity of buffer pool related details its unique strength is its ability to give clear briefings for problem solution and tuning.
BPA4DB2 makes buffer pool tuning easy, it analyzes measurements, identifies tuning opportunities and provides guidelines and precise instructions for problem solution and tuning. However, permanent observation of a number of DB2 subsystems can occupy the time of a DBA or even of several DBAs. Therefore BPA4DB2 offers permanent automatic surveillance. The tool consists of two parts, the Host Component and the Workstation. The workstation is used to visualize the analysis results, to explain the findings and to present the recommendations for tuning and problem solution. The Host Component takes the measurements against the DB2 subsystems, creates a measurement file and invokes the Host Examination Component (HEC) of BPA4DB2. HEC inspects the measurement file and examines several issues like the percentage of ReReads (tuned or not), the Sequential Prefetch Threshold (SPTH), the Data Management Threshold (DMTH), the z/OS system paging rat, 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 measured DB2 system doesn’t adhere to the defined quality criteria an email alert is sent to the database administration together with the measurement file. A DBA will then open the measurement file under the workstation and receive all kind of support for analysis, understanding the problem and preparing the right counter measures.
Measurement Data Manager
Ongoing measurement at regular intervals ensures performance but leads to an increasing number of measurement data files. Therefore BPA4DB2 provides a DB2 based measurement manager. The condensed measurement data files are available in DB2 tables for comparison, trend analysis and error analysis.
Buffer Pool Performance and SQL Quality
Sufficient buffer pool performance saves IO and CPU time. However poor SQL statements can spoil a lot. BPA4DB2 now relates Getpage and IO to SQL statements and reveals that way CPU burners, hence it provides valuable information to application developers and performance specialists.
BPA4DB2 offers what DBAs look for.