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

Reorganizing a Single Table with SAPDBA

Use

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

Note

In normal operation, you do not need to regularly reorganize the tables in Informix databases. Therefore, make sure you have a good reason before you reorganize.

You can change the following storage parameters for a table:

Dbspace
First extent size
Next extent size
Locking mode

The following guidelines can help you choose the new storage parameters:

Make the next extent size large enough to allow growth without causing a high number of new extents to be created.

Make the new first extent of the table larger than the current size of the entire table to allow for growth.

Decide if the table needs to be relocated to another, less-heavily used dbspace if you are trying to remove an I/O bottleneck.
Decide if the table needs to be fragmented (this is not available in 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. However, expert mode is only required for the reorganization method Change 'Next Extent Size' or 'Lock Mode' of Table if you want to change the locking mode of the table.

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

If you are using the reorganization method Reorganize Table by 'insert into select from …' or Reorganize Table by 'Export/Import', you have completed the preparations for reorganizing a table.

Procedure

Choose Reorganization ® Reorganize Single Table in SAPDBA for Informix.

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

Select the reorganization method you want:

Method	Description
Reorganize Table by 'insert into select from …'	SAP recommends you to normally use this method. This method runs entirely within the database (instead of using the file system) and is more rapid as well as more secure. To use it successfully there must be sufficient space available in the database to store a second copy of the table being reorganized. SAPDBA checks this.
Reorganize Table by 'Export/Import'	Use this method if you do not have enough space in the database for a second copy of the table to be reorganized.
TEST UNLOAD of table (no Table Locks)	Use this method if you want to check that there is enough space in the file system to allow the previous method to function correctly. SAPDBA only performs the export phase, including data compression, and deletes the downloaded data at the end. No table locks are needed for this method.
Change 'Next Extent Size' or 'Lock Mode' of Table	This is best if you only want to alter the next extent size or locking mode, because it is much simpler and quicker than the other methods. No data is moved with this method.

If you want to analyze the table information before you reorganize, choose Table Information.

Choose Continue.

SAPDBA displays:

Fixed table parameters that you cannot change
Variable table parameters that you can change for the reorganization

The table parameters displayed vary, depending on the reorganization method you have chosen (that is, not all parameters appear for every method). The parameters include the following:

Parameter	Meaning
used size	Shows the maximum amount of storage space that the table has ever occupied (in kilobytes). You cannot change this.
rows	Shows the current number of rows in the table. You cannot change this.
first extent	Shows the size (in kilobytes) of the first extent (physical storage unit) for the table. If a table has a large number of extents and you wish to compact it into one single extent, you would increase this value. We recommend you enter a value here that is greater than or equal to the current used size of the table. The minimum extent size is four times the page size on your system (zero is an exception – see below). If you enter a first extent size of zero, SAPDBA calculates an optimal size for the first extent. We recommend you enter zero if you want to reduce the storage space used by the table. This calculation can take some time with large tables, especially if there are blobpages present (the estimate is also less precise with blobpages). A message appears, informing you that SAPDBA is calculating: Estimating optimal size of ‘first extent’. Please wait a moment...
next extent	Lets you redefine the size of the next extent (physical storage unit) for the table. The value displayed is the current next extent size for the table. If you expect the table to grow very rapidly (that is, more than expected when first created) it is a good idea to increase this parameter. On the other hand, you might want to decrease it if you are sure the table will not grow very much in the future. The following limitations apply to this parameter: The minimum extent size is four times the page size on your system. So with a 2 KB page size, the minimum size is 8 KB. SAPDBA warns you if you attempt to set a lower next extent size. If you redefine the next extent size as less than 5% of the table size, SAPDBA warns you. You can still proceed with this change if you are sure.
locking mode	Shows how the table is locked. Tables are normally locked at the row level because this increases the general availability of the data.
target dbspace	Shows the dbspace where the table is currently stored. This field is used to specify where the table will be stored after the reorganization, so you can change it if you want to move the table. For more information about finding a new dbspace with enough space, see Listing Dbspaces with SAPDBA.
index options	Lets you specify how the indexes are handled by the reorganization: ‘detach’ indexes in dbspace You can specify that the indexes are detached and which dbspace they are stored in. Create indexes BEFORE or AFTER loading data This is available starting with Informix version 7.30. You can specify that indexes are created before or after the data has been loaded: Use the default AFTER option normally, unless you have good reason not to (for example, insufficient space in the temporary dbspace). Avoid the BEFORE option if possible, because this can lead to difficulties with the "insert into select from" method. This option is more complicated because it requires renaming of the indexes, which can cause difficulties if there is a problem during the reorganization.
download path	Lets you specify the working directory for reorganizing the table, if you are using the "Export/Import" method. Remember the following when you set up this directory: Specify appropriate access rights so that your chosen directory is protected from access by third parties and available for read/write access by user informix (a dialog box informs you if the access rights are insufficient). Do not use directory /tmp because it is frequently used by other processes and is often cleared in system housekeeping operations. Avoid using the same file system as the directory that contains working scripts and log files during the reorganization process, that is, $INFORMIXDIR/sapreorg (UNIX) or %INFORMIXDIR%\sapreorg (NT).

Enter data as required to change the variable table parameters.

Choose Continue.

If you are	Then
Performing Change 'Next Extent Size' or 'Lock Mode' of Table	SAPDBA reorganizes the table and the procedure is complete. See "Result."
Performing any other type of reorganization.	SAPDBA prepares for the reorganization, which is not yet complete.

Execute the reorganization.

SAPDBA reorganizes the table.

Note

If you are using the method Reorganize Table by 'Export/Import' or TEST UNLOAD of table (no Table Locks), SAPDBA creates a directory and files during the unload phase. You do not normally need to look at this unloaded data.

For more information if there is a problem, see SAPDBA Files, Directories, and Reports.

Result

After a table reorganization, your database is less likely to run into space problems. However, you need to keep on monitoring the table, especially if it is growing rapidly. Refer to Analyzing Table Information with SAPDBA.

If you have used the method Reorganize Table by 'insert into select from …' or Reorganize Table by 'Export/Import,' see Finishing a Table or Dbspace Reorganization with SAPDBA.

See also:

Informix documentation