Reorganizing a Single Index with SAPDBA (SAP Library - SAP Database Guide: Informix (BC-DB-INF-DBA))

Reorganizing a Single Index with SAPDBA

Use

You can use SAPDBA for Informix to reorganize a single index. SAPDBA rebuilds the index using the parameters that you specify.

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.

You have analyzed the storage parameters for the index to make sure that the reorganization is necessary. Refer to Analyzing Index Information with SAPDBA.

Procedure

Choose Reorganization ® Reorganize Index(es) ® Single Index in SAPDBA for Informix.

In index name, enter data as required. If you do not know the name of the index you want, you can use a partial entry (for example, pa*) and SAPDBA lists the matching indexes.

If you want to analyze the index information before you reorganize, choose Index Information.

Choose Continue.

SAPDBA displays a set of parameters specifying the reorganization.

Enter the fill factor.

This parameter specifies the fill level of the index when it is reorganized. Set it as follows:

- If the underlying table is dynamic (that is, a lot of new data is being added) specify a low fill factor.

- If the underlying table is static (that is, little new data is being added) specify a high fill factor.

The value initially displayed is a default that you can find in the ONCONFIG file:

FILLFACTOR 90 # Fill factor for building indexes

You can use SAPDBA to view the ONCONFIG file. Refer to Listing System Information with SAPDBA. The file is called onconfig.<hostname>.<sid> and you can find it in the directory $INFORMIXDIR/etc (UNIX) or %INFORMIXDIR%\etc (NT).

Enter the value for 'detach' index in dbspace.

This parameter specifies whether the reorganized index remains in the same dbspace. You can enter a new dbspace if you want to detach the index and store it in a separate dbspace.

Caution

To reorganize the index, SAPDBA needs to lock it. Therefore, it is best to reorganize when the R/3 System is down, to avoid performance problems. You might also experience unpredictable results if the index you are trying to reorganize is locked by another process.

Choose Continue to reorganize the index.

SAPDBA reorganizes the index.

Note

You might see warning messages during the reorganization. These alert you to possible problems and often advise you on the best course of action.

Note

You need to periodically clear up the $INFORMIXDIR/sapreorg (UNIX) or %INFORMIXDIR%\sapreorg (NT) directory in which SAPDBA creates working directories and files. Refer to SAPDBA Housekeeping.

Result

After an index reorganization, your database is less likely to run into space problems. However, you need to keep on monitoring the index, especially if the underlying table is growing rapidly. Refer to Storage Analysis of Tables and Indexes with SAPDBA.

See also:

Informix documentation