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. | 