Overview of statistics migration

The Migrate Access Path Statistics feature of the BMC AMI SQL Explorer for Db2 product enables you to copy statistics from one Db2 catalog to another, or to the same subsystem’s Db2 catalog. For example, you can migrate production statistics to a test environment to view the actual access paths that the Db2 optimizer selects, without the cost of replicating the production environment. This example assumes that your Db2 environments are the same—for instance, that buffer pools, EDM pools, and so on are of comparable sizes in both environments. You can preserve the test data and still see the access paths based on production data.

You can use the Explain feature to analyze SQL in a test environment and store the access paths in the History tables. After you migrate the production statistics to the test environment, you can re-Explain the SQL in combination with the Compare function to get information about changes in access paths that occur prior to actually binding the SQL in production. Alternatively, if you run the RUNSTATS utility and perform the rebinds in production, then note a significant or sudden drop in performance, you can quickly copy your saved statistics to the catalog. Then, using SQL Explorer, you can Explain the production statistics later in a non-production situation to determine why the access paths changed.