Running statistics on a table

Use the Step Properties notebook (for DB2 UDB runstats) to create a step that can be used to update system catalog statistics on the data in a table, the data in the table's indexes, or the data in both the table and its indexes. The optimizer uses these statistics to choose which path will be used to access the data.

In general, you want to update statistics if there have been extensive changes to the data in the table.

Note: The table that you want to run statistics on must be linked as both the source and target.

  Authorities and privileges

To run statistics on a table:

1. Open the step notebook for DB2 UDB runstats.

2. Specify information for your step:

   • In the Name field, you can type a new name for the step. Otherwise, you can keep the name that the Data Warehouse Center automatically supplied for the step. This field is required.

   • In the Administrator field, type the name of the person who is responsible for the maintenance of this step. This field is optional.

   • In the Description field, type a business description for your step. This description can be a maximum of 255 characters. This field is optional.

   • In the Notes field, type detailed information that might be helpful to users who can access this step. This field is optional.

3. Optional: On the Parameters page, specify information for the run statistics step (the fields on this page are available only if the table that you want to run statistics on is linked as both the source and target on the Process Modeler):

   • Specify the level of statistics you want to gather for the table by selecting a radio button under Statistics for the table.

   • Specify the level of statistics you want to gather for the table's indexes by selecting a radio button under Statistics for the indexes.

   • Use the Share level radio buttons to specify the type of access you want other users to have to the table while the statistics are being gathered.

4. On the Processing Options page, in the Agent Site list, select an agent site where you want your step to run. The selections in this list are agent sites that are common to the target table and the step that you are defining.

5. If you want to have the option to run your step at any time, select the Run on demand checkbox. Your step must be in test or production mode before you can run it.

6. Optional: Select the Populate externally check box if the step is populated externally, meaning that it is invoked in some way other than by the Data Warehouse Center. The step does not have to have any other means of running in the Data Warehouse Center in order to change the mode to production.

   If Populate externally is not selected, then the step must either have a schedule, be linked to a transient table that is input to another step, or be started by another program in order to change the mode to production.

7. In the Retry area, specify how many times you want the step to run again if it needs to be retried and the amount of time that you want to pass before the next run of the step.

8. Click OK to accept the parameters specified in this notebook and save the run statistics step.

Related information

Exporting data from a table or view

Loading data into a table

Reorganizing a table

For information about the RUN STATISTICS command, see the DB2 Command Reference.

For information about the RUNSTATS utility, see the Administration Guide.

For information about statistics for tables or indexes, or about share levels, see the Administration Guide.