Migrating statistics by using the HSTATS command

You can use the HSTATS command 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—that buffer pools, EDM pools, and so on are of comparable sizes in both environments. You can preserve the test data and still view the access paths based on production data.

Related topics

Migrating statistics

To migrate statistics, follow these steps:

  1. Extract the statistics from the source Db2 subsystem. This process creates a script containing the statistics to update on the target Db2 catalog.
  2. (Optional) Update extracted statistics.
  3. Transfer statistics into the target Db2 subsystem (SSID).

Because of the method used to extract the statistics, BMC AMI Command Center does not verify whether the objects exist in the target environment. If the objects do not exist, the transfer process receives the following message: The following row not found for update.

Before you begin

Make sure that you have the appropriate authorities to access the Db2 catalog.

To migrate statistics by using the HSTATS command

  1. Select the DB2 Navigator perspective.
  2. Open or create an object view.

  3. Select one or more objects from the database, table space, table, or index list.

    Important

    The table, table space, and index statistics that affect the access path are migrated for the selected objects.

  4. Select  Commands > HSTATS.

    Tip

    Alternatively, you can right-click the selected objects and select Commands > HSTATS.

    In the Scratchpad perspective, BMC AMI Command Center displays SQL commands for the extracted statistics.
    NG_scratchpad_hstats.png

  5. Update the object names as needed.
  6. To save the extracted statistics, follow these steps:
    1. On the toolbar, click Save file NW_floppy.png.
    2. Select a z/OS data set in which to save the file.
      1. Click Add filter NW_plus_circle_o.png .
      2. In the Filter Pattern box, enter a filter (the wildcard * is supported) to display a filtered list of data sets that include the one that you want.
      3. Click OK.
      4. In the displayed hierarchical tree, navigate to and select the data set that you want.If you select an archived data set, when prompted confirm that you want to restore that data set.
    3. Enter a file name.
    4. Click Save.
  7. To transfer statistics to another Db2 database or subsystem, follow these steps:
    1. On the toolbar, click Run NW_run.png.
    2. In the Run Commands dialog box, select the DB2 Connection to which you want to transfer the statistics.
    3. Click OK.

Statistics migration updates the following Db2 statistics tables:

  • SYSTABLESPACE
  • SYSTABLES
  • SYSCOLUMNS
  • SYSCOLSTATS
  • SYSCOLDIST
  • SYSTABSTATS
  • SYSINDEXES
  • SYSKEYTARGETS
  • SYSKEYTARGETSTATS
  • SYSKEYTGTDIST
  • SYSKEYTGTDISTSTATS

 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*