Performance-related tips

About this task

The following tips might be useful when you perform an optimization:
  • Generally, objects with similar access characteristics should be grouped and placed in the same buffer pool.

    Mixing page sets with a high sequential access characteristic with those that exhibit random access characteristics will be detrimental to both types of page sets. The pages belonging to the predominantly random page set could be stolen by the prefetch on the page set with sequential characteristic and it will increase the number of I/Os for the random page set.

    The more you separate sequential processing from random processing, the more benefit you get. To achieve this, you can adjust the sequential threshold of each buffer pool. See also the Seq Access column in Figure 1.

  • Assigning table spaces and index spaces into separate buffer pools that have sequential and random data access can have a more positive impact on overall buffer pool efficiency. It is generally better to put indexes in a separate buffer pool from the data because the access characteristics for indexes are usually very different from the data. See also the User-defined column in Figure 1.

    Exceptions might be objects that are really small, such as lookup tables.

  • Do not place any other objects in the buffer pool that is used by WORK/SORT data sets (DSNDB07), because of the intensive and special usage characteristics.
  • BP0 should be used exclusively for DB2® catalog objects and directory objects. These objects are not taken into account by the object placement algorithms.
  • A general recommendation is to distribute the objects in different pools based on the access intensity and the number of buffers the page set needs in the pool. The object placement function recommends the optimal size and thresholds for the buffer pools. The simulation function can be used to simulate if additional buffers to the pool will have an effect on reducing the buffer pool misses.
  • Validate the recommended values for deferred write threshold (DWQT) and vertical deferred write threshold (VDWQT). If these values allow a particular page set to monopolize the buffer pool by filling up with too many changed pages, the amount of subsequent asynchronous writes can flood the I/O subsystem all at once, thus adversely affecting overall throughput. Usually, it is better to distribute these writes evenly over time rather than to have peaks. However, these thresholds depend on your workload and the type and size of data being cached.
  • The first-in-first-out (FIFO) page steal algorithm is recommended, instead of the default Least-Recently-Used (LRU) algorithm, in either of the following situations:
    • For data and indexes residing entirely in the buffer pool
    • For objects with very low buffer pool hit ratio (< 1%)

    The FIFO algorithm, which can be specified by the ALTER BUFFERPOOL PGSTEAL command, will reduce processor cost under these conditions. See The Buffer Pool Characteristics section for more information about the page steal methods.

  • For buffer pools with high I/O rates (a high number of pages read or written) also consider the long-term page fix option for buffer pools, introduced with Db2 UDB for z/OS® Version 8. This option fixes a buffer pool in real storage for an extended period of time and can help reduce the number of I/Os for I/O-intensive buffer pools. For more information, see the description of the ALTER BUFFERPOOL command and its PGFIX keyword in the IBM DB2 11 for z/OS: Command Reference.