You can use SAPDBA for Informix to reorganize a dbspace and all its tables. SAPDBA optimizes the space allocation of tables in the dbspace. For more information, see Reorganization of a Group of Tables or Dbspace with SAPDBA. If you use the interrupt facility, you can use this procedure to resize a dbspace and optimize its storage.

• 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 tables and dbspaces to make sure that the reorganization is necessary. Refer especially to the following:
• Analyzing Tables by Fill Level, Size, and Extents with SAPDBA
• Analyzing Tables for Critical Next Extent Size with SAPDBA
• Listing Dbspaces with SAPDBA

• You have completed the preparations for reorganizing a dbspace.

1. Choose Reorganization ® Reorganize Dbspace in SAPDBA for Informix.
2. In Dbspace to reorganize, enter the dbspace that you want to reorganize.
3. In Dbspace for intermediate storage, enter the dbspace where the tables are stored during reorganization. This dbspace cannot be the same as the Dbspace to reorganize.
4. If you want, use SAPDBA to set up a dbspace for intermediate storage (or you can use an existing dbspace if you prefer).
5. In No space gain for tables smaller than [KB ], enter the threshold for the table size used by SAPDBA to determine the method of reorganization used for individual tables during the import phase of the reorganization.

This field is not relevant if you are using Informix version 6.0, because SAPDBA uses the "insert into select from" method for all tables. However, if you are using a later Informix version, then SAPDBA determines the method to use as follows:

If table is	Then SAPDBA performs a reorganization with the
Larger than value of No space gain for tables smaller than [KB]	"Insert into select from" method. This is the slower reorganization method but is aimed at reclaiming space (more important for larger tables).
Smaller than or equal to value of No space gain for tables smaller than [KB]	"Alter fragment" method. This is the faster method and does not always result in a reduction of storage space for the table (less important anyway for smaller tables).

6. Select whether to interrupt the reorganization using Interrupt Selection. If you interrupt the reorganization, you can also reorganize the dbspace itself (that is, not only the tables in the dbspace). The interrupt selections are as follows:

Interrupt selection	Meaning
No interruption	SAPDBA continues without pause from the first phase (that is, moving tables to intermediate storage) to the second phase (that is, moving tables back to original dbspace).
Interrupt after selected dbspace is empty	SAPDBA pauses between the two phases. This allows you to drop and recreate the original dbspace before the tables are loaded back into it. SAPDBA automatically offers you this functionality so that the entire operation can run as one sequence. Alternatively, you can resume normal production with the R/3 System during the interruption and finish the operation later. Refer to "Interrupting the procedure" in "Performing Reorganize Dbspace" below.

7. In Intermediate Storage, select what SAPDBA does with the tables during processing:

Intermediate storage	Meaning
Data only	SAPDBA only stores the data, not the index. Therefore, processing is generally faster and less intermediate storage space is required. However, the tables in the dbspace cannot be productively used while they are held in intermediate storage, as they are incomplete without indexes.
Data + indexes	SAPDBA stores both data and index. Therefore, processing is slower because SAPDBA needs time to create indexes for the tables in the intermediate storage area. An update statistics is also performed. Select this option if you want to use the database productively while the tables are in intermediate storage or if you want a time delay between the first phase of the reorganization and the second phase. In this case, you must do a level-0 archive. See description in section "After Reorganize Dbspace".

8. Choose Continue to start the reorganization.

SAPDBA performs the first phase of the reorganization, processing each table in turn until all the tables in the Dbspace to reorganize have been processed.

During the reorganization, SAPDBA keeps you informed of progress. You can abort the reorganization by pressing Ctrl-C , but this works only between one table and the next. Every 10 tables, SAPDBA performs a checkpoint, which avoids unnecessarily large data loss if a serious error occurs midway through the reorganization.



The additional parameters necessary to reorganize tables using the "insert into select from" method are assigned as follows (you cannot alter these yourself):

· Next extent and locking mode (that is, page or row) are taken from the values previously held for the table by the database server.

· First extent is optimally calculated by SAPDBA for each table, according to the amount of data currently in the table. However, this calculation is only approximate if the table contains blobpages.

What happens now depends on whether you selected "interrupt":

If you	Then
Did not select interrupt	SAPDBA continues without pause to the second phase of the reorganization. The reorganization procedure is finished when SAPDBA displays the appropriate message. See "Result."
Selected interrupt	SAPDBA stops at the end of the first phase. Continue with the procedure as described below.



You can safely interrupt the reorganization for a short time. However, a long interruption can be more of a problem. Make sure that, if you intend to use the database productively during a long interruption, you immediately perform a full level-0 database backup ( ON-Bar ) or archive ( ON-Archive or ontape ).

You can also exit SAPDBA during a long interruption and then restart uncompleted reorganization requests with SAPDBA to complete the procedure. In this case, SAPDBA prompts you to "update" the request if you have used any of the tables involved during the interruption (for example, if the R/3 System has been productive in the meantime). Do not do this if you have lost data.

SAP recommends that you complete the procedure without a long interruption.

9. Use SAPDBA to drop the original dbspace and recreate it with more or fewer chunks (that is, resize it). If you want, you can create a new mirrored dbspace to reduce the chances of data loss and reduce system downtime (but this requires more disk space).
10. Start the second phase of the reorganization.

SAPDBA finishes the reorganization, moving the tables back to the original dbspace.

11. Use SAPDBA to drop the intermediate dbspace.

After a reorganization, your database is less likely to run into space problems. However, you need to keep on monitoring the dbspace and its tables, especially if they are growing rapidly. Refer to Analyzing Tables by Fill Level, Size, and Extents.

You must now finish the reorganization.

For more information if there are problems with the reorganization, see Reorganization Troubleshooting with SAPDBA.