When the time depth of archiving is reached, the process D2000 Archiv process deletes old data from the archive database in at 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 the 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, the TEMPORARY tablespace must have enough space, because during reorganization the table is copied to the TEMPORARY tablespace first, reorganized in it , than then 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 the 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 the 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 the 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
...
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 the PostgreSQL archive, periodic reorganization with a period of several days is recommended.
...
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, the archive table will be reorganized, if the following conditions are fulfilled:
...
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 reorganising 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 that can be used when reading from the archive should be blocked because the archive is being reorganized. For example: structured historical value values 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 |
| Kotva | ||||
|---|---|---|---|---|
|
Due to the 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 the TELL command SET_OPTION.
| Kotva | ||||
|---|---|---|---|---|
|
Using the TELL command REORGANIZE allows to reorganize reorganizing one table or the 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 in archive parameters stored in the Windows Registry are permanent.
Note: Current values of the parameters can be retrieved by the TELL command SHOW_INFO.