Top Ten Db2 Performance Tips – No. 9 Regular Maintenance and Housekeeping

When thinking about the performance characteristics of your Db2 databases and applications keep in mind the routine maintenance that can help or hinder your performance. Regular maintenance and housekeeping tasks are essential for ensuring the optimal performance and stability of your Db2 environment. 

By performing routine maintenance activities, database administrators can proactively address performance degradation, optimize query execution, and maintain a healthy database environment. In today’s post, we will highlight the importance of regular maintenance tasks and discuss key activities that contribute to database performance.

Index Maintenance

Indexes play a crucial role in query performance, as they facilitate quick data retrieval. Over time, indexes can become fragmented, leading to increased disk I/O and decreased query performance. Regularly reorganizing indexes helps eliminate fragmentation and enhances query execution efficiency. By scheduling index reorganization tasks based on the fragmentation level and database activity, administrators can maintain optimal index performance and minimize the impact of fragmentation on query response times.

You should also monitor index usage and consider removing any unused indexes. You can identify unused indexes relatively easily using the LASTUSED information in the RTS SYSINDEXSPACESTATS table in the Db2 Catalog.

Tablespace Reorganization

As your database grows and data is modified or deleted, storage space can become fragmented, leading to suboptimal performance. Regularly reorganizing database structures helps to consolidate data, reclaim unused space, and optimize storage allocation. Reorganizing your tablespaces can improve I/O performance, reduce disk fragmentation, and enhance overall system efficiency. By performing regular database reorganizations based on data growth patterns and workload characteristics, administrators can maintain a well-organized and performant database environment.

Up-to-Date Statistics

Accurate and up-to-date statistics are essential for the Db2 optimizer to make informed decisions on query execution plans. As data in the database changes, statistics need to be updated to reflect the current distribution of data. Regularly updating statistics (using the RUNSTATS utility) ensures that the optimizer has the most accurate information to generate optimal execution plans. By analyzing data distribution patterns and scheduling statistics updates accordingly, administrators can improve query performance and avoid suboptimal query plans caused by outdated statistics.

For packages using static SQL, taking advantage of updated statistics requires rebinding. However, you may not want to rebind every time you run RUNSTATS unless application performance is suffering.

Routine Backups

Regular backups are vital for data protection and disaster recovery. Performing routine database backups not only safeguards the integrity of the database but also contributes to performance optimization. In the event of a failure or data loss, having a recent backup minimizes the recovery time and ensures business continuity. DBAs should establish a backup schedule based on the criticality of the data, recovery time objectives (RTOs), and workload requirements. 

And do not forget to regularly test your recovery plans and capabilities. Too often DBAs focus on backups at the expense of recovery… and backups are needed primarily to enable recovery, right?

Transaction Log Management

And let’s not forget the transaction logs! Logs play a critical role in ensuring data consistency and recoverability. Regularly monitoring and managing the transaction log space helps prevent log-related performance issues and ensures uninterrupted database operations. Activities such as transaction log backups, log file sizing, optimizing log offloading, and log file utilization monitoring are crucial for maintaining optimal transaction log performance and managing log space efficiently.

Buffer Pool Review

Regularly monitoring the efficiency of your Db2 buffer pools is important to ensure that you are achieving expected hit ratios and performance. As new applications are added, your amount of data increases, and access patterns change it will be necessary to adjust buffer pool sizes and parameters to optimize performance.

System Maintenance

Be sure to keep your Db2 subsystem updated with recent maintenance. You can use the -DIS GROUP command, even if you are not running data sharing, to display the current status of your Db2 software.

This command returns the message DSN7100I which is documented at https://www.ibm.com/docs/en/db2-for-zos/13?topic=messages-dsn7100i. It will show you the current version and function level, the current code level, and also the highest possible function level you can activate for your Db2 environment.

Be sure too to follow the IBM recommended preventive maintenance strategy to apply the appropriate maintenance using the IBM supplied RSU (Recommended Service Upgrade).

Summary

By incorporating these regular maintenance tasks into your database administration and management routine, DBAs and systems programmers can optimize performance, prevent performance degradation, and mitigate potential issues. Automation tools and scripts can streamline these maintenance activities and ensure consistency and timeliness in execution.

Top Ten Db2 Performance Tips - No. 7 Configuration Parameters and Tuning

Configuration parameters play a crucial role in optimizing the performance of an IBM Db2 database environment. By understanding the impact of these parameters and tuning them appropriately, database administrators can fine-tune the system to achieve optimal performance. In this blog post, we will explore key configuration parameters and discuss recommended settings and tuning options.

The first thing to understand is that the actual parameters, their specific names, and how they are implemented and modified will differ between Db2 LUW and Db2 for z/OS. This blog post will talk mostly in generalities but the ideas resonate across all Db2 database management systems (and, indeed, even across other relational DBMS platforms).

The first type of parameters are used to configure Database Manager options. The database manager configuration parameters govern the overall behavior of the Db2 instance. Parameters such as database shared memory, lock list, and log buffer size have a significant impact on performance. Adjusting the shared memory size to accommodate the workload, appropriately sizing the lock list to handle concurrent transactions, and setting an optimal log buffer size based on the transaction rate are essential considerations.

Next we have the Buffer Pool parameters. Buffer pools act as a cache for frequently accessed data pages, reducing disk I/O and improving query performance. The size and configuration of buffer pools are critical for efficient memory utilization. Allocating an appropriate amount of memory to buffer pools based on workload characteristics and monitoring buffer pool hit ratios helps optimize performance. 

Here are some crucial considerations for configuring Db2 for z/OS buffer pools:

• Data Access Patterns: Understand the access patterns of your applications. Buffer pool configuration should align with the types of queries and transactions that are frequently executed. If your workload involves mostly random access, a larger buffer pool may be required. If it involves mostly sequential access, specific settings to favor sequential reads may be required.
• Buffer Pool Sizing: Determine the appropriate size for each buffer pool. Consider the amount of available memory on your system, the size of the database objects, and the anticipated workload. Larger buffer pools can improve performance, but it's essential to balance memory usage across other system components.
• Multiple Buffer Pools: Db2 for z/OS allows you to create multiple buffer pools. Consider segregating data based on access patterns or table spaces. For example, you could use separate buffer pools for frequently accessed tables and indexes versus less frequently accessed ones.
• Page Sizes: Db2 for z/OS supports various page sizes. Ensure that the buffer pool page size matches the page size of the objects being cached. Using the correct page size can reduce internal I/O operations and improve efficiency.
• Thresholds and Actions: Set appropriate thresholds for buffer pool thresholds, such as the high water mark and low water mark. Define actions to be taken when these thresholds are crossed, such as dynamically adjusting the buffer pool size or issuing alerts.
• Asynchronous I/O: Enable asynchronous I/O to allow Db2 to overlap I/O operations with processing. This can improve performance by reducing wait times caused by synchronous I/O operations.
• Monitor and Analyze: Regularly monitor buffer pool performance using Db2's monitoring tools. Analyze statistics and performance metrics to identify areas for improvement or potential issues.
• Buffer Pool Replacement Policies: Understand and configure the buffer pool replacement policies (e.g., LRU, MRU, FIFO) based on your workload characteristics. Different policies prioritize different data pages for retention in the buffer pool.
• Maintenance: Regularly review and adjust buffer pool configurations based on changing workloads, data growth, and hardware upgrades.

Also, Db2 for z/OS offers the following buffer pool tuning “knobs” that can be used to configure buffer pools to the type of processing they support:

• DWQT (deferred write threshold) –  expressed as a percentage of the virtual buffer pool that might be occupied by unavailable pages. When this threshold is reached Db2 will start to schedule write I/Os to externalize data. The default is 30%, which may be too high for many shops.

• VDWQT (vertical deferred write threshold) – basically the same as DWQT, but for individual data sets. The default is 10%, which once again is quite likely to be too high for many shops.

• VPSEQT (sequential steal threshold) – expressed as a percentage of the buffer pool that can be occupied by sequentially accessed pages. Tune buffer pools for sequential access such as scans and sorting by modifying VPSEQT to a larger value. The default is 80%.

• VPPSEQT (parallel sequential steal threshold) – the sequential steal threshold for parallel operations; the default value is 50%.

Additionally, adjusting parameters like page cleaning and prefetch size can further enhance buffer pool performance.

Remember that buffer pool configuration is not a one-size-fits-all approach. It requires a deep understanding of your specific workload, database design, and hardware capabilities. Regular monitoring and tuning are essential to maintain optimal performance over time.

There are other Memory settings that are nearly as critical as buffer pools, too. One example is the Db2 for z/OS EDM pool. EDM stands for Environmental Descriptor Manager. The EDM pool is used by Db2 to control programs as they execute. It will contain structures that house the access paths of the SQL statements for running programs. 

Actually, the EDM pool is a group that encompasses several other pools, all of which can be configured separately, including skeleton pools, DBD pool, sort pool, and RID pool. The RID pool is used by DB2 to sort RIDs (record identifiers) for List Prefetch, Multiple Index Access, and Hybrid Join access paths.  RID pool failures can cause performance degradation as alternate access paths are invoked, such as scans, and the CPU invested up to the point of the failure is wasted.

Db2 for z/OS also enables a Dynamic Statement Cache to be configured and tuned. It permits dynamic SQL prepare information to be cached in memory to optimize dynamic SQL. 

Another consideration to keep in mind is Query Parallelism, which refers to the ability of Db2 to execute multiple query tasks concurrently, leveraging multiple CPU cores. Enabling parallelism can significantly speed up query execution for resource-intensive workloads. The degree of parallelism, controlled by parameters like DFT_DEGREE and NUM_INIT_AGENTS, should be set based on the available hardware resources and workload characteristics. Careful tuning of these parameters ensures optimal parallel query execution without overloading the system.

Tuning Db2 Sort operations is also critical. During query execution sorting can consume significant memory resources. The sort heap is responsible for allocating memory for sort operations. Tuning the Db2 LUW SORTHEAP parameter to an appropriate size based on the complexity of sort operations and available memory helps avoid  excessive disk I/O and improves query performance. Regular monitoring and adjustment of SORTHEAP ensure efficient memory utilization for sort operations.

Statement Concentration is another configuration parameter to consider. It controls the consolidation of multiple SQL statements into a single unit of work. Enabling statement concentration reduces the overhead associated with parsing and optimizing individual statements, improving overall performance. By setting appropriate values for parameters like STMT_CONC and STMTHEAP, administrators can optimize statement concentration based on the workload and resource availability.

Connection and Maximum settings are also crucial to consider. For example, in Db2 for z/OS MAXDBAT sets the maximum number of database agents and Db2 LUW uses MAXAPPLS to define the maximum number of concurrent application connections. And an example of a setting that can control maximums is DSMAX (Db2 for z/OS) that can be set between 1 and 200000; it controls the maximum number of underlying data sets that Db2 can have open at any point.

It is important to note that tuning these configuration parameters should be done carefully and based on workload analysis and performance monitoring. The optimal settings may vary depending on the specific requirements and characteristics of the database environment. Regular monitoring of performance metrics, workload patterns, and system behavior is crucial to identify areas for tuning and ensure continued optimization.

Summary

In conclusion, configuration parameter tuning is a critical aspect of optimizing the performance of an IBM Db2 database. By understanding the impact of key parameters and adjusting them based on workload characteristics, administrators can fine-tune the system for optimal performance. 

We have only taken a high-level look at several configuration considerations and parameters in this blog post. But keep in mind that the configuration parameters and their settings contribute to an efficient and high-performing Db2 environment. Regular monitoring and tuning of these parameters help achieve optimal performance and responsiveness in the database system.

Top 10 Db2 Performance Tips - No. 4 Effective Memory Configuration

The memory configuration of an IBM Db2 environment plays a critical role in determining the performance of applications accessing data, as well as the overall efficiency of the subsystem and DBMS. By properly allocating and managing memory resources, DBAs and systems programmers can significantly enhance the system's responsiveness and query execution speed.

One of the primary areas of memory configuration is the allocation of buffer pools, as well as other pools of memory. Db2 for z/OS uses memory for buffer pools, the EDM pool, RID pool and sort pools to cache data and structures in memory. The better memory is allocated to these structures, the better Db2 and applications that access it will perform. 

Buffer pools act as a cache for frequently accessed data pages, reducing disk I/O and improving query performance. Allocating an appropriate amount of memory to buffer pools is crucial to ensure that frequently accessed data remains in memory, readily available for query processing. By monitoring workload patterns and adjusting the buffer pool sizes accordingly, database administrators can optimize memory utilization and minimize disk I/O.

When allocating Db2 buffer pools, keep these rules of thumb in mind:

• Don't allocate everything to a single buffer pool (e.g., BP0); use a multiple buffer pool strategy.
• Explicitly specify a buffer pool for every table space and index. Don't simply let Db2 choose a default buffer pool by failing to specify one.
• Isolate the Db2 Catalog in BP0 (and BP8K0 and BP16K0 ); put user and application Db2 objects into other buffer pools.
• Consider separating indexes from table spaces with each in their own dedicated buffer pools.
• Consider isolating heavily hit data into its own buffer pool to better control performance.
• Consider isolating sorts into a single buffer pool and tuning it for mostly sequential access (e.g. BP7).
• Consider separating Db2 objects into separate buffer pools that have been configured for sequential verses random access.

Forget about trying to follow a cookie-cutter approach to buffer pool management. Every shop must create and optimize a buffer pool strategy for its own data and application mix. DB2 offers several buffer pool tuning "knobs" that can be used to configure virtual buffer pools to the type of processing they support. The following parameters all can be changed using the ALTER BUFFERPOOL command:

DWQT – this value is the deferred write threshold; it is expressed as a percentage of the virtual buffer pool that might be occupied by unavailable pages. When this threshold is reached Db2 will start to schedule write I/Os to externalize data. The default is 30%, which is likely to be too high for most shops.

VDWQT – this value is the vertical deferred write threshold; it is basically the same as DWQT, but for individual data sets. The default is 10%, which once again is quite likely to be too high for many shops.

VPSEQT – this value is the sequential steal threshold; it is a expressed as a percentage of the virtual buffer pool that can be occupied by sequentially accessed pages. Tune buffer pools for sequential access (such as scans and sorting) by modifying VPSEQT to a larger value. The default is 80%.

VPPSEQT – this value is the sequential steal threshold for parallel operations; the default value is 50% of VPSEQT.

VPXPSEQT – this value is assisting parallel sequential threshold; it is basically the VPPSEQT for operations from another Db2 subsystem in the data sharing group.

You can tune these parameters, as well as the size of the buffer pools, to accommodate the usage patterns of your Db2 applications. 

Consider modifying the deferred write threshold parameters to enable trickle write; that means lower values that will cause changed data to be written to disk more frequently, instead of waiting for a system checkpoint to occur. 

Furthermore, think about modifying the sequential steal thresholds for the type of data being buffered; if that data is mostly sequentially accessed, then increase these thresholds… if the data is mostly randomly accessed, then decrease these thresholds. Of course, these are basic, high-level guidelines that you will need to study before adjusting them at your shop.

The PGSTEAL parameter also can be adjusted to modify the manner in which the buffer pool steals pages when new data arrives and there is no space for it. There are three options: 

• LRU, 
• FIFO, and 
• NONE. 

The typical option is LRU, or least recently used. This will cause the oldest pages (in terms of when they were last accessed) to be stolen before newer pages. An alternate approach is FIFO, or first in/first out. With this approach there is no need for Db2 to monitor when the data was last accessed to determine which the least-recently pages. FIFO can reduce CPU usage (no LRU algorithm needed) and works well if the data is read once and never accessed again. The final option, NONE, is a special case to be used when a buffer pool is large enough to hold all of the data assigned to it so no page stealing is needed. When NONE is specified, Db2 will pre-load the buffer pool when the objects are opened, basically creating an in-memory area for the data. 

You can also use the PGFIX parameter to fix buffer pool pages in real storage. Doing so avoids the processing time that DB2 needs to fix and free pages for every I/O operation. This can reduce CPU for bufferpools involved in very intensive I/O applications.

Other Memory Considerations

In addition to buffer pools, Db2 uses memory for other purposes. The first we will examine is the EDM pool. EDM stands for Environmental Descriptor Manager. The EDM pool is used for caching internal structures used by Db2 programs. This includes DBDs, SKCTs, CTs, SKPTs, and PTs. It also includes the authorization cache for plans and packages, as well as the cache for dynamic SQL mini-plans.

Although it is common to refer to the EDM pool in the singular, Db2 actually breaks the EDM pool into separate pools for DBDs, for the dynamic statement cache, and for the program elements (CTs, SKCTs, PTs, SKPTs). Tuning the size of these structures to facilitate the processing requirements of your applications is crucial to ensuring optimal performance.

As a general rule of thumb, shoot for an 80 percent hit rate with the EDM pools; this means that only one out every five times should a structure need to be loaded from disk into the EDM pool.

Db2 also uses a pool to help with specific types of access paths called the RID pool. The RID pool is used by Db2 to store RIDs (record identifiers) for List Prefetch, Multiple Index Access, and Hybrid Join access paths.  RID pool failures can cause performance degradation as alternate access paths are invoked, such as scans, and the CPU invested up to the point of the failure is wasted. Not to mention that the scan usually will not perform as well as an indexed access!

Another aspect of memory configuration is sort memory. Sort operations are commonly performed during query execution, such as order by, group by, or distinct operations. Allocating sufficient memory for sort operations reduces the need for temporary disk storage, which can significantly impact query performance. It is important to allocate an appropriate amount of memory for sort operations based on the workload requirements, ensuring efficient sorting and minimizing disk I/O. Failure to provide sufficient memory for sorts can cause performance degradations can impact elapsed times dramatically and sort failures can terminate a statement.

In addition to specific memory allocations, it is important to consider the overall memory availability and system-wide settings. Ensuring that Db2 has access to an adequate amount of system memory prevents excessive swapping or paging, which can severely degrade performance. Adjusting system-wide memory parameters, such as the maximum memory target, can help fine-tune the overall memory allocation for Db2.

Regular monitoring of memory usage and performance metrics is crucial for effective memory configuration. By analyzing memory-related statistics and performance indicators, DBAs and performance analysts can identify potential bottlenecks or areas where memory resources may be over or underutilized. Proactive monitoring allows for timely adjustments to memory configuration to optimize performance.

Summing It Up

By allocating memory resources efficiently, including buffer pools, EDM pools, sort memory, and so on, you can minimize disk I/O, reduce contention, and enhance query execution speed. Regular monitoring and tuning of memory settings based on workload patterns and system-wide considerations contribute to a well-optimized Db2 environment with improved responsiveness and overall performance.

Finally, remember that tuning the memory structures of Db2 is in-depth subject that cannot be adequately covered in-depth in a blog post such as this. So, study those IBM Db2 manuals - and learn by doing. 

 

DB2 for z/OS Coupling Facility Sizing

Just a very brief blog post today to inform you about something that I ran across doing some web research today... and that is a free, web-based Coupling Facility sizing tool from IBM named CFSIZER. 

The CFSIZER tool will connect to your live server to get information. You will need to input some values and some estimation of the type of workload for your system and CFSIZER will output its recommendations.

If you are struggling with your Data Sharing environment this might be a good place to start if you don't have any tools to help you. Here is the link to CFSIZER.

Thanks IBM!

Four Important Buffer Pool Tuning Knobs in DB2 for z/OS

DB2 has five (well, four current) primary adjustable thresholds that can be modified using the ALTER BUFFERPOOL command.  

These thresholds are as follows:

The Sequential Steal Threshold, or VPSEQT, is the percentage of the buffer pool that can be occupied by sequentially accessed pages. For example, at the default value of 80, when this threshold is reached, 80% of the buffer pool represents pages for sequential processing. Of course, 80% is just the default; you can modify this value based on your processing needs to any value ranging from 0 to 100. When this threshold is reached, DB2 will steal a sequential page first before stealing a page from a randomly accessed page. So, for data that is accessed mostly sequentially (for example, through scans and prefetching) consider increasing the value of this parameter, and for data that is accessed most randomly, consider decreasing the value of this parameter. A VPSEQT value of zero will prevent any sequential pages from lingering in the buffer pool and it will turn off sequential prefetch.  A VPSEQT value of 100 allows the entire buffer pool to be monopolized by sequential pages.

The next tunable buffer pool threshold is the Parallel Sequential Threshold, or VPPSEQT. This threshold indicates the amount of the buffer pool that can be consumed by sequentially accessed data for parallel queries. When this threshold is reached, DB2 will cease to steal random pages to store sequential pages accessed by parallel queries. The default value for VPPSEQT is 50%, indicating its size as 50% of the sequential steal threshold (VPSEQT). For example, if the buffer pool is defined as 1000 pages and VPSEQT is set at 80%, a query using I/O parallelism can consume up to 400 sequential pages (that is, 1000 x 80% = 800 for the sequential steal threshold and 800 x 50% = 400 for the parallel sequential threshold).

The third, and final sequential threshold is the Assisting Parallel Sequential Threshold (or VPXPSEQT). This threshold is no longer supported as of DB2 11 because Sysplex Parallelism is no longer supported. When it was available, VPXPSEQT was used to indicate the portion of the buffer pool that might be used to assist with parallel operations initiated from another DB2 in the data sharing group. 

The final two modifiable DB2 buffer pool thresholds are used to indicate when modified data is to be written from the buffer pool to disk. Log data is externalized when a COMMIT is taken, but writing of the actual data itself is controlled by the two deferred write thresholds (and DB2 system checkpoints).

First we have the Deferred Write Threshold (or DWQT). When DWQT is reached, DB2 starts scheduling write I/Os to externalize the data pages to disk. By default, the deferred write threshold is reached when 30% of the buffer pool is allocated to unavailable pages, whether updated or in use. The default is probably too high for most larger buffer pools.

DB2 also provides the Vertical Deferred Write Threshold (VDWQT), which is basically the same as DWQT but for a single page set. By default, VDWQT is reached when 5% of the buffer pool is allocated to one data set. When reached, DB2 will start scheduling write I/Os to externalize the data pages to disk. Once again, this default is most likely too high for most shops.

The VDWQT threshold can be specified as a percentage of the buffer pool, or as an absolute number of buffers. When you want to specify a relatively low threshold for VDWQT using an absolute number of buffers provides better granularity and control.

In general, consider ratcheting the deferred write thresholds down to smaller percentages (from the defaults) for most of your buffer pools. Doing so enables “trickle” write from the DB2 buffer pools. This means that the data is written asynchronously to disk regularly over time in smaller amounts, instead of storing up a lot of modified data that has to be written all at once when the threshold percentage is reached. Of course, the needs of every shop will vary.

And yes, there are other buffer pool tuning options other than these 5  4 parameters, such as changing the size of the pool, specifying min/max size for BP expansion/contraction, or altering the page most often used to fine tune buffer pool operations.

All of the above thresholds can be changed using the -ALTER BUFFERPOOL command. 

DB2 Buffer Pool Monitoring

After setting up your buffer pools, you will want to regularly monitor your configuration for performance. The most rudimentary way of doing this is using the -DISPLAY BUFFERPOOL command. There are many options of the DISPLAY command that can be used to show different characteristics of your buffer pool environment; the simplest is the summary report, requested as follows:

-DISPLAY BUFFERPOOL(BP0) LIST(*) DBNAME(DSN8*)

And a truncated version of the results will look something like this:

DSNB401I - BUFFERPOOL NAME BP0, BUFFERPOOL ID 0, USE COUNT 20

DSNB402I - VIRTUAL BUFFERPOOL SIZE = 500 BUFFERS 736

             ALLOCATED = 500 TO BE DELETED = 0

             IN-USE/UPDATED = 0

DSNB404I - THRESHOLDS - 739

             VP SEQUENTIAL        = 80   HP SEQUENTIAL = 75

             DEFERRED WRITE       = 85   VERTICAL DEFERRED WRT = 80,0

             PARALLEL SEQUENTIAL  = 50   ASSISTING PARALLEL SEQT = 0

Of course, you can request much more information to be displayed using the DISPLAY BUFFERPOOL command by using the DETAIL parameter. Additionally, you can request that DB2 return either incremental statistics (since the last DISPLAY) or cumulative statistics (since DB2 was started). The statistics in a detail report are grouped in the following categories: GETPAGE information, Sequential Prefetch information, List Prefetch information, Dynamic Prefetch information, Page Update statistics, Page Write statistics, and Parallel Processing Activity details.

A lot of interesting and useful details can be gathered simply using the DISPLAY BUFFERPOOL command. For example, you can review GETPAGE requests for random and sequential activity, number of prefetch requests (whether static or dynamic, or for sequential or list prefetch), number of times each of the thresholds were tripped, and much more. Refer to the DB2 Command Reference manual (SC19-4054-02 for DB2 11) for a definition of each of the actual statistics returned by DISPLAY BUFFERPOOL.

Many organizations also have a performance monitor (such as IBM’s Omegamon) that simplifies the gathering and display of buffer pool statistics. Such tools are highly recommended for in-depth buffer pool monitoring and tuning. More sophisticated tools also exist that offer guidance on how to tune your buffer pools — or that automatically adjust your buffer pool parameters according to your workload. Most monitors also provide more in-depth statistics, such as buffer pool hit ratio calculations.

The buffer pool hit ratio is an important tool for monitoring and tuning your DB2 buffer pools. It is calculated as follows:

Hit ratio = GETPAGES - pages_read_from_disk / GETPAGEs

“Pages read from disk” is a calculated field that is the sum of all random and sequential reads.

The highest possible buffer pool hit ratio is 1.0. This value is achieved when each requested page is always in the buffer pool. When requested pages are not in the buffer pool, the hit ratio will be lower. You can have a negative hit ratio — this just means that prefetch was requested to bring pages into the buffer pool that were never actually referenced.

In general, the higher the hit ratio the better because it indicates that pages are being referenced from memory in the buffer pool more often. Of course, a low hit ratio is not always bad. The larger the amount of data that must be accessed by the application, the lower the hit ratio will tend to be. Hit ratios should be monitored in the context of the applications assigned to the buffer pool and should be compared against hit ratios from prior processing periods. Fluctuation can indicate problems.

Using the DISPLAY Command, Part 2

In the first part of this series on the DISPLAY command, we focused on using DISPLAY to monitor details about you database objects. In today's second installment of this series, we will look into using DISPLAY to monitor your DB2 buffer pools.

The DISPLAY BUFFERPOOL command can be issued to display the current status and allocation information for each buffer pool. For example:

DSNB401I  =DB2Q BUFFERPOOL NAME BP0, BUFFERPOOL ID 0, USE COUNT 202

DSNB402I  =DB2Q BUFFER POOL SIZE = 4000 BUFFERS  AUTOSIZE = NO     

             ALLOCATED       =     4000   TO BE DELETED  =        0

             IN-USE/UPDATED  =        0   BUFFERS ACTIVE =     4000

DSNB406I  =DB2Q PGFIX ATTRIBUTE -                                  

             CURRENT = NO                                          

             PENDING = NO                                          

           PAGE STEALING METHOD = LRU                              

DSNB404I  =DB2Q THRESHOLDS -                                       

            VP SEQUENTIAL    = 50                                  

            DEFERRED WRITE   = 15   VERTICAL DEFERRED WRT  = 5,  0

            PARALLEL SEQUENTIAL =0   ASSISTING PARALLEL SEQT = 0   

DSNB409I  =DB2Q INCREMENTAL STATISTICS SINCE 11:20:17 DEC 31, 2011 

DSNB411I  =DB2Q RANDOM GETPAGE = 6116897 SYNC READ I/O (R) = 37632 

           SEQ.   GETPAGE      = 799445  SYNC READ I/O (S) = 10602

           DMTH HIT            = 0       PAGE-INS REQUIRED = 0

DSNB412I  =DB2Q SEQUENTIAL PREFETCH -                              

             REQUESTS        =    11926   PREFETCH I/O    =   11861

             PAGES READ      =   753753                            

DSNB413I  =DB2Q LIST PREFETCH -                                    

             REQUESTS        =        0   PREFETCH I/O    =       0

             PAGES READ      =        0                            

We can see by reviewing these results that BP0 has been assigned 4,000 pages, all of which have been allocated. We also know that the buffers are not page fixed. The output also shows us the current settings for each of the sequential steal and deferred write thresholds.

For additional information on buffer pools you can specify the DETAIL parameter. Using DETAIL(INTERVAL) produces buffer pool usage information since the last execution of DISPLAY BUFFERPOOL. 

To report on buffer pool usage since the pool was activated, specify DETAIL(*). In each case, DB2 will return detailed information on buffer-pool usage such as the number of GETPAGEs, prefetch usage, and synchronous reads. The detailed data returned after executing this command can be used for rudimentary buffer pool tuning. We can see such detail in the example above.

For example, you can monitor the read efficiency of each buffer pool using the following formula:

  (Total GETPAGEs) / [ (SEQUENTIAL PREFETCH) +
                       (DYNAMIC PREFETCH) +
                       (SYNCHRONOUS READ)
                     ]

A higher read efficiency value is better than a lower one because it indicates that pages, once read into the buffer pool, are used more frequently. Additionally, if buffer pool I/O is consistently high, you might consider adding pages to the buffer pool to handle more data.


Finally, you can gather even more information about your buffer pools using the LIST and LSTATS parameters. The LIST parameter lists the open table spaces and indexes within the specified buffer pools; the LSTATS parameter lists statistics for the table spaces and indexes reported by LIST. Statistical information is reset each time DISPLAY with LSTATS is issued, so the statistics are as of the last time LSTATS was issued.