SAP Note 22941 - Reorganization of tables and dbspaces!

Symptom:

Key words: INF REORG
Requirement for the reorganization of an Informix database.

Cause and prerequisites

Reasons that would cause a reorganization to be required are:

1. Table has a large number of extents
This is less important if you use an index. Only for 'full-table-scans' are the data accesses slower. As you generally access data via suitable indices, you should weigh up the costs against the benefits. 80%) the maximum possible extents for this table appear, action is required (see 2.).

2. Reaching storage limits, for example, max-extents
In INFORMIX, this is only important in a very small number of exceptional cases with large installations, due to 'extent merging', 'extend doubling' and a very large number of extents. It is recommended that you monitor the system very carefully (SAPDBA, CCMS) in order to be able to recognize possible problems (SAPDBA, CCMS) and to intervene where required!

3. Release of table free space that is already allocated
In large DELETE actions without a subsequent INSERT, a lot of general free space is created within tables that cannot be used by other tables. One example is the 'Client-remove'. A table reorganization may be useful here.

4. Reconfigure disks/dbspaces/chunks
If a large number of tables are deleted, a lot of freespace results within dbspaces/chunks that cannot be used by other dbspaces, for example in the 3.0 upgrade. A dbspace reorganization is useful here.
The same applies if hard disk dbspaces/chunks should be moved to other hard disks, for example, for I/O balancing.

Solution
!!!!!!!!!!!!!!!!!!!!! Warning !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Due to an Informix error in 'dbaccess' for Informix version 7.20 (all platforms), you must never unload tables with 'unload' and 'load' into the file system. The -X flag that is required for dbaccess does not work correctly, and this can lead to serious data loss! For a reorganization, use only SAPDBA and always follow the strategy:
'insert into select from ...'.
Using export/import, data is unloaded into the file system. This must not be used with this version!

In the dbspace reorganization 'insert into select from ..' or 'alter fragment ...' (Informix 7.x) is basically used, so no errors occur here!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

An INFORMIX database generally does not require any reorganization in normal operation, except in cases 2, 3 or 4.
This is however only really the case if all other tuning options, for example 'UPDATE STATISTICS', index strategy (-> Early Watch !!) have been exhausted, or there are acute space problems.

Steps prior to a reorganization

• Executing a level 0 backup.

• Shut down the R/3 System ("stopsap r3")

• Switch the database to 'NO LOGGING MODE':

Please see the following note regarding this!

• Log off all users (SAPDBA, DBACCESS ... )

• su - informix

• Start SAPDBA Version 3.1G:

Via the menu option f) 'Logging Mode' ->

a) Switch to 'No Logging' Mode

• or enter the following at UNIX level:

ontape -N
Note:

In the case that you only want to reorganize small tables or dbspaces, this can also be done in the 'Unbuffered Logging' mode. In this context, 'small' means that the dataset to be reorganized is small when compared to the size of the logical log. Assuming your log has a size of 350 MB and you want to reorganize a dbspace that is 40-100 MB, there should be no problems as long as no-one else uses the database at the same time. In reorganizations with logging switched on, there is a danger of a so-called 'long transaction' that would then be rolled back automatically by the database. A 300MB table, for example, cannot be reorganized like this, as the threshold values for the start of the roll back (LTXHWM, LTXEHWM), depending on the values set, take effect when 60% of the log files are filled.

When reorganizing the dbspaces, that contain very little data after an upgrade from R/3 Release 2.x to 3.x, a reorganization with the logging switched on would be sensible.

In all cases, the R/3 System should be shut down during a reorganization, to avoid locking problems both from the viewpoint of the R/3 System and from the viewpoint of the reorganization.

If, after considering the above points, you want to execute a reorganization with the logging switched on, SAPDBA allows this if you use the following environment variables:

SAPDBA_IGNORELOGGING

Set this variable to any number, for example (for the C shell):

setenv SAPDBA_IGNORELOGGING 1

SAPDBA then allows you (as the user 'informix') to execute the functions for reorganization in the 'Unbuffered Logging' mode.

If the Logging mode stays set to Unbuffered Logging during the reorganization, there is no need to perform a special level 0 backup of the database, either before or after the reorganization. In the case of an error, the recovery mechanisms can then access the database. A 'gap' in the sequence of the transaction logs, unlike a reorganization in the 'No Logging' mode, does not occur.

Note:

During a reorganization all indexes of the tables concerned are
recreated. Please make sure, that you have sufficiant temporary dbspace
available (e.g. in dbspace 'tmpdbs1') to allow a successful completion
of this task. In general 30 - 40% of the size of the table (in case of
a dbspace reorganization 30 - 40% of the largest table) as temporary
dbspace should be enough.
Reorganization of a table:

If possible, use the SAPDBA for INFORMIX for this task!
All necessary steps are executed automatically and checked for success. All objects related to the table (indexes, views, constraints, access authorizations and so on) are recreated by SAPDBA during the reorganization.

Note:

If you are not familiar with this functionality of SAPDBA, it is highly recommended that you read the corresponding chapters in the SAPDBA documentation before starting the operation.

The procedure described here corresponds to the current SAPDBA 3.1G version. Note 38401 describes how you can download the latest version of this from the servers 'sapservX'. In a reorganization, it is recommended that you always work with the most recent SAPDBA version.

Note:

If you use a SAPDBA Version 2.2C to 3. 0D, the texts of the menu options and also the menu structure are slightly different to the guideline that follows! Instead of 'Reorganization', you find 'Change Storage Options' (main menu). You find the Table reorganization under 'Change Table Storage Options with Export/Import'. In older versions, the selection of a reorganization strategy described in point 5 is also inapplicable. You can then only unload the table into the file system, it is not possible to reorganize in the database. You must always make sure that there is enough space in the file system!

1. Start SAPDBA (Versions 3.0E to 3.1G) as the user 'informix'.

2. Menu option: c) Reorganization

3. Menu option: e) Reorganize Single Table

4. Enter: a) Table name

5. Select Reorganization strategy:

You should generally use option b) (insert into select from ...). The table is then reorganized in the database via a copy. It is not necessary to unload into the file system. However, you must have sufficient space in the database, in order to allow the temporary creation of a copy of the table. SAPDBA may show you possible bottlenecks.

6. Enter: s) Continue

7. When the screen appears, you can enter the storage parameters, such as extent sizes, lock mode or target dbspace of the table if required. If you want to move the table to another dbspace, you can enter this under target dbspace!

8. Enter: s) Generate shell script files

9. Confirm that you really want to run the reorganization with 'Yes'

If there are problems, SAPDBA reports these. In this case, please read the documentation. This contains a more detailed description of the procedure. If you cannot recover from the error, please contact the Hotline.

If you want to reorganize several tables at the same time, you can find the following menu option in SAPDBA (3.0E to 3.1G):

f) Reorganize Group of Tables

Here, you can create an input file with the name and storage parameters for all the tables to be reorganized. The tables listed are then edited sequentially without interruption. For further details on this, please refer to the documentation for SAPDBA (3.0E to 3.1G).

Reorganization of a table without SAPDBA

Note: It is not recommended that you carry out a reorganization in this way. If possible, you should use SAPDBA for INFORMIX.

Reorganization via 'dbaccess LOAD/UNLOAD'

!!!!!!!!!!!!!!!!!!!!!!!!! Warning !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
You should never use this method if you use Informix Version 7.20!! The -X flag for dbaccess does not work.
Unloading and loading tables can cause serious data losses!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

At the moment, the only suitable method is 'dbaccess LOAD/UNLOAD' with the undocumented option '-X', that allows you to unload and load table data. Without this flag, SAP data is destroyed!

Respective structure information can be unloaded with 'dbschema' and the option '-ss'.

Example:
Unload the table ABCD from system C11, structure first, then data:
$ su - informix
$ dbschema -d c11 -t abcd -ss abcd.sql
$ echo "unload to abcd.uld select * from abcd" | dbaccess -e -X c11
Caution:
As the affected views are also deleted by the DROP TABLE later, the schema must be saved first!
$ foreach viewname ( `echo "output to pipe xargs without headings
select t.tabname from systables t, sysdepend d
where t.tabid = d.dtabid and d.dtype = 'V'
and d.btabid = (select tabid from systables
where tabname = 'abcd')" | dbaccess c11` )

? dbschema -d c11 -t $viewname -ss view_$viewname.sql
? end
Load the table ABCD into the system C11, structure first, then views, then data:
$ echo 'drop table abcd' | dbaccess c11
$ dbaccess -e c11 abcd.sql
$ foreach viewfile ( `ls view_*.sql` )
? dbaccess -e c11 $viewfile
? end
$ echo 'load from abcd.uld insert into abcd' | dbaccess -e -X c11
Reorganize dbspaces:

As of SAPDBA Version 3.0E, there is a user-friendly way to reorganize dbspaces. From Version 3.1G, this operation was improved further and accelerated (particularly under Informix Versions 7.x!!), and you are highly recommended to download the current version (>= 3.1G) from the corresponding sapservX machine (see note 38401).
Even though the actual SAPDBA procedure is very simple, serious problems can still occur. You should therefore carefully consider this action first, and if you have any doubts, request consulting via the Hotline.
Basically, the reorganization follows this procedure:

1. Move all tables in the dbspace to be reorganized to a different, new dbspace of a suitable size.

2. If all tables are moved, the empty dbspace can be reconfigured.

3. All tables are then copied back again into the original dbspace.

Execution:

1. Create a new dbspace if possible (for example with SAPDBA) that is large enough to include all the tables from the dbspace to be reorganized). If required, you can also use an already existing dbspace that contains other tables. If serious problems, a completely separate dbspace will simplify matters greatly!

2. SAPDBA version < 46C:
Please refresh the statistics of table 'syscontraints' of your database. Please use the Informix tool 'dbaccess' for this task.
In order to do that, start 'dbaccess' and connect to the database. Then run the SQL command:
update statistics low for table sysconstraints;

With version 46C SAPDBA will do this automatically!

3. Start the SAPDBA (from Version 3.0E)

4. Menu option: c) Reorganization

5. Menu option: g) Reorganize Dbspace

6. Enter: a) Dbspace to be reorganized

7. Enter: b) Enter the dbspace created/planned under point 1)

8. Input option:

(*) No interruption

( ) Interrupt after selected dbspace is empty

Here, you can choose whether SAPDBA should stop after exporting the tables to the intermediate dbspace (point 6), in order to allow you to delete and recreate the original dbspace (Interrupt after selected dbspace is empty), or whether the import should be executed directly without any interruptions (No interruption).

9. Input option:

(*) Data only

( ) Data + Indexes

As a default, no indices are created when storing the tables temporarily, and no Update Statistics is executed. This brings a 20-30% space and time reduction during the export.
However, the tables are not to be used productively in this status. Only when the tables are imported back into the original dbspace are the indices regenerated and the Update Statistics is executed.
If you want to interrupt the reorganization, to run a level 0 backup in productive mode, indices must also exist in the intermediate storage. Also, current statistical information is required for the data. If you want to do this, select:

Data + Indices (Version 3.1G)

10. Input parameter: (only for SAPDBA 3.1G and Informix 7.x)

c) No space gain for tables smaller than [KB]:

When importing the tables back into the original dbspace, different strategies are used when moving the tables, depending on whether they are larger or smaller than the entered value. For tables that are smaller than the threshold value, a controlled size correction of the table does not occur, this is done for tables that are larger. However, the first method is considerably faster for small tables. The default value of 100 KB is adequate in most cases. Details and further information on this can be found in the documentation for SAPDBA (3.1G).

11. Enter: s) Continue

12. Confirm this with 'Yes'.

Caution:
During and after the reorganization, the logs written must be checked (reorg_log_exp/reorg_log_imp),
particularly if SAPDBA reported errors or irregularities.
It is recommended that you read the corresponding documentation thoroughly before using this function for the first time.

Reorganization without SAPDBA:
We strongly recommend that you do not use this!!
The procedure is, however, given here for the sake of completeness:

The tables in the dbspace involved are determined as follows:
$ dbaccess <select tabname
from sysmaster:sysptnhdr a, systables b
where a.partnum = b.partnum
and DBINFO('DBSPACE',a.partnum) = ''
EOF
Reorganization via dbaccess LOAD/UNLOAD
For this, the procedure described in 'Reorganization of a table without SAPDBA' must be carried out for each of the tables to be reorganized so that first, all tables are unloaded and all tables, index and view descriptions are generated. If this succeeds, all affected tables must now be dropped. The dbspace involved should be deleted after this. $ onspaces -d

• The dbspace can then be recreated accordingly. $ onspaces -c -d -p -o -s

• All tables must be recreated, as described below.

• After this, all views must be generated and then all tables must be reloaded.
  
  

Steps after the reorganization

• Switch the database logging mode back to Unbuffered Logging:

Use SAPDBA to do this (from version 3.1G):

Menu option f) Logging Mode ->

b) Switch to 'Unbuffered Logging' Mode

or manually:

• Set the onconfig parameter 'TAPEDEV' to '/dev/null'

(onmonitor -> Logical-Logs -> Tape-Parameters).

• Switch the Logging Mode:

$ ontape -s -U

• Reset the parameter 'TAPEDEV' to its previous value.

• Start the R/3 System ("startsap")

• Perform a level 0 backup of the database!!!

Only absolutely necessary if you set the logging mode to 'No Logging' during the reorganization.

Additional key words

Reorganization