Optimizing object placements and buffer pool sizes

This function determines the optimal placements of table spaces and index spaces in buffer pools, the optimal buffer pool sizes, and the optimal values for some buffer pool thresholds. It uses predefined and modifiable expert rules and the object's access behavior to determine the optimum. The function is performed on the client and uses buffer pool performance data (from a bpd file) as input. These bpd files need to be downloaded to the client before they can be used.

This function analyzes the data, finds the optimum placements, sizes, and thresholds, and generates recommendations as ready-to-use SQL statements and DB2® commands:
  • The SQL ALTER statements contain parameters that determine which object (table space or index spaces) should be assigned to which buffer pool.
  • The Db2 ALTER BUFFERPOOL commands contain parameters that determine the recommended size and thresholds of each buffer pool.

The trace data must be a representative snapshot of the buffer pool performance, as described in Determining when and how long to collect. This function uses the objects' access behavior to determine the optimal object placements, and it uses many factors to determine the buffer pool sizes based on the placements. Approximations are used by the algorithms for data that cannot be retrieved from the input data.

You use the object placement tool on the client to select an appropriate bpd file and perform the optimization. The GUI provides an easy-to-use wizard that guides you through a few steps to determine the optimal object placements and buffer pool sizes. The wizard uses defaults based on information from the bpd file, but you can influence the optimization in several ways:
  • Buffer Pool Analyzer determines the available memory for buffer pools from the bpd file and uses this value as the default for the optimization.

    You can adjust the total buffer pool size, if you want this function to use a different size.

  • Buffer Pool Analyzer uses one of several predefined pattern files to determine the object placements. Pattern files contain expert rules that define which objects should be placed in which buffer pool according to each object's characteristics. The rules define criteria that must be met to assign an object to a buffer pool. The sequence of rules defines in which order the rules are applied to the objects.

    Buffer Pool Analyzer preselects a pattern file based on the total buffer pool size of the Db2 subsystem.

    You can choose a different pattern file. You can also edit a pattern file to adjust the object placement rules according to your needs. Modified pattern files can be saved and will automatically be preselected whenever a bpd file from the same subsystem is opened.

    Note: You can save a pattern file only if the file has at least one rule.
  • Buffer Pool Analyzer calculates the optimized assignments of objects to buffer pools, based on the available memory for buffer pools and the placement rules, and it calculates the optimum size of each buffer pool.

    You can adjust the assignments and the sizes of individual buffer pools, if required.

  • When Buffer Pool Analyzer has generated its recommendations for object placements and buffer pool sizes, you can adjust them according to specific needs. Your adjustments are reflected in the generated SQL ALTER statements and Db2 ALTER BUFFERPOOL commands.

The results from optimizations are lists of SQL ALTER statements and Db2 ALTER BUFFERPOOL commands that have their parameters set to the recommended values. Your adjustments and changes to an optimization are reflected in the results.

Results from optimizations are kept on the client. You can select them from the Buffer Pool Analyzer main window and view them in a web browser to assess them. To apply the statements and commands to a subsystem, you must upload them to the host and run them as usual. Right-click an object placement result to directly start a simulation using this placement.

You can also work with different performance scenarios by using different bpd files. You can compare the results and assess the variations on the client before you apply the recommendations to a Db2 subsystem.

Optimizing object placements and initial buffer pool sizes describes how to use this function and explains how to work with object placement rules in pattern files.