Reorganization of archive database

When the time depth of archiving is reached, the process D2000 Archiv deletes old data from archive database in regular intervals. Deleting old data and adding new data causes fragmentation of archive tables and their indexes (they contain unused fields) and increases of the database size.

For the reasons mentioned above, the process D2000 Archiv includes a reorganization tool that can be used on the database platforms Oracle, PostgreSQL and Sybase Adaptive Server Anywhere 8.0 and higher. The reorganization is not supported for the platforms MSSQL and Sybase Adaptive Server Anywhere 7.0 and older.

Reorganization of archive databases can be automatic, periodic or manual (using TELL command REORGANIZE).

Reorganization on Oracle platforms

The reorganization is being performed by the SQL command ALTER TABLE MOVE. The TELL command REORGANIZE allows to define a target tablespace - in this case, there is performed the command ALTER TABLE MOVE TABLESPACE, which moves the table into the target tablespace.

Note on Oracle 9i implementation of ALTER TABLE MOVE: Before the reorganization is enabled, TEMPORARY tablespace must have enough space, because during reorganization the table is copied to the TEMPORARY tablespace first, reorganized in it, than copied to the original or other tablespace and the original table is removed.

Note on Oracle 10g implementation of ALTER TABLE MOVE: TEMPORARY tablespace is not used for reorganization - the table being reorganized is copied to the original tablespace, reorganized and the original table is removed.

Starting with D2000 version 7.02.008 and using database Oracle 10g and above a new mode of reorganization is available. It is performed by SQL command ALTER TABLE SHRINK SPACE. The advantage of this mode is that it can be performed simultaneously with normal operations of D2000 Archiv - inserts and deletes in table being reorganized are permitted. This mode of reorganization can be enabled by start parameter /DBCS or dynamically by TELL command REORGANIZE SHRINK. TEMPORARY tablespace is not used either.

Reorganization on Sybase platform

The reorganization is being performed by the SQL command REORGANIZE TABLE INDEX. It reorganizes both the data and indexes (fragmentation level can be detected through the procedures sa_index_density and sa_table_fragmentation, for more detailed information see the Sybase manual).

Reorganization of archive tables does not reduce the size of the database file, just releases the space usable for adding new data. To view free database size use the Sybase Central tool - right-click on the database and select the Properties item from the context menu. The Extended Information tab contains the FreePages attribute that shows free pages in the database. To view increasing the FreePages attribute during the reorganization click the Refresh button.

Operations with archive table (read, write) while it is being reorganized are not allowed. Read, delete and write requests for such a table are queued and processed later after the reorganization is completed

Reorganization may take several seconds (small archive tables) up to tens of minutes (large structured archives) and depends on the hardware configuration of the database server.

Reorganization on PostgreSQL platform

The reorganization is being performed by the SQL command VACUUM. VACUUM reclaims storage occupied by obsolete versions of rows. PostgreSQL supports also automatic vacuuming, for more information see PostgreSQL documentation - configuration parameter autovacuum.

During vacuuming, both reading and writing operations are allowed.

For PostgreSQL archive, periodic reorganization with a period of several days is recommended.

Automatic reorganization

For the reasons of the back compatibility, the automatic reorganization is disabled by default. The start parameter /DBCY enables the reorganization. After the reorganization is enabled, archive table will be reorganized, if the following conditions are fulfilled:

• more or at least the number of rows defined by the parameter ReorganizeTableRowLimit are deleted from the table (default value is 50000),
• the number of hours defined by the parameter ReorganizeTableTimeLimit has elapsed since the last reorganization (default value is 24).

The parameters ReorganizeTableRowLimit and ReorganizeTableTimeLimit are defined in the Windows Registry (the archive key) and are of DWORD type.

Reorganization can also be enabled for either active instance of the process D2000 Archiv (the start parameter /DBCA) or passive instance (the start parameter /DBCP).

Use of the parameter /DBCA is meaningful along with the parameter /DBSH (archive database sharing) and avoids the passive instance reorganizes the archive table - the reorganization would block operations with the table for the active instance. The parameter /DBCA avoids reorganizing as well as deleting old archive values in the passive mode.

The parameter /DBCP can be used when reading from the archive should be blocked because the archive is being reorganized. For example: structured historical value with the parameters Archive playback and Publish values are checked and published values are used to control. For such a configuration, reorganization may take a several minutes and may cause a delay in publishing values. To avoid this situation, reorganization can be only performed by the passive instance that only writes into the archive.

The parameter /DBCP avoids reorganization and deleting old archive values in the active mode.

	none	/DBCY	/DBCA	/DBCP
Active	deleting: yes, reorganization: no	deleting: yes, reorganization: yes	deleting: yes, reorganization: yes	deleting: no, reorganization: no
Passive			deleting: no, reorganization: no	deleting: yes, reorganization: yes

Periodic reorganization

Due to complete reorganization of all archive tables it is possible to configure periodic reorganization.
It is controlled by archive parameters ReorganizePeriode and ReorganizeOffset. It is active if the parameter ReorganizePeriode has a non-zero value. The requests for reorganization of all archive tables will be generated each ReorganizePeriode hours (with offset ReorganizeOffset), supposing that:

• reorganization is on (/DBCY),
• reorganization in passive mode is on (/DBCP) and archive is passive,
• reorganization in active mode is on (/DBCA) and archive is active.

Note 1: It is possible to combine periodic and automatic reorganization, e.g. to configure periodic reorganization once a month and automatic reorganization every 50000 deleted rows but no more often than once every 72 hours.
Note 2: Parameters ReorganizeOffset and ReorganizePeriode can be changed via TELL command SET_OPTION.

Manual reorganization

Using the TELL command REORGANIZE allows to reorganize one table or entire archive. The parameters ReorganizeTableRowLimit and ReorganizeTableTimeLimit are ignored for manual reorganization.

The TELL command REORGANIZE offers the possibility to change the parameters /DBCY, /DBCA, /DBCP, ReorganizeTableRowLimit and ReorganizeTableTimeLimit while the archive is still running. All changes of start parameters performed this way are valid while the archive is running and are not stored in the configuration database. Changes of archive parameters stored in the Windows Registry are permanent.

Note: Current values of the parameters can be retrieved by the TELL command SHOW_INFO.