Db2 Pools Test

A buffer pool is an area of memory into which database pages are read, modified, and held during processing.

Buffer pools improve database performance. If a needed page of data is already in the buffer pool, that page is accessed faster than if that page had to be read directly from disk. The database manager has agents whose tasks are to retrieve data pages from disk and place them in the buffer pool (prefetchers), and to write modified data pages from the buffer pool back to disk (page cleaners).

The reading and writing of data pages to and from disk is called disk input/output (I/O). Avoiding the wait associated with disk I/O is the primary way to improve the performance of the database. How you create the buffer pool, and configure the database manager and the agents associated with the buffer pool, controls the performance of the database. Through SQL and configuration parameters, you can control the size of the buffer pool, the number of prefetchers and page cleaners that move data pages into and out of the buffer pool, the size of the data pages, and the number of data pages that can be moved at one time.

The statistics reported by the Db2 Pools test help administrators analyze the usage of the buffer pools, and provides them with useful pointers to fine-tune the configuration of the buffer pools.

Target of the test : A DB2 database server

Agent deploying the test : An internal agent/remote agent

Outputs of the test : One set of results for every database on the DB2 database server that is currently active

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. user - Specify the name of the user who has any of the following privileges to the specified DATABASE: SYSADM or SYSCTRL or SYSMAINT or SYSMON. You can create a separate user on the OS hosting the DB2 server for this purpose, and assign any of the aforesaid privileges to that user. The steps for the same are detailed in the Creating a Special User for Monitoring DB2. password - Enter the password of the specified USER in the PASSWORD text box. confirm password – Confirm the password by retyping it here. database - Specify the name of the database on the monitored DB2 server to be used by this test SSL- If the target database server is SSL-enabled, then set the SSL flag to Yes. If not, then set the SSL flag to No. Trust Store file name- The trust store file contains certificates from trusted Certificate Authorities (CAs). These certificates are used by eG agent to verify the authenticity of servers hosting DB2 UDB and establish a secure connection with the server using SSL. Specify the filename for Trust store file in Trust Store file name text box. Trust Store Password- The trust store password is the passphrase or key used to encrypt and decrypt the trust store file. This password is required by the eG Agent when it needs to access the trust store file to establish secure connections. Specify the password in Trust Store password text box. Confirm Password- Confirm the Password by retyping it in the Confirm Password text box.

Measurements made by the test
Measurement	Description	Measurement Unit	Interpretation
Buffer pool hit ratio:	Indicates the percentage of the requested pages that is readily available in the memory without doing disk I/O.	Percent	This measure is an overall indicator of how well the buffer pool is performing. Lower values indicate that more physical I/O is happening than logical. Since physical I/O costs more, maintaining higher buffer hit ratio is desired. Typically a hit ratio over 90% shows that the buffer pool is performing well. If this measure is consistently low, increase the size of the buffer pool by increasing BUFFPAGE configuration value.
Buffer Pool Hit Ration (Data):	Indicates the the percentage of the requested data pages that is readily available in the memory without doing disk I/O.	Percent	This measure is an indicator of how well the buffer pool is performing for the data page requests. Lower values indicate that more physical I/O is happening than logical. Since physical I/O costs more, maintaining higher buffer hit ratio is desired. Typically a hit ratio over 90% shows that the buffer pool is performing well. If this measure is consistently low, increase the size of the buffer pool by increasing BUFFPAGE configuration value.
Buffer Pool Hit Ratio (Index):	Indicates the percentage of the requested index pages that is readily available in the memory without doing disk I/O.	Percent	This measure is an overall indicator of how well the buffer pool is performing. Lower values indicate that more physical I/O is happening than logical. Since physical I/O costs more, maintaining higher buffer hit ratio is desired. Typically a hit ratio over 90% shows that the buffer pool is performing well. If this ratio is really low and the overall ratio is relatively high, then break the index(s) out into their own tablespace/buffer pool.
Pre Fetch Ratio:	The ratio of asynchronous reads to synchronous reads. The value indicates how effectively DB2 database manager is populating the buffer pools through the use of prefetchers.	Percent	High value indicates more asynchronous I/O is happening than synchronous. The value can be used to tune the num_ioservers configuration parameter.
Log clean percentage	Indicates the percentage of times a page cleaner was invoked because the logging space used had reached a predefined criterion for the database.	Percent	If this value is high (say > 40%), this could mean that page cleaners are constantly being utilized to clean the log and aren’t available for other page cleaning activities, hampering performance. On the other hand, if the value is low, (say < 10%) then the page cleaners aren’t being triggered as often for this activity. this means that they would be more available for the other types of page cleaning activities, which is great for buffer pool performance.
Dirty page clean percentage	Indicates the percentage of times a page cleaner was invoked because a buffer pool had reached the dirty page threshold criterion for the database.	Percent	The threshold is set by the chngpgs_thresh configuration parameter. It is a percentage applied to the buffer pool size. When the number of dirty pages in the pool exceeds this value, the cleaners are triggered. If this value is set too low, pages might be written out too early, requiring them to be read back in. If set too high, then too many pages may accumulate, requiring users to write out pages synchronously.
Victim cleans percentage	Indicates the percentage of times the page cleaner(s) were triggered to oust a victim page from the buffer pool. A victim page is a clean or dirty page in the buffer pool that is removed simply because DB2 needs to make room for incoming pages. If a victim page is a dirty page then the information must be written out to disk. Any page that is removed will most likely cause more physical I/O to occur in order to retrieve it again at later time when DB2 is ready to use it.	Percent	If the ratio is higher then the above two then that is typically a good indicator that the buffer pool needs to be larger since there never seems to be enough room for new pages to be brought in. This could also be a sign that dirty pages are staying in the buffer pool too long which could mean that the changed pages threshold (CHNGPGS_THRESH) is set too high. Even the SOFTMAX parameter could be set too high and too much of the changed pages that are logged are not getting flushed out to make way for new pages. If this ratio is low, it may indicate that you have defined too many page cleaners. If your chngpgs_thresh is set too low, you may be writing out pages that you will dirty later. Aggressive cleaning defeats one purpose of the buffer pool, that is to defer writing to the last possible moment.
Catalog Cache Hit Ratio:	Indicates the percentage of time the requested information for table descriptor or authorization was readily availble in catalog cache without requiring to perform disk I/O.	Percent	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.
Package Cache Hit Ratio:	The package and section information required for the execution of dynamic and static SQL statements are placed in the package cache as required. This information is required whenever a dynamic or static statement is being executed. The ratio indicates the effectiveness of package cache hit ratio.	Percent	If the hit ratio is high (more than 80%), the cache is performing well. A smaller ratio may indicate that the package cache size (pckcachesz) should be increased.