BPA4DB2 was designed to make buffer pool tuning as simple as possible. The tool helps to identify tuning opportunities and provides guide lines for improvement measures. With BPA4DB2 database administration is unburdened from issuing IFCID and other commands, studying and analyzing piles of statistics, filling spreadsheets, etc., instead immediate results can be expected.
There are two basic types of i/o, physical and logical. Physical i/o is viewed as bad, expensive and slow, because i/o requests are made to devices, disks rotate, and eventually data is returned to the application. Physical i/o causes delays waiting for it to complete and consumes CPU time. Through proper use of areas in memory, called buffer pools, DB2 can substantially reduce CPU consumption, i/o expense, and elapsed time. This is reflected by reduced overall costs and improved user productivity.

When a getpage is issued the page can either be found in memory or it needs to be retrieved from the VSAM file of the tablespace. It is obviously better when the page is already in the pool. Buffer pool tuning basically means to decide for a reasonable number of pools, to choose applicable sizes for those pools, to make sure that there is proper allocation of objects, i. e. tablespaces and indexspaces, to those pools, and last but not least, to set the right threshold values for each pool, in order to achieve a high socalled hit rate, which means to accomplish, that most of the requests are satisfied through pages in memory.

In the example below we see a rather good overall hit rate of 99%, see the field "Overall Hit %" in the upper part of the window. It is the percentage of pages found in the buffer pools during the observation period. The higher the hit ratio the better the performance. But, there is still tuning potential. See the column "RR%" in the main table, it shows the percentage of re-reads, displayed for every buffer pool. A page not available in the pool must be read from the file, there is nothing wrong with this, but we should avoid re-reads. Occur­rences of re-reads indicate, that pages have quite recently been in the pool and memory was needed, so they were overwritten, and must therefore shortly after fetched again. Look at buffer pool 9 (BP9), 114.104 i/o's occurred, 12% of them were probably unnecessary. Preventable reads from the storage devices are worsening the performance and should be avoided. BPA4DB2 detects unnecessary re-reads and explains how to prevent them.

There is a column “HPSize” which shows the size of the hiper pools. As you know hiper pools are not supported with DB2 v8, they must be removed. The common formula to remove the hiper pools is “new_buffer_pool_size = VPSize + HPSize”. This may cause paging if it is not enough memory available. BPA4DB2 calculates the optimal buffer pool sizes from it's measurement data, hiper pools will vanish.

Analyses of the i/o behavior is one thing, precise statements for improvement another. The window "Expert Analysis" below provides some useful hints and statements. The window was opened for buffer pool 9, it states "Buffpool seems to be to small, pool size should be 102523 Pages". Additionally the Vertical Defered Write Threshold (VDWQT) should be reduced to 32 pages, in order to write changed pages only once within one minute, which is the recommended minimum for this threshold. This triggers writes on a regular base, but avoids writing the same page twice.

BPA4DB2 analyzes each pool and provides recommendations to reduce or enlarge the pool, to change threshold values, or to shift objects to another pool.

To see more details about a specific buffer pool click on the icon in the left frame, the navigation frame, of the primary window. In our example we clicked on BP9 and received the "BufferPoool Details: .." window, see below. The upper part of the window displays on the left side the threshold settings for buffer pool 9, and aside a pie-chart which depicts the i/o types occurred against this buffer pool during the measurement period. It is mainly a "random pool", depicted by the blue color, but there was as well some sequential access observed, see the green color.

The table at the bottom of the window shows further details on database and object level, see columns "DB" and "Page Set". We see the getpages, the i/o count, etc., and again the re-reads per pageset. Most of the access' to the pageset "XMDPAY_EARNINGS" is sequential, it should be considered to move this pageset into a sequential pool. There are four buttons on the bottom of the window, one of them is the button labeled "MisMatch". Clicking this button displays a report showing all wrong placed objects.

The pagesets "XM_TAX_BALANCE" and "XM_EARNINGS_BAL" cause to many re-reads and are good tuning candidates.

BPA4DB2 checks as well group buffer pools. To see the result of the analysis of a group buffer pool go to BPA4DB2's Primary Window and open the sub tree of the concerning pool and click on the icon labeled "GroupBP". In our example, see the "Group Bufferpool Statistics" below, we selected group buffer pool 9. In the upper part we see the current threshold values and some numbers from the measurement. More important are the statements beneath the line, really crucial are the statements in red color.

The hit ratio of group buffer pool 9 is 1%, a value which is not acceptable. The other red line states, that castout, the write operations from the group buffer pool to the file, failed 125 times during the examined period. This value should be zero, so this pool has to be enlarged.

BPA recalculates the size of the group buffer pool and recommends settings for CLASST and GBPOOLT and the checkpoint interval.

This was a short inspection of some windows of BPA4DB2. And the tool has even more nice functions: It generates the buffer pool ALTER STATEMENTs, writes a report in popular PDF format - containing all details and a management summary, and it optionally outputs the entire measurement data to external editors or spreadsheets.

The product largely consist of two components, the z/OS measurement job and the workstation, a PC program written in JAVA. The z/OS job gathers sample data from the DB2 subsystems, processes, compresses and stores it in sequential files. The user transfers the measurement files to the PC and opens them with the BPA4DB2 workstation program.

BPA4DB2 – The strongest points

• Immediate detection of tuning opportunities

• Clearly arranged results of measurement

• Analysis and recommendation

• Selectable tuning rules, SAP, VLDBs, etc.

• ALTER Statement Generation

• Improvement or redesign

• PDF Reports - Details, Summary, Recommendations

• Data export - measurement data is available in MS ACCESS and EXCEL format

• Modifiable examples based on EXCEL.

Bufferpools einstellen mit BPA4DB2

BPA4DB2 besteht aus zwei Komponenten, dem Host-Monitor und dem JAVA-Client. Mittels IFCID Kommandos (DB2 Instrumentation Facility) erstellt das Hostprogramm Messdaten und legt sie auf dem Host ab. Der Benutzer legt fest, zu welchen Zeitpunkten die Messungen stattfinden sollen: morgens, abends, Schichtwechsel, Spitzenzeiten. Die Messdaten werden noch bearbeitet und komprimiert und dann auf die Workstation übertragen (PC).

Die Workstation-Komponente analysiert diese Daten und präsentiert sie in übersichtlich geordneter Form, graphisch und tabellarisch. Insbesondere zeigt die Spalte RE-READS das Tuning-Potential direkt an. Der Benutzer kann unter verschiedenen Darstellungen die für ihn am besten geeigneten auswählen. Die Datentabellen können zu anderen Programmen (Tabellenkalkulation) übertragen werden.

Die Reportfunkion erstellt PDF-formatierte Berichte zu den Ergebnissen der Analyse und gibt Empfehlungen und Tipps.

BPA4DB2: was steht in den Reports?

• Empfehlungen zum Vergrößern von BUFFERPOOLs
• Empfehlungen zum Verkleinern von BUFFERPOOLs
• Empfehlungen, bestimmte Objekte in andere BUFFERPOOLs zu verschieben
  
• Vorschläge zum Anpassen bestimmter Thresholds
• Vorschläge, weitere Bufferpools mit bestimmten Eigenschaften anzulegen
• Identifizierung falsch platzierter Objekte
  

Version 2.00

Das vorrangige Ziel bei der Gestaltung der Version 2.00 war die Verbesserung der Berechnung der BUFFERPOOL-Größen für den bervorstehenden Übergang nach DB2 Version 8. Bekanntermaßen werden HIPERPOOLS unter DB2 Version 8 nicht mehr unterstützt. Die übliche Regel für die Berechnung der neuen BUFFERPOOL-Größe

newBufferpool size = size of primary poos + size of hiperpools

wird in den meisten Fällen zu Problemen mit dem z/OS Memory führen. HIPERPOOLS werden anders allokiert als PRIMARY POOLS, sie werden nur angelegt, wenn z/OS genügend Memory zur Verfügung hat. Vor allem Anwender mit großen HIPERPOOLS werden beim Übergang nach Version 8 Probleme bekommen, wenn sie ihr BUFFERPOOL-Konzept nicht überdenken. 

Schon mit der Version 1.0 von BPA4DB2 war es möglich den 'realen' Speicherbedarf anhand der DISTINCT PAGES zu schätzen. Die Anzahl der GETPAGEs innerhalb eines Pools genügt nicht zur Ermittlung des Speicherbedarfs, man braucht die Anzahl der DISTINCT PAGES. Ein Beispiel; das Vorkommen von einer Million GETPAGEs innerhalb von 10 Minuten gibt noch keinen Hinweis auf die benötigte Poolgröße. Erst wenn bekannt ist, dass diese eine Million Seitenanforderungen sich auf z. B. 50.000 'distinct pages' verteilen, ist die Frage beantwortet - 50.000 Seiten reichen aus, um unnötige I/O zu vermeiden.

Die Version 2.00 macht dem Anwender zusätzliche Variablen zugänglich:

• Distinct random pages: Das ist die Anzahl der distinct pages die für 'random access' ermittelt wurde. Vor allem bei Index-Pools ist diese Variable von Interesse.

• Distinct sequential pages: Diese Variable hilft die Auswirkung von sequentiellen 'scans' zu beurteilen, bei sequentiellem Zugriff findet in der Regel keine Wiederverwendung der Seite statt.

• Re-Reference Random Pages: Zeigt an wie oft 'random pages' benutzt wurden. BPA4DB2 berechnet damit die Zielgröße der Random-Pools.

• Re-Reference Sequential Pages: Sequentielles Lesen fordert Seiten an, die i. R. nicht mehr gebraucht werden. Diese Variable hilft bei der Bestimmung des VPSEQ Schwellwerts.

Wie alle anderen Variablen stehen auch die neuen innerhalb BPA4DB2 zur Verfügung und können in eine Tabellenkalkulation überführt werden. Der Anwender kann also auch eigenen Auswertungen und Berichte erstellen.

Es wurden weitere Beispiele in MS-Excel Format erstellt. In der obigen Abbildung Total GetPage sehen Sie eines zum Thema Wiederverwendung von Seiten 'Re-Referenced Pages'. In BUFFERPOOL BP3 wurden nur 50% der Pages noch einmal genutzt:

Noch einige Hardcopies aus BPA4DB2:

Bufferpool-Übersicht

Bufferpool Getpage

Getpage I/O Grafik

Write Interest Grafik

Re-Reads

PDF-formatierter Report