Reorganizing Objects (Informix)

Use

You can use SAPDBA to reorganize tables, indexes, and dbspaces. SAP recommends you not to regularly reorganize Informix databases. Therefore, make sure you have a clear reason for reorganization, which usually causes downtime (depending on the type of reorganization).

The following graphic illustrates the types of reorganization possible:

Types of Reorganization with Informix

The reorganization on the left is at table level, resulting in optimal storage of the reorganized table in a single extent. The one on the right is at dbspace level, resulting in optimal storage of all objects in the reorganized dbspace.

Prerequisites

Be sure to monitor extents, dbspaces, and chunks. This helps you to avoid reorganization where possible and identify when it is required. Refer to the following:

·        Managing Extents (Informix)

·        Managing Dbspaces (Informix)

·        Managing Chunks (Informix)

It is best to detect storage problems early:

·         Set up the database optimally and reorganize early when the database is small. You can use GoingLive to help you during the implementation phase. In general, reorganize early while the database is small. This can be done based on early usage patterns and future projection. For example, perform reorganizations immediately after an upgrade.

·         You can use EarlyWatch to detect storage problems early. EarlyWatch also gives recommendations to adjust database parameters, so helping to avoid reorganization completely.

For more information on GoingLive and EarlyWatch, see SAP Safeguarding.

Procedure

...

       1.      Identify the problem, making sure that reorganization is the correct way to solve it.

Ў        Extent overflow

If a dbspace has many tables with a large number of extents, it is often best to reorganize the dbspace, or particular tables within it. Extent overflow normally indicates that the dbspace is also short of space. Therefore, you can also extend the dbspace during reorganization.

If the dbspace is simply short of space, you can add a chunk with SAPDBA to extend the dbspace.

Ў        Dbspace overflow and dbspace contains many tables with a high number of extents

Reorganize the dbspace with SAPDBA. You can also extend the dbspace during this procedure.

Ў        Relocation of large tables

If you want to relocate large tables to a dbspace of their own (not generally recommended but sometimes a good idea), reorganize the tables with SAPDBA, specifying a new target dbspace.

You might then want to reduce the size of the original dbspace. For this you need to reorganize the dbspace.

Ў        Reclaim space from large tables that have shrunk

Before you reclaim the space, be sure that the tables will not grow again. If you are sure of this, you can use SAPDBA to reorganize the tables with smaller extent sizes.

Ў        Chunk overflow

An unusual reason for reorganization is that the dbspace has almost reached the maximum number of chunks. In this case, reorganize the dbspace with SAPDBA.

       2.      Choose the correct kind of reorganization:

Reorganize at the correct level, that is, at table or index level, or dbspace level.

SAP advises you to reorganize individual tables and indexes rather than entire dbspaces, if possible. In general, use SAPDBA for reorganization, with the SAP system down. However, you can reorganize an index or extend a dbspace (that is, add chunks to it) when the SAP system is up. You can also perform a minimal table reorganization with the SAP system up.

You can reorganize tables and dbspaces as follows:

·         Reorganizing a Single Table with SAPDBA

·         Reorganizing a Group of Tables with SAPDBA

·         Reorganizing a Dbspace and Its Tables with SAPDBA (if you want to resize the dbspace afterwards)

       3.      Reorganize in such a way as to minimize downtime.

When you decide to reorganize, there are a large number of factors to consider if you want to minimize the downtime. The time taken for data reorganization depends on the database size and the objects to be reorganized and could be several hours. Index reorganizations are usually faster than table reorganizations. As an example, the reorganization of an index of about 400 MB in size might take less than 10 minutes while for a 1 GB table around 1 hour might be needed (depending on hardware).

Prepare properly for reorganization

Check using SAPDBA to make sure the required disk space is available and relevant parameters are correctly set before shutting down the database host. Disk space needs to be available in the database itself for the “insert into select from” or “alter fragment” types of reorganization and in the file system for the “export/import” type. Proper preparation avoids unnecessary downtime due to improper settings or insufficient resources.

       4.      Schedule reorganizations together with database backups ( ON-Bar) or archives ( ON-Archive or ontape)

You must back up or archive the database before reorganization. Therefore, it makes sense to schedule reorganizations immediately after regular archives or database backups to avoid the need for a separate archive or database backup. It is also a good idea to group a number of reorganizations together to reduce total downtime.

Result

Reorganization helps you to solve problems that might lead to downtime, such as extent overflow, dbspace overflow, or chunk overflow. However, be sure to continue monitoring the database so that you can anticipate future problems before they cause downtime.

See also:

Reorganization with SAPDBA