MIMER SQL Replication

Owned by Deane Alderman
Last updated: Apr 21, 2022
8 min read

The concept of the Mimer SQL Replication
Categories: Database administration, System Management, Tools and Interfaces

Introduction

In many situations it may be desirable to have copies of data in several databases.
The reason for this may be due to security measures, disaster recovery, to gain performance or just for convenience.

The Mimer SQL Replication mechanism is enables functionality for applying modifications performed towards a source database to defined target databases, keeping them identical in a secure and transaction safe manner.

NB: Currently, the replication product only exists on the Unix/Linux platform.

Description

Some basic terms

Source and target - This article will refer to the original database as the source database, and to the database that is updated by the replication mechanism as the target database.

 

Subscription - Usually we only want to replicate parts of a database. When you initiate a replication you define the tables that are going to be replicated - this set of tables is named a subscription.

The replication programs

repserver - This is the program that performs the actual replication, acting as a service.

mimrepadm - This is the administrative program for the replication environment, for example, used to create the replication idents and subscriptions.

mimsync - This program is used to manually synchronize source and target databases.



Mimer SQL Replication overview
When you have defined your subscription you start the repserver program by invoking it with the subscription name. As long as the repserver process is running, it will make sure that all changes to tables defined in the subscription are replicated to the target database for that subscription. The mechanism is using database triggers to automatically perform the operations. The repserver program is usually run in background.

The tasks performed by a replication system are described in the following simplified picture.

An application is modifying the source database.

Inside the source database replicated tables have a trigger that creates modification information for each operation (insert, update and delete) that is done on the table.

When the repserver program is started it will service a specific subscription. The repserver program will locate the modification information for tables in its subscription, and all the operations will be replicated to the target database.

It should be noted that the replication system does not manage tables without a primary key. Both the source and the target database tables must have primary keys. In addition, all the tables defined within a subscription must be owned by the same database user ident and the databanks must be defined as LOG or TRANSACTION databanks.

NB: The Mimer SQL Replication can handle more than one target.

Environment Setup

Installing the software
There is no separate installation for the Mimer SQL Replication module. When you install Mimer SQL on a platform that supports replication the needed programs will be available.

The core replication system consists of two programs, repserver and mimrepadm - the service and the environment administrator, respectively. In addition there is the mimsync program for ad-hoc table synchronization. Please note that you must have a Mimer License Key installed on the source database server that includes the Replication module.

Furthermore, the Mimer SQL version on the source database must be 9.3.5 or later, and the Mimer SQL version on the target database must be 9.2.4 or later.

Defining source and target databases
Each replicated table must exist in both the source and the target database, and they must be identically defined.

Both the replication mechanism and the mimsync utility handles data only, i.e. no data dictionary operations are handled. If you create a new table in the source database you must create the corresponding table in the target database.

When setting up your first replication environment you will need two or more Mimer SQL databases. You can create new databases or use existing ones, depending upon how you intend to create your initial data. Remember that you cannot use WORK databanks.

There are several possibilities to achieve the replication database environment, for example:

Use an existing database as your source database. Take a complete online backup of that database and use the created backup files as your target database.

Create databases from scratch and define the environment by using the same SQL-script on both the source and target databases.

Filling the target with initial data
The replication mechanism handles only changes to the data. Therefore you must make sure that the source and target tables are identical before starting the replication. In addition, you must keep in mind that data to be replicated must be primary key based. The following are examples on how to handle initial data:

If you used a fresh backup of the source as the initial target, the content is already the same.

If you used SQL-scripts to create the target, the database will usually be empty. In this case you can use the mimload utility to unload data from the source and load it on to the target. Of course you can use mimsync with an empty target, but mimload/unload would be faster.

If you used an older backup, and the source has been modified later on, you can use the mimsync utility to make sure the source and target are the same.

Setting up the replication environment
The mimrepadm program is used to create the initial source and target environment needed for the replication.

On the source database the mimrepadm program creates the users REPADM and REP_SOURCE_USER and the databank REPADM. You run the mimrepadm program with the install option and the name of the source database. You will be prompted for the password of the SYSADM database ident (that you are presumed to know). Then you set the password for REPADM and REP_SOURCE_USER. See the following example, where user input is in italics and the source database name used is sourcedb:

*********************************************************
# mimrepadm --install=source sourcedb
Mimer SQL Replication source dictionary
=======================================
Enter user with ident and grant databank privilege [SYSADM]: sysadm
Password: (input not echoed)
Enter password for REPADM: (input not echoed)
Verify password for REPADM: (input not echoed)

Enter password for REP_SOURCE_USER: (input not echoed)
Verify password for REP_SOURCE_USER: (input not echoed)
Creating ident REPADM...
Creating ident REP_SOURCE_USER...
Creating replication dictionary as REPADM...
DONE.
*********************************************************

On the target database the mimrepadm program creates the user REP_TARGET_USER. See the following example, where user input is in italics and the target database name used is targetdb:

*********************************************************
# mimrepadm --install=target targetdb
Mimer SQL Replication target dictionary
=======================================

Enter user with ident privilege [SYSADM]: sysadm
Password: (input not echoed)
Creating ident REP_TARGET_USER...
Enter password for REP_TARGET_USER: (input not echoed)
Verify password for REP_TARGET_USER: (input not echoed)

DONE.
*********************************************************

Setting up the replication rules (subscriptions)
The mimrepadm program is used to create a subscription, making tables available for replication. It can either take all tables in a given schema or specified tables. The source database user must own all the tables defined in a subscription. See the following example, where user input is in italics and the source database name used is sourcedb and the REPADM password is repadmpw:

*********************************************************
# mimrepadm --rpassword repadmpw sourcedb

REPLICATION>connect source user 'MIMER_STORE' USING 'MIMER_STORE';

REPLICATION>connect target to 'targetdb' user 'MIMER_STORE' using 'MIMER_STORE';

REPLICATION>create subscription SUB1 to TARGETDB for schema MIMER_STORE;
REPLICATION>create subscription SUB2 to TARGETDB for table MIMER_STORE.MUSIC, MIMER_STORE.BOOKS;
*********************************************************

Running the repserver service
Now it is time to start the replication service. In the example below the database user ident REP_SOURCE_USER has the password RSUPW and REP_TARGET_USER has the password RTUPW. The source database is sourcedb and the subscription to replicate is named sub1. Since using the '&', the process will be executed in background.

*********************************************************
# repserver --spassword=RSUPW --tpassword=RTUPW sourcedb sub1 &
*********************************************************

To stop an executing replication service, the following command is used:

*********************************************************
# repserver -t --spassword=RSUPW sourcedb sub1
*********************************************************

Error handling
Most Mimer SQL errors are considered fatal for the repserver program, with exception for 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 warnings that will be written to the repserver log file. The reason for letting these errors be treated as warnings is to make replication possible even if the target table is not identical to the source table.

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

If the repserver program fails to execute an SQL-statement, it will report the failure in its log file. Then it cannot be restarted until the value of the STATUS field in the REP_SUBSCRIPTIONS table is set to 'OK'. First, you need to find out the cause of the problem, correct it, and then modify the STATUS column. Then you can restart repserver program.

The mimsync program
The mimsync program supports synchronization between tables in the source and target database. It can synchronize individual tables or complete subscriptions.

The program operates on pairs of tables, and compares the contents of the two tables and makes both contain the same records. The table in the source database is considered to be the master, which means that the table in the target database will be the one updated.

The SQL statements needed to modify the target table are constructed and grouped into reasonably large transactions.

Synchronization using mimsync
If you have a situation where a specific source and target table is not the same you can use the mimsync program to make sure they have the same content.

For example, you have a table in the source database that contains data. It is currently not replicated but you would like it to be replicated. You create the table in the target database, you synchronize the tables using mimsync, and then adds the table to the subscription.

Another example would be if someone, maybe by mistake, has deleted rows from a replicated table in the target database. The replication mechanism itself will not repair this situation, it only applies the source modifications to the target. To repair the target database you use mimsync to synchronize the two tables.

The following command will synchronize a pair of tables. The included tables must have a primary key and the tables must have identical definitions on source and target:

# mimsync -t --susername=sysadm --spassword=sysadm
--tdatabase=targetdb --tusername=TP --tpassword=cake
--stable=oliner --ttable=oliner sourcedb

To synchronize a subscription mimsync is run as the database user ident REP_SOURCE_USER and REP_TARGET_USER. The following command will synchronize all the tables in the subscription called sub1:

# mimsync -s --logfile=synclog --spassword=REP_SOURCE_USER
--tpassword=REP_TARGET_USER sourcedb sub1


If, for some reason, the source and target databases has come out of sync, the way to get them synchronized again would be to do the following:

Stop any updates to the source database.

 

Make sure there are no pending replication records in the source database. This is done by running the repserver program with the –e option. The repserver program will then replicate all pending operations and then exit.

At this point the table REPADM.REP_LOG and all source log tables (schema.REP_TABLE_name) will be empty. You can verify that the log tables are empty by doing the following SQL statement from the REPADM user ident account:
SELECT COUNT(*) FROM REP_LOG;

 

When the table REP_LOG is empty, you can run mimsync and after that start the replication service again.

Links

For details and further information on Mimer SQL use the Mimer SQL Documentation Set; html navigation, or PDF-file. Most details on the Mimer SQL Replication module and the related programs is found in the section called "Replication" in the System Management Handbook part.

Â