--
ESAI - Enterprise Systems Associates, Inc.

Back to Applications & DB2 Solutions Products

Enterprise Systems Associates, Inc.

ESAI ProductsESAI ServicesESAI TrialsAbout ESAIContact ESAIESAI Home Page

BPA4DB2 DB2 Buffer Pool Tool Analysis and Sizing

BPA4DB2™ is a buffer pool analyzer that supports DB2® Administrators on IBM® z/OS® by analyzing the I/O behavior of DB2 subsystems and DB2 groups. This buffer pool tool generates proposals to improve buffer pool setup and does monitoring/alerting for continued health checking. BPA4DB2 consists of two parts, a host component which measures the I/O rates and a JAVA client which receives the compressed measurements from the host. BPA4DB2 is a DB2 buffer pool tuning tool that surpasses all others... better analysis, user interface, object placement suggestion, and better overall value.

DB2 version 11 and 12 upgrade and sizing support.

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.

-top of page-

With Version 3.0 more data, more expert analysis. The competitor: more simulation, older technology.

  • Distinct random pages: this is the number of distinct pages which came in by random access. Especially for index pools this is the most interesting number.
  • Distinct sequential pages: this number helps now to estimate the influence of sequential scans on objects, as sequential pages are required mostly once (no reuse).
  • Re-Reference Random Pages: Within BPA4DB2 these counters are used to calculate the target sizes of the buffer pools. These numbers now are also externalized to the DBA.
  • Re-Reference Sequential Pages: Sequential scans bring in pages, mostly not being reused. Minutes later, these pages are “stolen” by other pages, causing IO’s. A tablespace scan fills the buffer pool with a huge number of pages of one object, but the chance to reuse these pages is very small. This counter now helps the DBA to set the VPSEQ-Thresholds more exactly.

All these counters are now, like all other measurement data, available to the DBA, in BPA4DB2's screens and as MS EXCEL columns. Thus a user is able to write his own reports or to accomplish his own, specific purpose-built analysis.

Some Screen Snapshots from BPA4DB2:

BUFFERPOOL Overview

-top of page-

BUFFERPOOL GETPAGE

GETPAGE I/O GRAPH

-top of page-

WRITE INTEREST

REREADS

PDF FORMATTED REPORT

-top of page-

Buffer Pool Analyser Brochure

BPA4DB2 on UBS sister website

Click to demo or trial BPA4DB2

©Enterprise Systems Associates, Inc.
All trademarks are of their respective trademark holders including IBM, DB2,z/OS,IMS,VSAM, Omegamon which are of the IBM Corp.
.