Statistics for modeling production databases

Sometimes you might want your development system to contain a subset of the data in your production system. However, access plans that are selected on development systems are not necessarily the same as those that would be selected on the production system.

In some cases, it is necessary that the catalog statistics and the configuration of the development system be updated to match those of the production system.

The db2look command in mimic mode (specifying the -m option) can be used to generate the data manipulation language (DML) statements that are required to make the catalog statistics of the development and production databases match.

After running the UPDATE statements that are produced by db2look against the development system, that system can be used to validate the access plans that are being generated on the production system. Because the optimizer uses the configuration of table spaces to estimate I/O costs, table spaces on the development system must be of the same type (SMS or DMS) and have the same number of containers as do those on the production system. The test system may have less physical memory than the production system. Setting memory related configuration parameters on the test system same as values of those on the production system might not be feasible. You can use the db2fopt command to assign values to be used by the optimizer during statement compilation. For example, if the production system is running with sortheap=20000 and the test system can only run with sortheap=5000, you can use db2fopt on the test system to set opt_sortheap to 20000. opt_sortheap instead of sortheap, will be used by the query optimizer during statement compilation when evaluating access plans.