A depository database (depository) is a long-term archive, that provides time-unlimited archiving values in SQL databases. All values of an object with the option Depository enabled at its configuration, are stored in the depository database. Request for the storing into the depository database is generated immediately after storing into the archive database. So this storing and the storing into the archive database are executed almost in parallel. Unlike the archive database, data from the depository database is not available automatically in on-line mode and is never deleted. On-line accessing the data from depository databases is allowed after their mounting into the system.
Depository databases are available on Sybase, Oracle, and PostgreSQL platforms and their features depend on the possibilities of respective platforms. For Microsoft SQL Server platform, resp. Microsoft Desktop Engine (MSDE) platform, depository databases are not implemented.
Note: To find out about the configuration of the depository database, use a LIST_TREZOR TELL command.
Configuration of the D2000 Archiv process contains a configured time period TrezorPeriod, during which the process writes data to one depository database. When this period elapses, the database is released and the system starts storing data into a new database. The released database is available for storing delayed data for a defined time (configuration parameter TrezorCompressTime). After this time, the database is compressed by Sybase tools and moved to a directory specified by configuration parameter TrezorCompressPath. The name of the compressed database contains the end time of the repository database (e.g. Trezor_2008_03_20_16.cdb).
Note: Compression of Sybase databases is implemented by supported versions Sybase Adaptive Server Anywhere 6 to 9. Starting with the version Sybase SQL Anywhere 12, the utility for database compression does not exist anymore and compressed databases are considered to be obsolete. Therefore if you use Sybase SQL Anywhere 12, the depository databases are not being compressed but they remain in the directory specified by configuration parameter TrezorCompressPath, as files .db and .log (e.g. Trezor_2010_02_04_07.db and Trezor_2010_02_04_07.log).
Premature releasing the depository database can be performed using the RELEASE_TREZOR TELL command.
Off-line analysis of the data from the depository database is possible without performing any decompression. The support for the data import from the depository database into MS-EXCEL was created for the analysis. As the Sybase Adaptive Server Anywhere is a standard SQL server, any tool suitable for work with SQL databases can be used for the analysis.
Configuration of the parameters defining the method of depository database creation is implemented in the D2000 Application Manager process. They may be defined for each process D2000 Archiv.
The parameters TrezorPeriod and TrezorCompressTime can be given in hours, days, weeks, or months. The syntax is e.g.:
Note: It is possible to query the configuration of depository databases and currently mounted depository databases by Tell command LIST_TREZOR.
Warning: When a depository database is created or if it does not exist, the process D2000 Archiv of database templates copies the template of the depository database and initializes its structure. Because of this, it is not allowed to copy the templates of the depository database manually since the D2000 Archiv process does not initialize the depository database when it finds it in the Trezor subdirectory of the application directory.
Depository database is implemented as an independent tablespace named APPLICATION_TS_TREZORID, where ID=1,2.. etc (e.g. TEST_TS_TREZOR5). The tablespace contains datafiles - their number is defined by the parameter TrezorCountDatafiles (1, by default). Datafiles are generated in the TrezorPath directory.
If TrezorCountDatafiles=1, the datafile name is APPLICATION_TS_TREZORxxID_YYYYMMDD.ORA.
If TrezorCountDatafiles>1, the datafile names are APPLICATION_TS_TREZORxxID_01_YYYYMMDD.ORA, APPLICATION_TS_TREZORxxID_02_YYYYMMDD.ORA etc, where xxID is ID enhanced from the left with a zero to the length of 4 characters and YYYYMMDD is the depository database creation date, e.g. TEST_TS_TREZOR0005_03_20060930.ORA. Datafile size is specified by the parameter TrezorStartDatafileSize, the parameter TrezorMaxDatafileSize allows specifying the maximum datafile size.
Similarly to Sybase, the configuration of the process D2000 Archiv contains a configured time period TrezorPeriod, during which the process writes data to one depository database. When this period elapses, the database is released and the process starts storing values into a new database. The released database is available for storing delayed data for a given time (configuration parameter TrezorCompressTime). After the expiration of this time, the tablespace is first switched into read-only mode (ALTER TABLESPACE APPLICATION_TS_TREZORID READ ONLY) and then if the value of parameter TrezorCompressOffline=1, switched to offline mode (ALTER TABLESPACE APPLICATION_TS_TREZORID OFFLINE). If the parameters TrezorPath and TrezorCompressPath are defined, datafiles are to be moved to the TrezorCompressPath directory.
After releasing a depository database (and possible moving), it is possible to run an external program - its name and path to it is defined by the parameter TrezorPostCompressCmd and its parameters are defined by the parameter TrezorPostCompressPar.
The TELL command RELEASE_TREZOR allows releasing a depository database before time.
The process D2000 Archiv creates its internal list of depository database - the Trezors table.
Note 1: When switching tablespace to read-only mode, Oracle requires no active transactions in the whole database. The process D2000 Archiv, therefore, commits the writes to the archive as well as to an active depository database. Therefore it is important for the database containing the tablespace not to be used for other purposes which would require transactions that could take more time because the depository parts of the archive will be blocked until the transactions are finished (see theTrezorReadOnlyTimeout parameter).
Note 2: Maximum length of tablespace name in Oracle is 30 characters. That limitation must be taken into account when defining an application name - the name APPLICATION_TS_TREZORID (or APPLICATION_TS_TREZORID_seg in use of depository database segments) cannot be longer than 30 characters.
Note 3: Oracle database can contain as many datafiles as defined by the parameter DB_FILES in the initialization file of the database. Therefore, set up the value of the parameter to sufficient value before creating depository databases is enabled.
Since the 7.01.10 version, the process D2000 Archiv supports the concept of depository database segments on the Oracle platform. Depository database segments is a depository database tablespace, several of which can be created and filled in parallel. Each historical value is stored in one segment, the number of which is specified by the parameter Depository segment in the historical value configuration.
The number of segments created by the process D2000 Archiv is specified by the parameter TrezorCountSegments in the Windows registry. Historical values with the parameter depository segment higher than the value of the parameter TrezorCountSegments are to be stored in zero depository segment (it is the segment that is also created when depository database segments are disabled).
The main purpose of depository database segments is the creation of several smaller depository databases so that reading of one historical value for a long time interval requires less disk space (because only specific segments containing that historical value need to be mounted) - compared to mounting all non-segmented depository databases for a required time interval.
Having depository database segments enabled, the name of zero segment tablespace is APPLICATION_TS_TREZORID (identical with the name of the depository database tablespace if segments are disabled) and the names of all other segments are APPLICATION_TS_TREZORID_seg, where seg is a double-digit number of segment (between 01 and TrezorCountSegments).
If TrezorCountDatafiles=1, the names of datafiles are APPLICATION_TS_TREZORxxID_Syy_YYYYMMDD.ORA
If TrezorCountDatafiles>1, the names of datafiles are APPLICATION_TS_TREZORxxID_Syy_zz_YYYYMMDD.ORA, where xxID is ID enhanced from the left with zero to the length of 4 characters, yy is the segment number enhanced from the left with a zero to the length of 2 characters, z is the datafile number enhanced from the left with a zero to the length of 2 characters and YYYYMMDD is the depository database creation time. Example: TEST_TS_TREZOR0005_S02_01_20060930.ORA
Note: Starting with D2000 version 7.02.010, there is possible to specify a different suffix of datafile than ORA by using the parameter TrezorDatafileSuffix.
Note: Default prefix of depository tablespaces and datafiles APPLICATION_TS_TREZOR can be changed using the parameter TrezorPrefix.
All the configuration parameters in the Windows Registry are of String type, besides the parameters TrezorCountDatafiles, TrezorCountSegments, TrezorCountDatafiles, and TrezorNoLogging of DWORD type.
Oracle-based archives support creating the depository databases in a different database than the archive is in. The TNS of the depository database is defined by the configuration parameter
TNS_Service_Name_Trezor. The user name and password are the same as in the archive database.
Parameter TNS_Service_Name_Trezor is located in the registry in a branch belonging to the application and archive, e.g. HKEY_LOCAL_MACHINE\Software\Ipesoft\D2000V70\cfg_test\SELF.ARC
Database, in which the depository will be created, must contain the archive tablespace (created in the same way as if the archive was supposed to be located in this database) and appropriate archive user application_name_archiv, e.g. test_archiv.
Oracle-based archives support the configuration where two or more shadow archives work with a common depository database. Only an active instance of archive uses the depository database. The common depository database is defined by the setting of configuration parameter Trezor_Active_Only (of REG_DWORD type) to a non-zero value. This parameter is located in the registry in a branch belonging to the application and archive. As the archives are shadow archives, the branch of the archive contains also instance number, generally HKEY_LOCAL_MACHINE\Software\Ipesoft\D2000V70\cfg_APPLICATION_NAME\ARCHIVE_NAME.ARC_INSTANCE_NUMBER, e.g. HKEY_LOCAL_MACHINE\Software\Ipesoft\D2000V70\cfg_test\SELF.ARC_2\)
Note 1: Parameter Trezor_Active_Only must be configured on all instances of shadow archive, otherwise the archives will block each other trying to write the same value to the depository database.
A depository database is implemented as an independent database. Names of depository databases are defined by configuration parameter PG_TrezorName0.
Similarly to Sybase, the configuration of the process D2000 Archiv contains a configured time period TrezorPeriod, during which the process writes data to one depository database. When this period elapses, the database is released and the process starts storing values into a new database. The released database is available for storing delayed data for a given time (configuration parameter TrezorCompressTime). After expiration of this time, a default read-only access is configured for the database (ALTER DATABASE APLIKACIA_TREZOR_#ID# SET default_transaction_read_only = true) and then if value of parameter TrezorCompressOffline=1, access to depository database is forbidden (update pg_database set datallowconn = false where datname = 'APLIKACIA_TREZOR_#ID#'). After disconnecting a depository database, it is possible to run an external program - its name and path to it is defined by the parameter TrezorPostCompressCmd and its parameters are defined by the parameter TrezorPostCompressPar.
This program can for example move and compress a disconnected depository database or create a dump of read-only depository database via PostgreSQL utility pg_dump.
Note 1: In order for a database user dba (used by D2000 Archiv to connect to PostgreSQL server), to create and disconnect the databases, it is recommended to grant the dba user a superuser privilege ( ALTER ROLE dba WITH SUPERUSER; ).
Note 2: As every depository database (resp. every depository database segment) is a separate database, which is accessed by D2000 Archiv via a separate database connection, it is necessary to configure the PostgreSQL server to permit a sufficient number of connections (parameter max_connections), for D2000 Archiv to open a connection to all mounted depository databases.
Depository database on the PostgreSQL platform supports also depository database segments (similarly to Oracle platform). The depository database segment is a separate depository database, several of which can be created and filled in parallel. Each historical value is stored in one segment, the number of which is specified by the parameter Depository segment in the historical value configuration.
The number of segments created by the process D2000 Archiv is specified by the parameter TrezorCountSegments in the Windows registry. Historical values with the parameter depository segment higher than the value of the parameter TrezorCountSegments are to be stored in zero depository segment (it is the segment that is also created when depository database segments are disabled).
Name of the database representing depository segment 0 is specified by configuration parameter PG_TrezorName0, names of databases representing depository segments 1..N are specified by configuration parameter PG_TrezorName.
The main purpose of depository database segments is the creation of several smaller depository databases so that reading of one historical value for a long time interval requires less disk space (because only specific segments containing that historical value need to be mounted) - compared to mounting all non-segmented depository databases for a required time interval.
PG_TrezorFileMulti - parameter is active if also the PG_TrezorFilePath parameter is specified. The parameter defines a multiplicator for the CommitCount parameter. The default value of this parameter is 10.
PG_TrezorFilePath - parameter can be used for PostgreSQL 9.5 and above. It activates writing to depository databases via files, which is 2 to 3-times faster than the standard batch insert via the ODBC interface. After PG_TrezorFileMulti * CommitCount values are accumulated, they are stored into a file named archiv_<TrezorId>_<SegmentId>.txt (e.g. archiv_4_1.txt) in directory PG_TrezorFilePath and an UPSERT into a depository database is performed, using a foreign table mapped onto this file (using PostgreSQL extension file_fdw) as a source of data.
The directory PG_TrezorFilePath must be available both for reading and writing to archive as well as to the PostgreSQL database, therefore this parameter can be used if both archive and database are on the same computer.
An example of a batch file used to perform a depository database maintenance and export as well as the export of trezors table located in archive database MyApp.Archiv. The batch file requires as a parameter the name of the depository database, which can be provided by setting the TrezorPostCompressPar parameter to value #TREZOR#.
rem Target directory for exports
set MyDir=D:\Trezors_export\
rem Set PGPASSWORD to password assigned to postgres user during installation
set PGPASSWORD=MyPostgresPassword
set PATH=%PATH%;c:\Program Files\PostgreSQL\9.5\bin
rem export of table trezors from the archive database
pg_dump -Fc -U postgres -f "%MyDir%\MyApp_arc_trezors.dmp" --table \"trezors\" MyApp.Archiv >> %MyDir%\%1.log
rem permit write access to depository database and cluster the data table
echo alter database "%1" set default_transaction_read_only=false | psql -S -U postgres MyApp.Archiv >> %MyDir%\%1.log
echo alter table data cluster on ix_data_rc | psql -S -U postgres %1 >> %MyDir%\%1.log
echo cluster data | psql -S -U postgres %1 >> %MyDir%\%1.log
rem set access to depository database back to read only
echo alter database "%1" set default_transaction_read_only=true | psql -S -U postgres MyApp.Archiv >> %MyDir%\%1.log
pg_dump -Fc -U postgres -f "%MyDir%\%1.dmp" %1 >> %MyDir%\%1.log
Example of an equivalent batch file for Linux:
#!/usr/bin/env bash
MyDir=/trezorbackup
MyArc=mes_tpd_archive_self
MyLog=$MyDir/$1.log
#path to pg_dump, psql etc
PATH=/usr/pgsql-11/bin:$PATH
#password for dba user
export PGPASSWORD=***
#export of table trezors from the archive database
pg_dump -Fc -U dba -f "$MyDir/arc_trezors.dmp" --table \"trezors\" $MyArc >> $MyLog
#permit write access to depository database and cluster the data table
echo alter database \"$1\" set default_transaction_read_only=false | psql -S -U dba $MyArc >> $MyLog
echo alter table data cluster on ix_data_rc | psql -S -U dba "$1" >> $MyLog
echo cluster data | psql -S -U dba "$1" >> $MyLog
#set access to depository database back to read only
echo alter database \"$1\" set default_transaction_read_only=true | psql -S -U dba $MyArc >> $MyLog
#execute dump of depository database
pg_dump -Fc -U dba -f "$MyDir/$1.dmp" "$1" >> $MyLog