Updating Statistics for All Tables with SAPDBA

Updating Statistics for All Tables with SAPDBA

Use

You can use SAPDBA for Informix to update statistics on all tables in the database. This makes sure that the query optimizer for the Informix database has up-to-date information when it optimizes queries at execution time. This action also updates system tables. For more information, see Update Statistics with SAPDBA.

Prerequisites

SAPDBA is installed correctly. Refer to SAPDBA Setup.

You are logged on in expert mode, that is, as user informix , and you have started SAPDBA. Refer to Starting SAPDBA.

Procedure

Choose Update Statistics ® Update Statistics for All Tables in SAPDBA for Informix.

Depending on the size of your database, this action may take several hours to run. Schedule it for a period when your system is not heavily loaded and you have plenty of time, so you do not impact your normal service levels.

Only expert users (that is, those logged on as user informix ) may perform this action. After choosing it, you see a screen with the following possibilities:

The deviation field specifies the threshold for how much the table’s statistics differ from the current value held by the query optimizer. For tables where the deviation is less than the value given here, no update statistics is actually carried out when you select Execute.

The update level dialog box has the levels defaulted to high, medium and low respectively. For more information about update levels, see the Informix documentation. Unless you have very good knowledge of the Informix database, we recommend you leave them as they are:

heading index columns

This refers to the leading component of an index and is set to high because the query optimizer can best optimize queries with exact information on such columns. You cannot change this parameter.

non heading index columns

This refers to the secondary component of an index and is defaulted to medium because the query optimizer requires fairly accurate information about such columns in order to optimize queries.

non index columns

This refers to columns that are not part of any index and is defaulted to no update because the query optimizer does not need accurate information about such columns in order to optimize queries.

Having chosen Execute, you see the following message:

Will now update statistics for all tables.

Do you want to continue?

You may abort the process by responding cancel.

If you proceed, you see a message to confirm that SAPDBA is running the update:

Running update statistics for all tables...

Then you see a message to confirm satisfactory completion:

Update statistics for all tables was successful!