Database

TIF uses by default an embedded database to store its data into. This embedded database is an Apache Derby database.

Another option is to use an external database. This is the preferred option especially if you want to maximize the performance, and/or if there will be multiple TIF server instances in use.

If you have multiple TIF servers in use and don’t use an external database, you need to log-in to each TIF instance to monitor the health and progress of the integration jobs. With an external database, you will be able to see the same information regardless which TIF instance you log in to.

The embedded database works well for Development environments and Test environments.

Embedded Database

Using an embedded database does not require any additional or specific configuration by default. It is the easiest setup and is convenient to use in many cases, although it does not offer the best performance.

The embedded Apache Derby database is per default started up without any network capabilities, meaning that you will not be able to connect to the database over JDBC. It is however possible to enable network access to the database and use some database tool to work with the database.

Connect to the embedded Derby DB

You need the Apache Derby DB / tools installed on your computer. Download these first from this URL:

http://db.apache.org/derby/releases/release-10_14_2_0.cgi

From now on, DERBY_HOME is referred to the directory where you installed the Derby tools.

You can connect in two different ways. The first approach can be used on a NON running TIF instance by connecting directly to the database files:

DERBY_OPTS=-Dderby.system.home=/path/to/tif/data/`hostname`/TIF-1/db.home $\{DERBY_HOME}/bin/ij

When "IJ" starts, connect to the DB using the below syntax.

ij> connect 'jdbc:derby:TIFDB';

The second approach can be done against a running TIF instance. Note however that you need to ensure that the below setting is set within the file etc/tif.custom.properties.

db.mode = mixed

This will enable network access on port 1527 (can be changed) to the Derby DB.

Once TIF is started, it will print out its JDBC URL in the console.

Start IJ and connect:

$\{DERBY_HOME}/bin/ij
ij> connect 'jdbc:derby://127.0.0.1:1527/TIFDB';

Backup

You can perform a database backup of the embedded database by using standard Operating System commands. However, this should only be done when the database is offline.

Another option is to do an online backup using the built-in procedures in the Apache Derby database. To do this, you need to ensure that TIF is running the database with network access enabled (see previous chapter). Secondly, you need to connect to the Derby database using for example the IJ tool (see previous chapter).

Then, read this document to understand the backup procedures:

https://db.apache.org/derby/docs/10.11/adminguide/cadminhubbkup01.html

Maintenance

Prior to doing database maintenance, you need to first shutdown the running TIF instance.

To run maintenance of the internal database, you can use the scripts "tif.bat" or "tif.sh" available in the "bin" folder. This script accepts an argument: dbmaintenance. If supplying this, the database maintenance task will start.

If you have multiple instances of TIF, you must perform the maintenance per instance. Below is an example script (Windows) showing how to run the maintenance task for the instance TIF-1. 
@echo off
setlocal
set EXTRA_OPTS=-Dtif.instance.id=TIF-1 (1)
call tif.bat dbmaintenance
1 Specify the instance ID here

There are some properties within the tif.properties file that are related to this task. These properties are described in the properties file.

External Database

Using an external database is the preferred option especially if there are multiple TIF server instances. This allows deciding whether to use a dedicated database per instance or share it across multiple instances. Sharing a database allows you to work with the same data regardless which TIF Admin UI instance you log in to.

The supported external databases and applied SQL dialects are listed in the below table. Dialect means the variant of the SQL language and defines the required database version. Dialects are typically compatible with later database versions.

|== |Database |SQL Dialect |Oracle |12c |PostgreSQL |10 |SQL Server |2008, 2000 |==

Database Drivers

Database specific drivers are required when using an external database. Due to licensing issues, the TIF server bundle does not include any external driver.

The database driver is distributed by the database vendor. See below links:

Oracle: https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html

PostgreSQL: https://jdbc.postgresql.org/download

SQL Server: https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server

The driver and other related JAR files must be copied into ${TIF_ROOT}/lib/custom.

Configuration

Using an external database requires some additional configuration to be added into the file: ${TIF_ROOT}/etc/tif.custom.properties .

First, the property db.type chooses what type of external database to use. The possible values are oracle, postgresql or mssql.

The minimum required configuration to be applied are host name, port number, database name, user and password.

Non-default database schema/tablespace must be defined by property db.<dbtype>.hibernate.hibernate.default_schema.

See more details in ${TIF_ROOT}/etc/tif.properties.

In case you have multiple TIF server instances sharing the same external database, also see Admin UI configuration.

Oracle Example

# Minimum required settings
db.type = oracle
db.oracle.host = myhostname
db.oracle.port = 1521
db.oracle.database = databasename
db.oracle.user = databaseuser
db.oracle.password = databaseuserpassword

# Additional settings
# db.oracle.hibernate.hibernate.default_schema=...

# Data source customization
# db.oracle.customClassName=...

PostgreSQL Example

# Minimum required settings
db.type = postgresql
db.postgresql.host = myhostname
db.postgresql.port = 5432
db.postgresql.database = databasename
db.postgresql.user = databaseuser
db.postgresql.password = databaseuserpassword

# Additional settings
# db.postgresql.hibernate.hibernate.default_schema=...

# Data source customization
# db.postgresql.customClassName=...

SQL Server Example

SQL Server database must use case insensitive collation. See: https://docs.microsoft.com/en-us/sql/t-sql/statements/collations
If the SQL Server is older than 2008, you must apply SQL Server 2000 dialect. 
# Minimum required settings
db.type = mssql
db.mssql.host = myhostname
db.mssql.port = 1433
db.mssql.database = databasename
db.mssql.user = databaseuser
db.mssql.password = databaseuserpassword

# To apply SQL Server 2000 dialect:
# db.mssql.hibernate.hibernate.dialect=com.technia.tif.core.db.mssql.TIFSQLServer2000Dialect

# Additional settings
# db.mssql.hibernate.hibernate.default_schema=...

# Data source customization
# db.mssql.customClassName=...

Data Source Customization

It is possible to inject a custom Java class that can be used for modifying the data source. The data source is a factory for JDBC connection between the database and the TIF server. The class is specified in the property db.<dbtype>.customClassName.

The class must implement an interface BiConsumer<T extends DataSource, Settings> where T is:

  • Oracle: oracle.jdbc.datasource.OracleDataSource

  • PostgreSQL: org.postgresql.ds.PGSimpleDataSource

  • SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDataSource

Below examples illustrates how to configure and implement a custom class.

tif.custom.properties:

db.mssql.customClassName=com.acme.tif.db.CustomInitializer

The Java class:

package com.acme.tif.db;

import java.util.function.BiConsumer;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

import com.technia.tif.core.Settings;

// This example customizes SQL Server data source
public class CustomInitializer implements BiConsumer<SQLServerDataSource, Settings> {

    @Override
    public void accept(SQLServerDataSource ds, Settings s) {
        // See setter methods exposed by the data source instance
    }

}

Migrating Data

Integration data can be imported from an existing TIF server instance that uses an embedded Derby database. To perform the migration, you can use the scripts "tif.bat" or "tif.sh" available in the "bin" folder. This script accepts an argument called dbmigration, and when supplied the TIF server starts in database migration mode.

For example:

call tif.bat dbmigration
Migration supports importing data from an embedded database to an external database, not vice versa.
The migration logic only supports migration from ONE TIF instance, e.g. if you have multiple TIF instances each using its own embedded Derby database you can only migrate one of those instances.

Before Migration

The following steps needs to be ensured before migration:

  1. The target external database instance must be empty. Do not start or run the TIF server in external database mode before migration.

  2. Files containing the embedded TIF database must be located in ${TIF_ROOT}/data/${NODE_ID}/${INSTANCE_ID}/db.home. E.g. if you want to migrate the data to a new server installation, first copy directory db.home from old TIF server instance.

  3. Take a back-up of the data folders.

  4. The TIF server must be configured to connect to the external database. Migration cannot be started when TIF server is configured to use the embedded database.

The TIF server cannot execute integration activities during migration.

Executing the Migration

Supply the argument dbmigration to start the TIF server in migration mode.

The migration consists of multiple tasks that each import data to one database table.

Depending on the size of the source database, the migration might take from a few minutes up to several hours. The progress can be monitored from the TIF log file or the terminal window where the migration process is started in.

After the migration is completed, the migration process is shut down automatically. To start the TIF server in normal operational mode again, you need to start the TIF server without the dbmigration argument.

If you need to stop the migration process, send the shutdown signal to TIF server by using the stop argument. When doing so, the status of an ongoing migration task is stored so that it can be continued later. Terminating the process e.g. with Ctrl+C combination will not store the status and thus make it difficult or even impossible to continue the migration from where it stopped.

Restarting the Migration

If the migration process is stopped properly, it usually can be continued from the last stored position.

If the process is terminated in an uncontrolled way, forcibly or due to an exception, the process cannot always continue due to conflicting data in the database. In this situation, you can restart a task and its table position. Each task stores its state in a properties file located in directory ${TIF_ROOT}/data/${NODE_ID}/${INSTANCE_ID}/migration. Deleting a file causes the task to start again from the first table row.

However, restarting a task might not be enough to recover if the table in the external database already contains imported rows. Typically, this leads to an exception like "duplicate key value violates unique constraint sometable_pk". In this case, rows needs to be manually deleted from the table with an SQL client. Alternatively, you may set the flag migration.cleanTableOnTaskRestart to true in the ${TIF_ROOT}/tif.custom.properties file.

For example:

migration.cleanTableOnTaskRestart=true

If the flag is true, each task will clean the target table if there is no associated file in migration directory.

The final recover option is to delete all files within the ${TIF_ROOT}/data/${NODE_ID}/${INSTANCE_ID}/migration directiry and also drop all tables in the external database. This will restart all migration tasks from the beginning.

After Migration

After migration, the directory called TIFDB within the directory ${TIF_ROOT}/data/${NODE_ID}/${INSTANCE_ID}/db.home still contains the old Derby database files.

These files will no longer be used by the TIF server. The sub-directory TIFDB can be removed, but it is recommended to keep these until you have verified that the migration was ok and you no longer have a need to switch back to the old embedded database.