Update Statistics in CCMS (Informix) 

Purpose

You can update statistics on the Informix database using the Computing Center Management System (CCMS). The cost-based optimizer (CBO) for the Informix database needs current, up-to-date statistics about the number of data rows, size of indexes and the distribution of data in individual fields of a table. By running update statistics, you make sure that the CBO has up-to-date information so it can work out the best access path to get data for a query.

If the statistics held by the CBO are out of date or not present at all, the CBO might generate inappropriate access paths, for example, use of the wrong index or inappropriate use of a full table sequential scan when a suitable index exists. The result is poor database performance.

You can also run update statistics for your Informix database using SAPDBA. Refer to Update Statistics with SAPDBA.

Prerequisites

Before running update statistics in CCMS, check that you have met the R/3 System prerequisites in Getting Started in CCMS with Informix DBA.

Process Flow

You have the following possibilities for running update statistics in CCMS:

  1. You can use the DBA Planning Calendar in CCMS to schedule regular execution of check statistics and update statistics, as follows:
    1. You can schedule check statistics in the DBA Planning Calendar to regularly examine how far the actual statistics deviate from the stored statistics for a table or tables.
    2. You can schedule update statistics in the DBA Planning Calendar to ensure that the database statistics are regularly updated.

    If you are new to the DBA Planning Calendar, see DBA Planning Calendar (Informix).

  2. You can also run one-off checks on individual tables to see if the table’s statistics are out-of-date, and then run an update statistics for the table if required. This is useful, for example, if the data in a table has been significantly updated, but the next scheduled run of update statistics is not for some time. Refer to Checking and Updating Statistics for a Single Table in CCMS (Informix).
  3. You can configure update statistics by amending the parameters in the control table DBSTATC . This control table contains a list of the database tables for which the default values for update statistics are not suitable. If you change this table, all runs of update statistics – in SAPDBA, CCMS, or the DBA Planning Calendar – are affected. Configuring update statistics makes sense with large tables, for which the default parameters might not be appropriate.

Result

The statistics used by the Informix CBO are kept up-to-date, resulting in optimal execution of queries and better performance.