Updating Statistics for All Tables with SAPDBA (SAP Library - SAP Database Guide: Informix (BC-DB-INF-DBA))

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.

To perform update statistics in parallel, you must meet the requirements given in SAP Note 189399. See the working processes field below. SAP intends to automate the preparations required in future.

Procedure

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

Caution

Depending on the size of your database, this action might 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.

You see a screen with the following possibilities:

Field	Meaning
threshold value: deviation %	The threshold for how much the table’s statistics differ from the current value held by the query optimizer. If the deviation is less than this threshold, no update statistics is actually performed when you select Execute.
logfile output	The method of storing logging information from a processing run of update statistics: Normal A single entry is written to the file. Extra large One entry is written to the file for each table processed.
working processes: number	The number of processes used by update statistics. If you specify more than 1, SAPDBA performs update statistics in parallel, but you must first meet certain prerequisites (see above).
update level	This dialog box has the levels defaulted to high and medium. 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.

Choose Execute.

You see the following message:

Will now update statistics for all tables.

Do you want to continue?

To stop, choose 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!