...
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 online mode and is never deleted. On-line Online accessing the data from depository databases is allowed after their mounting into the system.
...
| Kotva |
|---|
| automountpath |
|---|
| automountpath |
|---|
|
AutoMountPath - path to a directory from which all the depositories will be mounted automatically after the start of the D2000 Archiv process - TrezorPath - path to the directory where the depository databases are created
| Kotva |
|---|
| trezorcompresspath |
|---|
| trezorcompresspath |
|---|
|
TrezorCompressPath - path to the directory for storing the compressed depository databases- TrezorPeriod - the period of the depository creation. It is specified in format the format NX, where N is an integer number and X defines a time unit.
Permitted time units are:- H - hour
- D - day
- W - week
- M - month.
Note: Setting the parameter either to an empty value or to 0H disables the functionality of depository databases.
| Kotva |
|---|
| trezorcompresstime |
|---|
| trezorcompresstime |
|---|
|
TrezorCompressTime - time defining the compression of the released depository database. The database is available for writing delayed data during this time. The value of TrezorCompressTime must be less than the value of TrezorPeriod.| Kotva |
|---|
| trezorcompressoffline |
|---|
| trezorcompressoffline |
|---|
|
TrezorCompressOffline - value 1 (default) means that the compressed depository database will remain offline. Value 0 means that the compressed depository database will be re-connected and available for reading.
...
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 in 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, data files are to be moved to the TrezorCompressPath directory.
After releasing a depository database (and possible possibly moving), it is possible to run an external program - its name and path to it are defined by the parameter TrezorPostCompressCmd and its parameters are defined by the parameter TrezorPostCompressPar.
...
Note 1: When switching tablespace to read-only mode, Oracle requires no active transactions in the whole database. The D2000 Archiv process, 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 that 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 3: Oracle database can contain as many datafiles data files as defined by the parameter DB_FILES in the initialization file of the database. Therefore, setting up the value of the parameter to sufficient value before creating depository databases is enabled.
...
If TrezorCountDatafiles=1, the names of the data files are are APPLICATION_TS_TREZORxxID_Syy_YYYYMMDD.ORA
If TrezorCountDatafiles>1, the names of the data files are are APPLICATION_TS_TREZORxxID_Syy_zz_YYYYMMDD.ORA, where xxID is ID enhanced from the left with zeros 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 it is possible to specify a different suffix of datafile than ORA by using the parameter TrezorDatafileSuffix.
Note: Default The default prefix of depository tablespaces and datafiles APPLICATION_TS_TREZOR can be changed using the parameter TrezorPrefix.
...
| Kotva |
|---|
| trezorpathora |
|---|
| trezorpathora |
|---|
|
TrezorPath - path to the directory, the data files of tablespaces are created in. You must also enter the character "\" after the directory name. If the parameter is not given, data files are created in the database directory.- TrezorCompressPath - path to the directory, the data files of tablespaces released are being moved to. You must also enter the character "\" after the directory name. Once the TrezorCompressPath parameter is defined, you must also define TrezorPath, otherwise, data files are not to be moved.
| Kotva |
|---|
| trezorperiodora |
|---|
| trezorperiodora |
|---|
|
TrezorPeriod - the period of the depository creation. It is specified in format the format NX, where N is an integer number and X defines a time unit.
Permitted time units are:- H - hour
- D - day
- W - week
- M - month.
Note: Setting the parameter either to an empty value or to 0H disables the functionality of depository databases.
| Kotva |
|---|
| trezorcompressofflineora |
|---|
| trezorcompressofflineora |
|---|
|
TrezorCompressOffline - value 1 (default) means that the compressed depository database will be put into offline mode. Value 0 means that the depository database remains in read-only mode and data will be available for reading. Value 2 means that D2000 Archiv will not access the depository database while TrezorPostCompressCmd is running, so this command may perform various maintenance tasks which could otherwise block the archive.| Kotva |
|---|
| trezorcompresstimeora |
|---|
| trezorcompresstimeora |
|---|
|
TrezorCompressTime - time for releasing depository tablespace. The tablespace is available for writing delayed data during this period. TrezorCompressTime must be less than TrezorPeriod.| Kotva |
|---|
| trezorcountsegments |
|---|
| trezorcountsegments |
|---|
|
TrezorCountSegments - number of depository database segments (depository database tablespaces), that are being created in parallel. The default value is 0 (only one segment 0 is being created), the maximum value is 99 (segments 0 to 99 are being created). The parameter is of DWORD type !!!.| Kotva |
|---|
| trezorcountdatafiles |
|---|
| trezorcountdatafiles |
|---|
|
TrezorCountDatafiles - the number of data files in the tablespace, by default, is 1 (the parameter is of DWORD type !!!).| Kotva |
|---|
| trezorstartdatafilesize |
|---|
| trezorstartdatafilesize |
|---|
|
TrezorStartDatafileSize - the size of the data file that is to be created (in Oracle syntax, e.g. 500K, 300M). The parameter must be specified.
Note: For OpenVMS+Oracle 9.2.0.x platform, there is a problem with data files damaged at the size of 4GB - must be tested prior to usingbefore use!| Kotva |
|---|
| trezormaxdatafilesize |
|---|
| trezormaxdatafilesize |
|---|
|
TrezorMaxDatafileSize - maximum size of data file (in Oracle syntax, e.g. 500K, 300M or UNLIMITED). If it is not defined, the size of the data file is file is specified at its creation (see the note for the previous parameter).| Kotva |
|---|
| trezordatafilesizestep |
|---|
| trezordatafilesizestep |
|---|
|
TrezorDatafileSizeStep - the size of data file increment (in Oracle syntax, e.g. 200K, 1M) during the growth of the datafile (ON NEXT clause of the CREATE TABLESPACE command). If the parameter is not defined, the ON NEXT clause is not used and the increment is the database default value (8K, by default).| Kotva |
|---|
| trezordatafilesuffix |
|---|
| trezordatafilesuffix |
|---|
|
TrezorDatafileSuffix - suffix of the data file. The default value of this parameter is is ORA.- TrezorPrefix - if this parameter is not set, the prefix of depository tablespaces and datafiles is APPLICATION_TS_TREZOR.
Setting the value of TrezorPrefix changes this prefix. The parameter can be used together with the DbUsername parameter when doing a migration of migrating the archive database from one application to another (or when renaming an application) and keeping the archive tablespace and existing depositories (i.e. setting the value of TrezorPrefix to OldApp_TS_TREZOR). - TrezorReadOnlyTimeout - specifies the timeout (in seconds) for switching the depository database into READ ONLY mode. After expiration, the archive generates the system alarm "Changing trezor tablespace read-only takes more than 120 sec, possible Oracle lock!". The default value is 120 seconds.
| Kotva |
|---|
| trezorreadsegment0 |
|---|
| trezorreadsegment0 |
|---|
|
TrezorReadSegment0 - Parameter has a meaning only if depository segments are configured (non-zero value of TrezorCountSegments parameter). Activation of a parameter (value 1) instructs the archive to read also from depository segment 0 when reading from any other depository segment. Reading from depository databases will be slower, but it enables reconfiguring historical values from default depository segment 0 to a different segment while keeping access to data previously stored in depository segment 0.
The TrezorReadSegment0 parameter can be changed by the SET_OPTION TELL command.| Kotva |
|---|
| trezorreadsincecreate |
|---|
| trezorreadsincecreate |
|---|
|
TrezorReadSinceCreate - value 1 means that depositories that are older than Create Time of historical value will not be read. The TrezorReadSinceCreate parameter can be changed by the SET_OPTION TELL command.| Kotva |
|---|
| trezorpostcompresscmdora |
|---|
| trezorpostcompresscmdora |
|---|
|
TrezorPostCompressCmd - the name of a program to be run after releasing the depository database and possible moving of data files of the released depository database released. The program may be used e.g. to pack data files and copy them via network.
Note: before running the command, the environment variables D2000_DBUSER and D2000_DBPASS are set to the name and password that D2000 Archive uses to access the archive database. These variables can be used in a script to connect to the archive database.| Kotva |
|---|
| trezorpostcompressparora |
|---|
| trezorpostcompressparora |
|---|
|
TrezorPostCompressPar - optional parameter, used for the program specified by the parameter TrezorPostCompressCmd. It can contain optional text and predefined macros to be replaced before running the program:- #ID# - is replaced by depository database Id (e.g. 15).
- #TREZOR# - is replaced by the depository tablespace name (e.g. TEST_TS_TREZOR5).
- #SEGMENTS# - is replaced by the number of depository database segments (i.e. the value of the parameter TrezorCountSegments).
- #FILES# - is replaced by the number of data files of the depository database (i.e. the value of the parameter TrezorCountDatafiles).
- #FILE1#, #FILE2#, etc. - is replaced by the name of the corresponding data file along with the path, if the path is defined by the parameter TrezorPath (e.g. C:\ora920\oradata\D2000\TEST_TS_TREZOR5_1.ORA).
Example 1: There is enabled the creating creation of depository databases with one data file to be packed into the file TrezorID.zip (ID=1,2 etc.) and moved to the directory D:\backup
TrezorCountDatafiles 1
TrezorPostCompressCmd C:\utils\zip.exe
TrezorPostCompressPar -m D:\backup\Trezor#ID#.zip #FILE1#
After releasing for example the depository database nr. 5, a program can be run:
C:\utils\zip.exe -m D:\backup\Trezor5.zip C:\ora920\oradata\D2000\TEST_TS_TREZOR5_1.ORA
Example 2: Configuration, which will remount (using TELL command) the depository tablespace immediately after it is released by:
TrezorPostCompressCmd c:\D2000\D2000.E70\bin\tell.exe
TrezorPostCompressPar dst=SELF.ARC cmd="MOUNT_TREZOR #ID#" uid=myuser pwd=mypassword- TrezorNoLogging - the parameter of DWORD type, its non-zero value causes the depository tablespace to be created in the NOLOGGING mode. If its value is zero or doesn't exist, the depository tablespace is to be created in the LOGGING mode (it generates REDO logs and is restorable).
...
| Kotva |
|---|
| trezor_outside |
|---|
| trezor_outside |
|---|
|
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
| Kotva |
|---|
| tns_service_name_trezor |
|---|
| tns_service_name_trezor |
|---|
|
TNS_Service_Name_Trezor. The
user name username 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
The 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.
...
A depository database is implemented as an independent database. Names of depository databases are defined by the configuration parameter 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 are defined by the parameter 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 the PostgreSQL utility 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 the 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.
| Kotva |
|---|
| trezorovy_segmentpg |
|---|
| trezorovy_segmentpg |
|---|
|
Depository database segments
Depository The depository database on the PostgreSQL platform supports also depository database segments (similarly to the 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 main purpose of depository database segments is the creation of several smaller depository databases so that reading 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.
| Kotva |
|---|
| parametre_pre_pgsql |
|---|
| parametre_pre_pgsql |
|---|
|
Configuration parameters for the PostgreSQL platform:| Kotva |
|---|
| pg_createtrezor |
|---|
| pg_createtrezor |
|---|
|
PG_CreateTrezor - SQL command for creating a depository database. Default value is
CREATE DATABASE "#TREZOR#" WITH ENCODING='UTF8' OWNER=dba TABLESPACE="D2000" TEMPLATE=template0
where #TREZOR# is the name of the depository database (defined by configuration parameter PG_TrezorName0 resp. for depository database segments 1..N by parameter PG_TrezorName). By default existence of tablespace named D2000 and template database template0 is required in the PostgreSQL database server.| Kotva |
|---|
| pg_trezorfilemulti |
|---|
| pg_trezorfilemulti |
|---|
|
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.| Kotva |
|---|
| pg_trezorfilepath |
|---|
| pg_trezorfilepath |
|---|
|
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 in 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 the archive as well as to the PostgreSQL database, therefore this parameter can be used if both the archive and database are on the same computer.
| Kotva |
|---|
| pg_trezorname0 |
|---|
| pg_trezorname0 |
|---|
|
PG_TrezorName0 - mask for the name of created depository databases. If depository database segments are enabled, this mask will be used for segment 0. The default value is appname_TREZOR_#ID#, where appname is the application's name and #ID# is replaced by the depository database number.
E.g. for the application named Test, depository databases Test_TREZOR_1, Test_TREZOR_2, Test_TREZOR_3, etc will be created.
The specified mask must contain text #ID#.
Note: If the default value is not acceptable, the desired value must be configured before enabling depository databases. Should the value of this parameter be changed after some depository databases have been created, it is necessary to rename all existing depository databases according to the newly specified mask.| Kotva |
|---|
| pg_trezorname |
|---|
| pg_trezorname |
|---|
|
PG_TrezorName - mask for the name of created databases - depository database segments 1..N.
Parameter is used only when TrezorCountSegments > 0. The default value is je appname_TREZOR_#ID#_#SEG#, where appname is the application's name, #ID# is replaced by the depository database number and #SEG# is replaced by the segment number.
E.g. for an application named Test with a number of segments equal to 1, databases Test_TREZOR_1, Test_TREZOR_1_1, Test_TREZOR_2, Test_TREZOR_2_2, Test_TREZOR_3, Test_TREZOR_3_1, etc will be created.
The specified mask must contain text #ID# and #SEG#.
Note: If the default value is not acceptable, the desired value must be configured before enabling depository databases. Should the value of this parameter be changed after some depository databases have been created, it is necessary to rename all existing depository databases according to the newly specified mask.| Kotva |
|---|
| trezorperiodpg |
|---|
| trezorperiodpg |
|---|
|
TrezorPeriod - the period of the depository creation. It is specified in format the format NX, where N is an integer number and X defines a time unit.
Permitted time units are:- H - hour
- D - day
- W - week
- M - month.
Note: Setting the parameter either to an empty value or to 0H disables the functionality of depository databases.
| Kotva |
|---|
| trezorreadsegment0pg |
|---|
| trezorreadsegment0pg |
|---|
|
TrezorReadSegment0 - Parameter has a meaning only if depository segments are configured (non-zero value of TrezorCountSegments parameter). Activation of a parameter (value 1) instructs the archive to read also from depository segment 0 when reading from any other depository segment. Reading from depository databases will be slower, but it enables reconfiguring historical values from default depository segment 0 to a different segment while keeping access to data previously stored in depository segment 0.
The TrezorReadSegment0 parameter can be changed by the SET_OPTION TELL command.| Kotva |
|---|
| trezorreadsincecreatepg |
|---|
| trezorreadsincecreatepg |
|---|
|
TrezorReadSinceCreate - value 1 means that depositories that are older than Create Time of historical value will not be read. The TrezorReadSinceCreate parameter can be changed by the SET_OPTION TELL command.| Kotva |
|---|
| trezorcompressofflinepg |
|---|
| trezorcompressofflinepg |
|---|
|
TrezorCompressOffline - value 1 (default) means that the compressed depository database will be put into offline mode. Value 0 means that the depository database remains in read-only mode and data will be available for reading. Value 2 means that D2000 Archiv will not access the depository database while TrezorPostCompressCmd is running, so this command may perform various maintenance tasks which that could otherwise block the archive.| Kotva |
|---|
| trezorcompresstimepg |
|---|
| trezorcompresstimepg |
|---|
|
TrezorCompressTime - time for releasing the depository database. The database is available for writing delayed data during this period. TrezorCompressTime must be less than TrezorPeriod.| Kotva |
|---|
| trezorcountsegmentspg |
|---|
| trezorcountsegmentspg |
|---|
|
TrezorCountSegments - number of depository database segments (depository databases), that are being created in parallel. The default value is 0 (only segment 0 is being created), and the maximum value is 99 (segments 0 to 99 are being created). The parameter is of DWORD type !!!.| Kotva |
|---|
| trezorpostcompresscmdpg |
|---|
| trezorpostcompresscmdpg |
|---|
|
TrezorPostCompressCmd - the name of a program to be run after releasing the depository database. This program can for example move and compress a disconnected depository database or create a dump of read-only depository database via the PostgreSQL utility pg_dump.
Note: before running the command, the environment variables D2000_DBUSER and D2000_DBPASS are set to the name and password that D2000 Archive uses to access the archive database. These variables can be used in a script to connect to the archive database.| Kotva |
|---|
| trezorpostcompressparpg |
|---|
| trezorpostcompressparpg |
|---|
|
TrezorPostCompressPar - optional parameter, used for the program specified by the parameter TrezorPostCompressCmd. It can contain optional text and predefined macros to be replaced before running the program:- #ID# - is replaced by depository database Id ID (e.g. 5).
- #OID# - is replaced by OID (object identifier) of the depository database (OID is related to the name of the directory containing the respective database inside PostgreSQL tablespace).
- #OID0#, #OID1#, #OID2#, etc - is replaced by OIDs (object identifiers) of databases representing respective depository database segment segments 0, 1, 2, etc.
- #TREZOR# - is replaced by the depository database name (e.g. Test_TREZOR_5).
- #TREZOR0#, #TREZOR1#, #TREZOR2#, etc - is replaced by the database name representing the respective depository database segments.
An example of a batch file used to perform a depository database maintenance and export as well as the export of trezors table located table located in the 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 D2000 user
set PGPASSWORD=%D2000_DBPASS%
rem Set PATH to PostgreSQL installation (version-dependent)
set PATH=%PATH%;c:\Program Files\PostgreSQL\13\bin
rem export of table trezors from the archive database
pg_dump -Fc -U %D2000_DBUSER% -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 %D2000_DBUSER% MyApp.Archiv >> %MyDir%\%1.log
echo alter table data cluster on ix_data_rc | psql -S -U postgres%D2000_DBUSER% %1 >> %MyDir%\%1.log
echo cluster data | psql -S -U %D2000_DBUSER% %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 %D2000_DBUSER% MyApp.Archiv >> %MyDir%\%1.log
pg_dump -Fc -U %D2000_DBUSER% -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=$D2000_DBPASS
#export of table trezors from the archive database
pg_dump -Fc -U $D2000_DBUSER -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 $D2000_DBUSER $MyArc >> $MyLog
echo alter table data cluster on ix_data_rc | psql -S -U $D2000_DBUSER "$1" >> $MyLog
echo cluster data | psql -S -U $D2000_DBUSER "$1" >> $MyLog
#set access to depository database back to read only
echo alter database \"$1\" set default_transaction_read_only=true | psql -S -U $D2000_DBUSER $MyArc >> $MyLog
#execute dump of depository database
pg_dump -Fc -U $D2000_DBUSER -f "$MyDir/$1.dmp" "$1" >> $MyLog