New Brochure, New Features!   >> click for more...

BUFFERPOOLS
BUFFERPOOLs are areas of storage in which DB2 temporarily stores pages of table spaces and indexes. When an application program accesses a row of a table, DB2 retrieves the page containing that row and places the page in a buffer. If the needed data is already in a buffer, the application program does not have to wait for it to be retrieved from disk, significantly reducing the cost of retrieving the page. The relation of access times between access to storage and access to disk is about 106. In other words, access to a volume lasts million times longer than access to storage. It is as the difference between reaching to the cup of coffee on the desk and a order of a sack of coffee beans abroad.

Using a 'Monitor' for Buffer Pools?
Many organizations have monitors like Omegamon, Mainview, DB2PM, etc. These common monitors are beneficial in many respects but they do not display key DB2 information such as actual re-reads of a page within a measured interval or other information vital for optimal tuning. The numbers of those re-reads are essential for the evaluation of the buffer pool´s behavior, because reading from disk is much slower than reading from mainstorage. Therefore DB2 developers introduced cache pools and called them ”BUFFERPOOLs”. Buffer Pools are tuned in an optimal way if DB2 can satisfy all or at least most of its requests against its BUFFERPOOLs, instead of forwarding the request to a disk. If a re-read of the same page occurs, the space of the page in the pool was meanwhile needed for another page and has been overwritten. As result, the page must be read again from disk, with negative effects on the performance. Therefore the ”art” of BUFFERPOOL optimization is to identify, analyze and finally avoid re-reads. Common monitors do not show rereads

Tuning Process with BPA4DB2
The host component of BPA4DB2 monitors DB2's input/output behavior through IFCID commands (DB2 Instrumentation Facility). Measuring data is collected in regular intervals and is stored on the host. The user decides how often and at which times the measurement takes place (morning, evening, shift change, peak times). The collected data is reworked, compressed and transmitted to a JAVA workstation (a PC).

BPA4DB2's workstation component analyses the trace data collected by the host monitor and presents the results in clearly arranged graphs and informative tables. In particular the column "rereads" directly shows the potential for tuning. The user can individually choose different views of the presented information. Finally, BPA4DB2 is making propositions for the tuning of the buffer pools. PDF formatted reports with findings and recommendations are generated pool by pool, as well as reports for group buffer pools and summaries.

-top of page-

BPA4DB2 offers:

• Recommendation to enlarge BUFFERPOOLs
• Recommendation to scale down BUFFERPOOLs
• Suggestion to transfer objects to other BUFFERPOOLs
• Proposition to change thresholds to other values
• Suggestion to create a further BUFFER POOL with specific characteristics
• Identification of wrong placed objects.

Product outperforms the competitors
The main design goal for BPA4DB2 was to allow more precise approximations of BUFFERPOOL sizes and provide for continual passive monitoring for problems as the environment changes. Something only BPA4DB2 provides. Technology that is responsive to the needs of burdened DBA and Performance staff.

With older versions of BPA4DB2 it was already possible to estimate the “real” memory requirements when checking the “DISTINCT PAGES”. The number of GETPAGEs within one pool does not help to estimate the memory requirements, as long as the number of distinct pages is unknown. For example, 1 million GETPAGE within 10 minutes does not help to estimate the required pool size. If it’s known, that this million of GETPAGE operations is concentrated on 50.000 distinct pages, the estimation of the pool size is simple: 50.000 pages are enough to cache all the pages being required in a 10 minutes interval to avoid unnecessary I/O.

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 really look for.