Rdbms Export

Configuration

The RdbmsExport module exports historical time series data to tables in a RDBMS. These tables must exist prior to running the module. Notice that in the current version no qualifiers are supported!

The configuration of the module is setup as:

In the sections below the different elements of the configuration are described

General

jdbcDriverClass

JDBC driver class to use for connection to RDBMS.
FEWS installation contains drivers for Oracle, PostgreSQL, Firebird

An Oracle example

<jdbcDriverClass>oracle.jdbc.OracleDriver</jdbcDriverClass>

jdbcConnectionString

Connection string to use by JDBC driver to connect to RDBMS

An Oracle example:

<jdbcConnectionString>jdbc:oracle:thin:@localhost:1521:xe</jdbcConnectionString>

user

Username on the (target) RDBMS.

password

Password for user on the (target) RDBMS.
Encryption of the password in the FEWS configuration is not implemented yet.

exportTimeWindow

Defines the time window for which to export data from FEWS.

<exportTimeWindow unit="day" start="-10" end="0"/>

When setting up an export configuration, one must consider the following:
The RdbmsExport functionality is designed from the idea of a complete export initially (for the configured data sets) and periodic exports henceforth of new and/or mutated data to keep the FEWS datastore and export database in sync.
Identifying new or mutated historical data can only be done within a period of 10 days from the System Time (ST); if the period of 10 days is exceeded a complete export is forced:

• start < ST-10days => complete export
• ST-10days < start <= ST => mutated and new data only

The exportTimeWindow will be applied if it is within the aforementioned 10 days period. Then it will limit the amount of data exported to be within the specified start and end.

exportTimeZone

The time zone in which to export the data from FEWS.

<exportTimeZone>+01:00</exportTimeZone>

moduleInstanceID

Optional list of Module Instance Id's for which to export time series data.

<moduleInstance moduleInstanceID="Statistiek_Percentielen_jaar" />
<moduleInstance moduleInstanceID="Statistiek_Percentielen_seizoen" />

filter

Optional list of Filter Id's for which to export time series data.

<filter filterID="TSI_productie" />
<filter filterID="TMX_ruw" />
<filter filterID="DINO_ruw" />

RDBMS DDL/object creation scripts

Tables for storage of data in the RDBMS must be present before first execution of RDBMS Export module.
Proper priviledges must be assigned to the #user account by the database administrator to insert/update data in these tables as well as (execution) rights on the sequences and triggers in use.

Data model:

DDL scripts:

• Oracle DDL
• PostgreSQL DDL
• Ms SQL Server DDL

Notice that it may be required to increase some column sizes, like locations.description from the default 256 characters to e.g. 1024 characters to be able to store the complete string. If the size in the database is not large enough, the export will stop with an error (data truncation error).

Required database size (disk space)

One record in the TIMESERIEDATA table requires about 300 bytes. This means that 1.000.000 records take about 286 MB (300 * 1e6 million / 1024 /1024).

Additional remarks

• The value of the timestep attribute (a string indicating the time step) in the Timeserie table will depend on the locale/language settings of the computer which is running the export module.