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

 usage: mimsync -s [options] [subs-options]  [source_database] [subscription]
        mimsync -t [options] [table-options] [source_database]
 
    options:
     -s    Synchronizes all tables in a subscription
     -t    Synchronizes a pair of tables
     -l logfile, --logfile=logfile    Logfile name, if omitted standard output
 
   subs-options:
     --spassword=password    REP_SOURCE_USER source password
     --tpassword=password    REP_TARGET_USER target password
 
   table-options:
     --stable=table          Source table
     --ttable=table          Target table
     --susername=user        Source user
     --spassword=password    Source password
     --sprogram=program      Source program
     --susing=password       Source program password
     --tdatabase=database    Target database
     --tusername=user        Target user
     --tpassword=password    Target password
     --tprogram=program      Target program
     --tusing=password       Target program password
 

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:

 2006-11-29 09:33:41.62   <Information>
 ------------------------------------------------------------------------
 Starting Mimer SQL Synchronization
 
 2006-11-29 09:33:42.74   <Information>
 ========================================================================
 Mimer SQL 9.3.3C Beta Test Nov 12 2006
 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
 
 2006-11-29 09:33:42.98   <Information>
 ------------------------------------------------------------------------
 Mimer SQL Synchronization for subscription S STOPPED

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se