Replication

Mimer SQL supports continuous replication of data from a source database to a target database. The replication is based on triggers that store changes for replicated tables in log tables. The replication system reads these log tables and carries out the operations on the target system. The replication system consists of three separate programs, MIMREPADM, REPSERVER and MIMSYNC.

MIMREPADM is a command line based tool for defining which tables that should be replicated. It is also used for install and uninstall of the replication dictionary, which is needed for maintaining information about replicated tables.

REPSERVER handles the actual replication.

The synchronization program, MIMSYNC, performs data manipulation operations to ensure that source and target tables contain the same rows.

Note:The replication system cannot be used to create tables on the target database. It is the responsibility of the user to create these tables.

Requirements

These programs use ODBC for database access. The Mimer SQL version of the source database must be 9.3.5 or later. The Mimer SQL version of the target database must be 9.2.4 or later.

There must be a Mimer license key that allows replication on the source database. No replication license key is required on the target database.

Restrictions

For a replicated table, the value of a primary key column must not be updated.

Note:If a primary key value is updated, that row will not be replicated. In that case mimsync can be used to correct the replicated data. See MIMSYNC - Synchronizing Tables.

MIMREPADM - Replication Administration

This section describes how to set up a replication environment.

Syntax

The following options can be specified as command line arguments for the MIMREPADM program:

mimrepadm -i value|--install=value [source_database]

 

mimrepadm -u value|--uninstall=value [source_database]

 

mimrepadm -r pass|--rpassword=pass

    [--susername=user] [--spassword=pass]

    [--tusername=user] [--tpassword=pass]

    [--tdatabase=name] [source_database]

 

mimrepadm [-v|--version] | [-?|--help]

Command-line Arguments

You can use the following arguments with MIMREPADM.

 

Replication Setup

The first step in setting up a replication environment is to use the install option of the MIMREPADM program. This needs to be done on both the source database and the target database. This is done by running the MIMREPADM program with the following arguments

mimrepadm --install=source [source-database-name]

 

and

mimrepadm --install=target [target-database-name]

 

If the database name is omitted, the program will prompt for a database name. The installations must be run as a user with DATABANK and IDENT privilege. The program will prompt for name and password for such a user.

The installation on the source database consists in creating two users, REPADM and REP_SOURCE_USER. REPADM is the user that will own the replication dictionary. These dictionary tables are created in a databank named REPADM that is created by the install program. REP_SOURCE_USER is the user, which will be used when performing the actual replication on the source database.

On the target database the installation consists in creating the user REP_TARGET_USER. This user is used for performing operations on replicated tables on the target database.

The replication environment can be removed by using the uninstall option for the MIMREPADM program, i.e.

mimrepadm --uninstall=source [source-database-name]

 

and

mimrepadm --uninstall=target [target-database-name]

 

Uninstall is not allowed if there are any subscriptions (see next chapter) defined for the database.

Note:Do not try to remove the replication environment by dropping any users explicitly. Always use the uninstall option for this.

Replication Administration

A subscription defines which tables that should be replicated for a specific source database and target database. It is possible to have multiple subscriptions between the same source and target database, e.g. if tables owned by different users should be replicated. Subscriptions are defined by using the MIMREPADM program. In this case, the MIMREPADM program is started with the following options specified

mimrepadm –r password  database

 

where password is the password for the REPADM user and database is the name of the source database. Before creating a subscription it is required to specify the target database and the users, which own the tables to be replicated. These values can be specified by giving arguments to the MIMREPADM program or by using the CONNECT SOURCE USER and CONNECT TARGET USER statements.

When the MIMREPADM program is started, it will prompt for commands. Since the program reads from standard input and writes to standard output it is possible to use OS primitives for piping and redirection. The following sections describe the available commands.

As a step in the setup process, the target database environment (users, databanks and tables) must be created. This must be done manually, since the replication system cannot be used to any target database objects. (Perhaps MIMLOAD can be useful.)

CREATE SUBSCRIPTION

Creates a subscription.

 

Description

The name of a subscription follows the normal rules for identifiers in SQL (see Mimer SQL Reference Manual for more details.) When a subscription is created, a log table and triggers for logging all write operations are created for each table in the subscription. Since it is only the owner of a table that has the right to create triggers on a table, all tables in a subscription must be owned by the source user. Further requirements is that there is a primary key constraint defined for the replicated table and that the table is not located in a databank having work option. The table used for logging all changes done on the replicated table is created in the same databank in which the replicated table is located. The logging will include information about which transaction the operation belongs to.

When a subscription is created, a corresponding table for each replicated table must exist on the target database. They do not need to have the same name or be located in the same schema as on the source database but the definition must be the same. Within a subscription it is possible to define that a table is replicated to multiple tables on the target database.

Note:There is currently no option for automatically creating the tables in the target database. It is the responsibility of the user to create these tables.

Since the MIMREPADM program grants delete, insert and update privilege on the specified tables to the REP_TARGET_USER user, the target user must have these privileges with grant option for all tables in a subscription.

Example

REPLICATION>create subscription SUB_MIMER_STORE to DUSTPUPPY

REPLICATION& for schema MIMER_STORE as ROC

REPLICATION& interval '10' minute

REPLICATION& commit after 10;

 

This means that all tables in the schema MIMER_STORE will be replicated to similarly named tables in the schema ROC on the database DUSTPUPPY. The interval value specifies at which interval the REPSERVER program will look for data to replicate. The interval literal must be a short interval, i.e. date fields from day to second can be used. (The interval literal format is described in Mimer SQL Reference Manual). The value cannot be negative. The default value is 15 minutes. If a zero interval is given, the replication will be continuous. The commit after clause tells how many source transactions should be bundled in a target transaction. The default value is 1.

REPLICATION>create subscription SUB_MIMER_STORE_MB to CANARDO

REPLICATION& for table MIMER_STORE.MUSIC, MIMER_STORE.BOOKS;

 

Create a subscription for replication of the table MIMER_STORE.MUSIC and MIMER_STORE.BOOKS to the database CANARDO using default value for timing and commit rate. There must exist a schema named MIMER_STORE containing the tables BOOKS and MUSIC on the database CANARDO.

ALTER SUBSCRIPTION

Alters a subscription.

 

Description

The alter subscription statement is used to add or drop tables to an existing subscription. It can also be used to change the default replication interval or the commit rate.

It is not possible to alter a subscription if the REPSERVER program is running for that subscription.

Examples

REPLICATION>alter subscription SUB_MIMER_STORE

REPLICATION& drop table MIMER_STORE.MUSIC;

 

The table MIMER_STORE.MUSIC will not be replicated any more. The triggers defined on this table and the log table will be dropped.

REPLICATION>alter subscription SUB_MIMER_STORE commit after 1;

 

Change the transaction rate so that each transaction on the source database will be treated as one transaction on the target database.

DROP SUBSCRIPTION

Drops a subscription.

 

Description

Drop all information about a subscription from the replication dictionary. It will also drop triggers and the log table, which were created when the subscription was created.

It is not possible to drop a subscription if the REPSERVER program is running for that subscription.

Example

REPLICATION>DROP SUBSCRIPTION SUB_MIMER_STORE;

DESCRIBE SUBSCRIPTION

Describes a subscription.

 

Description

Display information about the specified subscription.

Example

REPLICATION>describe subscription SUB_MIMER_STORE_MB;

 

Subscription SUB_MIMER_STORE_MB:

 

Target database: CANARDO

Interval:        600 second(s)

Commit after:    1

SYSTEM_STARTUP:  2010-08-27 18:08:52

TRANSNO:         4711

SEQNO:           12

STOPPING_FLAG:   NO

 

Tables:

 

Source: MIMER_STORE.MUSIC

Target: MIMER_STORE.MUSIC

 

Source: MIMER_STORE.BOOKS

Target: MIMER_STORE.BOOKS

LIST SUBSCRIPTIONS

Lists subscriptions.

 

Description

Lists all defined subscriptions

Example

REPLICATION>LIST subscriptions;

 

Subscriptions

=============

MIMER_STORE

MIMER_STORE_MB

CONNECT SOURCE USER

Connects user to source database.

 

Description

Connect the table owner to the source database.

Example

REPLICATION>CONNECT SOURCE USER MIMER_STORE

REPLICATION& using 'GoodiesRUs';

CONNECT TARGET USER

Connects user to target database.

 

Description

Specify user on target database.

Example

REPLICATION>CONNECT TARGET to CANARDO

REPLICATION& user MIMER_STORE using 'niTeoW1';

DISCONNECT SOURCE

Disconnects user from source database.

 

Description

Disconnect the user connected to the source database.

Example

REPLICATION>DISCONNECT SOURCE;

DISCONNECT TARGET

Disconnects user from target database.

 

Description

Disconnect the user connected to the target database.

Example

REPLICATION>DISCONNECT TARGET;

ENTER SOURCE

Connects a PROGRAM ident to source database.

 

Description

Connects a PROGRAM ident to the source database.

Example

REPLICATION>ENTER SOURCE 'PgmIdnt' USING 'SecrtPlees';

ENTER TARGET

Connects a PROGRAM ident to target database.

 

Description

Connects a PROGRAM ident to the target database.

Example

REPLICATION>ENTER TARGET 'PgmIdnt' USING 'SecrtPlees';

LEAVE SOURCE

Leaves a PROGRAM ident from the source database.

 

Description

The current source PROGRAM ident is left and the saved environment of the previous ident is restored.

Example

REPLICATION>LEAVE SOURCE;

LEAVE TARGET

Leaves a PROGRAM ident from the target database.

 

Description

The current target PROGRAM ident is left and the saved environment of the previous ident is restored.

Example

REPLICATION>LEAVE TARGET;

SHOW SETTINGS

Shows source and target info.

 

Description

Display information about source and target user.

Example

REPLICATION>SHOW SETTINGS;

 

Settings

========

Source database: MOONBASE_ALPHA

Source user:     MIMER_STORE

Target database: CANARDO

Target user:     MIMER_STORE

 

Source program:  PgmIdnt

Target program:  Not connected

EXIT

Exits MIMREPADM.

 

Description

Exit from the MIMREPADM program.

Example

REPLICATION>EXIT;

REPSERVER - Replicating the Data

The actual replication is performed by running the REPSERVER program. This program will handle the replication for one subscription. This program connects to source database as REP_SOURCE_USER and to the target database as REP_TARGET_USER.

MIMSYNC is typically used before replication is first set up, or has been halted for some reason, to make sure that source and target tables have the same contents. After the synchronization, the replication functionality ensures that the tables remain identical.

Syntax

repserver [-e|--exit] [-l file|--logfile=file] [-d|--verbose] [--spassword=pass]

    [--spassword=pass] [source_database] [subscription]

 

repserver -t [--spassword=pass] [source_database] [subscription]

 

repserver [-v|--version] | [-?|--help]

 

If any required parameter is omitted, the program will prompt for these values.

Command-line Arguments

You can use the following arguments with REPSERVER.

The REPSERVER program will read the log tables for all tables in the subscription and perform the same operations on the target database. After each commit on the target database the data in the log tables will be deleted. Once all operations have been done the program will sleep for the rest of the interval specified for the subscription. If the interval for the subscription is set to 0 the program will poll the log tables for any data continuously.

Start the Replication

To start the replication for a subscription the REPSERVER program can be started with following command line arguments:

repserver [--spassword=password] [--tpassword=password] [--logfile=logfile]
   [--exit] database [subscription-name]

 

Note:The REPSERVER program should normally be run as a detached process on VMS, or as a background process on Linux.

Stop the Replication

To stop the replication the REPSERVER program should be run with the following options

REPSERVER –t [--spassword=password] database subscription-name

 

This will set the stopping flag in the replication dictionary to ‘YES’ for the specified subscription. The REPSERVER program will periodically look at this flag, when not active. This means that it can take some time before the replication is stopped.

Error handling

Most Mimer SQL errors are considered fatal for REPSERVER, with the exception of the following three:

•Error -10101, INSERT operation invalid because the resulting table will contain a primary key duplicate

•Error -10110, unique constraint violation

•Error 100, record for update or delete not found

These errors will only result in a warning. This is to make replication possible even if the target table is not identical to the source table.

If REPSERVER gets a transaction conflict, it will try to execute the transaction once more. If the second attempt fails REPSERVER considers this a fatal error.

MIMSYNC - Synchronizing Tables

A third component in the replication service is the MIMSYNC program. It is typically run in batch and operates on pairs of tables, where one table resides in the source database and the other resides in the target database. Data manipulation operations are performed to ensure that the two tables contain the same rows. The table in the source database is considered to be the master, which means that MIMSYNC will only modify (delete/insert/update) the table in the target database.

Synchronization can in some cases be used instead of replication. If the replication updates only need to be performed, say, every 24 hours, this could be done by a batch job running MIMSYNC each night.

The MIMSYNC program supports synchronization between tables in the source and target database. The program operates on pairs of tables, and compares the contents of the two tables in a pair and makes both contain the same records. The table in the source database is considered to be the master, which means that it is the table in the target database that will be updated. The SQL statements needed to modify the target table are constructed and grouped into reasonably large transactions (1 000 rows).

Syntax

mimsync -s|--subscription [-n|--noexecute] [-l file|--logfile=file]

    [subs-options] [source_database] [subscription]

 

mimsync -t|--table [-n|--noexecute] [-l file|--logfile=file]

    [table-options] [source_database]

 

mimsync [-v|--version] | [-?|--help]

Options

Subs-options

Table-options

If the database, user and/or password switches are not given the program will prompt for database, user and/or password. The other switches are optional.

Examples

$ mimsync -s --logfile=synclog --spassword=secret --tpassword=secret
   sourcedb subs1

 

$ mimsync -t --suuser=SrcUsr --spassword=scrt
   --tdatabase=TrgDb --tuser=TrgUsr --tpassword=scrt
   table1 table2 SrcDb

Authorization

When synchronizing a subscription MIMSYNC is run as REP_SOURCE_USER and REP_TARGET_USER.

When synchronizing a pair of tables MIMSYNC can be run as any user (having SELECT and INSERT rights).

Restrictions

•The tables must have a primary key

•The tables must have the same definition

Note:MIMSYNC may fail to synchronize a table that has a foreign key reference to itself. Also circular foreign keys may cause problems.

Output

When executing MIMSYNC, it will write execution information to its log file. (If no log file is specified, standard output will be used.)

Example log file:

2019-11-29 09:33:41.62   <Information>

------------------------------------------------------------------------

Starting Mimer SQL Synchronization

 

2019-11-29 09:33:42.74   <Information>

========================================================================

Mimer SQL 11.0.1A

Mimer SQL Synchronization

for subscription S on database SOURCEDB STARTED

 

Synchronizing table SYSADM.T1...

Synchronization of table SYSADM.T1 complete

 

Synchronizing table SYSADM.T3...

Synchronization of table SYSADM.T3 complete

 

2019-11-29 09:33:42.98   <Information>

------------------------------------------------------------------------

Mimer SQL Synchronization for subscription S STOPPED