Start of Content Area

Procedure documentation Preparing for Table or Dbspace Reorganization with SAPDBA  Locate the document in its SAP Library structure

Use

Before you reorganize a table, a group of tables, or a dbspace with SAPDBA for Informix, you must complete the preparations described here.

Prerequisites

The reorganizations for which you must prepare are the following:

- Reorganize Table by 'insert into select from …'

- Reorganize Table by 'Export/Import'

Refer to Reorganizing a Single Table with SAPDBA.

Procedure

  1. Make sure that the R/3 System is down, to prevent problems with locks on the tables that you are reorganizing.

    2. At the top of any menu screen in SAPDBA, you see 'sapr3' User(s): 1 when R/3 is down. SAPDBA requires a connection. If you have multiple SAPDBAs running, you see the number of SAPDBAs in this field, because each SAPDBA requires a sapr3 user.

    Note

    If you are reorganizing a table that is significantly smaller than the size of your logical log, you can perform the reorganization without switching database logging off. In this case, make an entry SAPDBA_IGNORELOGGING 1 in the ONCONFIG file, but only for the duration of the reorganization. If you decide to do this, you can skip the next two steps.

  2. Create a level-0 (full) database backup ( ON-Bar ) or archive ( ON-Archive or ontape ) before reorganization. For more information, see:

If data is lost during the reorganization, you can then restore the database using this database backup or archive.
  1. Switch the database server to "no logging mode."

    2. You must switch the database server to no logging mode before you start the process. For more information, see Switching to "No Logging Mode" with SAPDBA.

    In no logging mode, the database server does not log the reorganization. Logging uses up logical-log storage, possibly causing the logs to fill. You cannot start the process unless the database is in no logging mode.

  2. Make sure that there are no locks on the tables you want to reorganize

    3. If a lock has been set on a table by some other database process, you cannot reorganize the table. You have to wait for the lock to be released before proceeding with the reorganization. Make sure that no other database activity has placed a lock on the table. The best way to do this is to bring the R/3 System down (see first step above).

  3. Make sure of enough space in SAPDBA working directory.

    4. SAPDBA stops processing if there is less than 1 MB of space left in the directory $INFORMIXDIR/sapreorg (UNIX) or %INFORMIXDIR%\sapreorg (NT), used to store scripts and log files for the reorganization. Make sure that there is enough space in this directory before you start these actions. This does not apply to the actions under Reorganize Single Table.

    If you are not using the "export/import" method, you have now finished the procedure, so see "Result".

  4. If you are using the "export/import" method, check the following:
    1. Make sure of enough space in download path.

      2. SAPDBA displays the available space in a dialog box when you start the process. If there is not enough space left in the download path during processing, SAPDBA recognizes this and stops processing so that no data is lost.

    2. Make sure of access rights in download path.

      3. You must be able to write to this directory. Prevent other users from accessing this directory. This is because any change to the downloaded file endangers the integrity of your data and should be avoided.

    3. Delete existing unload files.

If there are existing unload files left over from a previous job, delete them before starting the process. SAPDBA prompts you to delete them if you forget.

  1. If the table to be reorganized has been altered with In-Place Alter Table much more space might be required for the reorganization than required for the existing table. You can check this with oncheck -pT .

Result

You are now ready to perform the reorganization. For more information, see: