Db2 Buffer Pools Test

This test, executed by an internal agent, tracks various statistics pertaining to the buffer pool in a DB2 UDB database. The details of the test are provided below:

Target of the test : A DB2 database server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every DB2 database server being monitored

Configurable parameters for the test
TEST PERIOD – How often should the test be executed Host – The IP address of the DB2 server Port – The port number through which the DB2 server communicates. The default port is 50000.

Measurements made by the test
Measurement	Description	Measurement Unit	Interpretation
Buffer pool hit ratio:	Indicates the effectiveness of the buffer pool. This indicates the percentage of the requested data blocks that is readily available in the memory without doing disk I/O.	Percent	The more a data block requested is found in the buffer pool, the better it is for performance since it avoids unnecessary disk input and output. The configuration of the buffer pool is the single most important tuning area, since most data manipulation for connected applications, excluding large objects and long field data, takes place here. If this measure is low (less than 90% for decision support, less than 50% for query-only and online transaction procession), increase the size of the buffer pool by increasing BUFFPAGE As a rule, the buffer pool should be as large as possible. Increase BUFFPAGE until you see an increase in swapping (indicated by an operating system monitor).
Buffer pool hit ratio for Index:	Indicates the effectiveness of the buffer pool. This indicates the percentage of the requested index blocks that is readily available in the memory without doing disk I/O.	Percent	The more data found in the buffer pool, the better it is for performance since it avoids unnecessary input and output. The configuration of the buffer pool is the single most important tuning area, since most data manipulation for connected applications, excluding large objects and long field data, takes place here. If this measure is low (less than 90% for decision support, less than 50% for query-only and online transaction procession), increase the size of the buffer pool by increasing BUFFPAGE. As a rule, the buffer pool should be as large as possible. Increase BUFFPAGE until you see an increase in swapping (indicated by an operating system monitor.
Catalog cache hit ratio:	Indicates the percentage of catalog cache hit ratio. This indicates the percentage of the requested catalog blocks that is readily available in the memory without doing disk I/O.	Percent	This measure includes both successful and unsuccessful accesses to the catalog cache. The catalog cache is referenced whenever a table, view, or alias name is processed during the compilation of an SQL statement. If the ratio is greater than 80%, then the catalog cache is performing well. A smaller value indicates that the catalog cache size should be increased by tuning the parameter CATALOGCACHE_SZ in the database configuration. The value may be low immediately following the first connection to the database. The execution of Data Definition Language (DDL) SQL statements involving a table, view, or alias will evict the table descriptor information for that object from the catalog cache causing it to be re-inserted on the next reference. Therefore, the heavy use of DDLs may also increase the value of the measure.