Updating Statistics for All System Tables with SAPDBA

Updating Statistics for All System Tables with SAPDBA

Use

You can use SAPDBA for Informix to update statistics on all system 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. System tables are particularly important for optimal performance of your database. 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 System Tables in SAPDBA for Informix.

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.

update level

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 system tables...

Then you see a message to confirm satisfactory completion:

Update statistics for all tables was successful!

See also:

Informix documentation