Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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:

 usage: mimrepadm [install-options | options] [source_database]
 
   install-options:
 
     --install=source      Creates the source replication dictionary
     --install=target      Creates the target replication dictionary
     --uninstall=source    Drops the source replication dictionary
     --uninstall=target    Drops the target replication dictionary
 
   options:
 
     -r password, --rpassword=password    REPADM password
                  --susername=user        Source user name
                  --spassword=password    Source password
                  --tdatabase=database    Target database
                  --tusername=user        Target user name
                  --tpassword=password    Target password

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:  2006-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;

Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX