Update Statistics (DB2 UDB for UNIX and Windows) 

Use

You can use the DBA Planning Calendar to schedule an update of the database statistics. The actions for updating database statistics are provided on the Action Pad.

Features

There are different ways to keep your statistics up-to-date:

·        RUNSTATS and REORGCHK for all Tables

Operates on all tables and may take several hours depending on the size of the database. You should schedule this action to run once a week. If you schedule this action using the DBA Planning Calendar, you can set the following parameters:

¡        Maximum Runtime

In case of large databases, it may be advisable to restrict the execution time of the RUNSTATS and REORGCHK for all Tables action if the total runtime of the action exceeds the available time window.

Assuming the action takes 10 hours to run, you could restrict the execution time of the action to 2 hours and schedule it to run on 5 days of the week, specifying an execution time of 120 minutes for each run. Since the table with the oldest statistics is processed first each time the action runs, this would ensure that all table statistics would have been updated after 5 days with 2 hours runtime per day.

¡        Number of parallel jobs

If there are enough system resources available (for example, background processes and the appropriate number of processors), the RUNSTATSjob can be parallelized by setting this parameter to a value >1. Doing so results in additional jobs, which are scheduled by the regular DB13 job and which perform RUNSTATS on tables in parallel.

The system makes sure that the number of parallel jobs does not exceed the number of available background processes. However, you have to handle this action with care because starting more jobs can have a high impact on the overall system performance even though it will significantly reduce the amount of time for the job execution.

·        RUNSTATS and REORGCHK (DBSTATC)

Operates on a subset of tables that are determined by the accumulation of table snapshot data. You should schedule this action to run daily. In contrast to previous releases, this action no longer needs the Check Tables Statistics Update as a prerequisite.

For the planning of RUNSTATS and REORGCHK (DBSTATC), you can set the following parameters:

¡        Update Threshold

Specifies the percentage of updates indicated by the table snapshot in relation to the current size of a table as stored in the DB2 system catalog. Whenever this threshold is exceeded, the affected table is flagged in the DBSTATC control table.

¡        Minimum Size of Table

Specifies the minimum size a table should have to receive a flag in the DBSTATC control table.

¡        Maximum Runtime

Same meaning as described for RUNSTATS and REORGCHK for all Tables . This option only makes sense when processing a set of tables.

¡        Number of parallel jobs

Same meaning as described for RUNSTATS and REORGCHK for all Tables . This option only makes sense when processing a set of tables.

·        RUNSTATS and REORGCHK for Single Table

Performs a RUNSTATS for a single table or a set of table specified by a name using wildcards. The parameters are the same as for RUNSTATS Control as described in Space: Single Table Analysis. In addition, you can set the following parameters.

¡        Maximum Runtime

Same meaning as described for RUNSTATS and REORGCHK for all Tables . This option only makes sense when processing a set of tables.

¡        Number of parallel jobs

Same meaning as described for RUNSTATS and REORGCHK for all Tables . This option only makes sense when processing a set of tables.

Since the RUNSTATS and REORGCHK for all Tables action affects system performance, you should schedule it to run outside normal working hours, for example, on Sundays.

Activities

The actions RUNSTATS and REORGCHK for all Tables and RUNSTATS and REORGCHK (DBSTATC) may be controlled by changing the DBSTATC control table using transaction DB21. For more information, see Space: Single Table Analysis.