How to set up Enterprise Replication for the first time

Resolving The Problem

INTRODUCTION

The following article provides steps to set up a basic Enterprise Replication (ER) system between two database servers. The steps outline only the basic options for setting up ER on your systems. There are many other ways to configure ER. Please refer to Guide to IBM Informix Enterprise Replication for more information regarding definitions of the commands used below, and all the available options.


BEFORE YOU BEGIN

In order to follow the steps provided below:

• It is assumed that you have permission to administer all database server instances, including the ability to:
• Stop and restart the database server
• Add dbspaces and chunks
• Change the database servers networking settings
• Edit the ONCONFIG configuration file
• For ease of setup, use the same version of IBM Informix® Dynamic Server™ (IDS) on both systems. While using different versions of IDS is supported, it is not the intent of this document to explain or prepare you for implementing a mixed version ER environment. For more information about which versions are supported in an ER environment, please contact IBM Technical Support.

Preparing for ER
The following are requirements before starting ER on your systems:

• If using Conflict Resolution other than 'Ignore' for a replicate - each table will need to have the CRCOLS columns added:
• for existing tables, run the command:
  ALTER table tabname add CRCOLS;
• for new tables, specify "with CRCOLS:
  CREATE table tabname (...) with CRCOLS;’
• If defining replicates using a select statement like ‘select * from tabname’, the base tables must be identical on all instances to be involved for this replicate.
• A smart blob space to hold spooled data needs to be created on each server, and listed in the ONCONFIG configuration file setting CDR_QDATA_SBSPACE.
• All databases that will take part in enterprise replication are logged, preferably with unbuffered logging.
• All tables involved with replication need a PRIMARY KEY constraint defined on at least one column, otherwise you must use the WITH ERKEY keywords in the table creation statement of any table that will be involved in replication and will not have a Primary Key defined.

For example,
create table "informix".customer
(
customer_num serial not null ,
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18)
)
WITH ERKEY;

• If a table that is to be used in ER has already been created without a PRIMARY KEY and without using the WITH ERKEY keywords, then you will either need to recreate the table defining a PRIMARY KEY, or run an ALTER TABLE statement with the ADD ERKEY clause.

For example, if the customer table above was created without the WITH ERKEY keywords, we could run the following statement to add the ERKEY shadow columns:
ALTER TABLE customer ADD ERKEY;

• All database servers to be involved in enterprise replication are already configured for general use and are operating correctly.
• All computers involved in ER have a trusted relationship with each other. To determine if your host is trusted by the other host(s) in the ER system, run:
  dbaccess -> connection -> connect
  You should be able to connect to the Group Name and the Servername of the other host(s) without using a password.
• Time on all servers to be involved in ER must be synchronized with each other (specific to there own local time zone).
  

NOTE: Table and Database names on all ER servers do not need to be the same.


STEPS

1. Login as user informix.

2. Set your environment to administer the database server.

NOTE: Verify that the INFORMIXSERVER variable in your environment is set for the network connection and not the shared memory connection.

Example: INFORMIXSERVER=unix1tcp

3. Set up the $INFORMIXDIR/etc/sqlhosts (or $INFORMIXSQLHOSTS) file:
The sqlhosts file must be modified for use with ER, defining server and group numbers for each server.

Example prior to modification:
An example of an sqlhosts file from the UNIX server 'unix1' before modified for ER:

unix1shm         onipcshm     unix1  unix1shm
unix1tcp         ontlitcp     unix1  unix1tcp
unix2tcp         ontlitcp     unix2  unix2tcp

The unmodified sqlhosts file from UNIX server 'unix2':

unix2shm         onipcshm     unix2  unix2shm
unix2tcp         ontlitcp     unix2  unix2tcp
unix1tcp         ontlitcp     unix1  unix1tcp

NOTE: The two servers must be configured to have connectivity to each other. This means that the /etc/services file has entries for the TCP services listed in the sqlhosts file, and there may also be entries in the /etc/hosts or .rhost files for these servers.

Make modifications to define server numbers and group names for ER on each of your servers:

Example after modification for ER:
The unix1 server:
unix1grp    group       -      -          i=1
unix1tcp    ontlitcp    unix1  unix1tcp   g=unix1grp

unix2grp    group       -      -          i=2
unix2tcp    ontlitcp    unix2  unix2tcp   g=unix2grp

unix1shm    onipcshm    unix1  unix1shm

The unix2 server:
unix2grp    group       -      -          i=2
unix2tcp    ontlitcp    unix2  unix2tcp   g=unix2grp

unix1grp    group       -      -          i=1
unix1tcp    ontlitcp    unix1  unix1tcp   g=unix1grp

unix2shm    onipcshm    unix2  unix2shm

NOTE: Check the Machine Specific Notes (located in a subdirectory of the $INFORMIXDIR/release directory, /en_us/0333 for english) to determine the proper network protocol (tli or socket connections) for your platform.

4. Define the ER servers using the cdr define server command:

Example:
On unix1, run: cdr define server -A atsdir -R risdir -I unix1grp
On unix2, run: cdr define server -A atsdir -R risdir -I unix2grp \ -S unix1grp

atsdir - path to a directory to store files that ER will create, example:
/usr/informix/ats
risdir - path to a directory to store files that ER will create, example:
/usr/informix/ris

(The slash '\' character is for line continuation.)

To ensure the servers have been defined correctly:

• check the output of the cdr list server command:

Example output:

SERVER       ID STATE    STATUS     QUEUE  CONNECTION CHANGED
-------------------------------------------------------------
unix1grp      1 Active   Local           0
unix2grp      2 Active   Connected       0  Nov 21 15:53:04


• Check the message log for successful build messages:

On unix1grp:
15:45:24  Building 'syscdr' database ...
15:45:28  'syscdr' database built successfully.
15:45:28  Loading Module <SPLNULL>
15:45:31  CDR queuer initialization complete
15:45:31  CDR NIF listening on asf://unix1grp

On unix2grp:
15:52:55  Building 'syscdr' database ...
15:52:59  'syscdr' database built successfully.
15:52:59  Loading Module <SPLNULL>
15:53:01  CDR queuer initialization complete
15:53:01  CDR NIF listening on asf://unix2grp
15:53:05  CDR GC: synchronization complete

NOTE: The message log for unix2grp shows an additional message
CDR GC: synchronization complete
This indicates that unix2grp has successfully connected to, and synchronized with, unix1grp.


• Run on unix1grp the command:
onstat -g nif

Example output:
NIF anchor Block: b777470
        nifGState               RUN
           RetryTimeout         300

CDR connections:
 Id    Name     State       Version       Sent   Received
---------------------------------------------------------
   2 unix2grp   RUN               7         14          6


• Run on unix2grp the command:
onstat -g nif
Example output:
NIF anchor Block: b777470
        nifGState               RUN
           RetryTimeout         300

CDR connections:
 Id    Name     State       Version       Sent   Received
---------------------------------------------------------
   1 unix1grp   RUN               7         15          6

5. Define the replicates using the cdr define repl command.

Example:
The commands below are based on the state table from the stores_demo database. To create the stores_demo databases, run the command on both servers:
dbaccessdemo7 -log;

To create a replicate named 'rep_state' using Update Anywhere syntax:

cdr define repl -c unix1grp -C timestamp -S tran -A rep_state \
"stores_demo@unix1grp:informix.state" "select * from state" \
"stores_demo@unix2grp:informix.state" "select * from state"

To create a replicate named 'rep_state' using Primary/Target syntax:

cdr define repl -c unix1grp -C ignore -S tran -A rep_state \
"P stores_demo@unix1grp:informix.state" "select * from state" \
"R stores_demo@unix2grp:informix.state" "select * from state"

(The slash '\' characters are for line continuation.)

NOTE: Timestamp conflict resolution requires the addition of shadow columns to the replicated table. Refer to the 'Preparing for ER' section for details on how to add the shadow columns to new and existing tables. Conflict Resolution with any setting other than ignorewith a Primary/Target configuration will cause error 69.


To confirm the replicates are defined correctly, run the following command from both ER servers:

unix1grp > cdr list repl

Example output:

CURRENTLY DEFINED REPLICATES
-------------------------------
REPLICATE:     rep_state
STATE:         Inactive
CONFLICT:      Ignore
FREQUENCY:     immediate
QUEUE SIZE:    0
PARTICIPANT:   stores_demo:informix.state
OPTIONS:       transaction,ats,fullrow

6. Start the replicate using the cdr start repl replicate command on either server.

Example:
cdr start repl rep_state

To confirm the command was successful:

• Run the following command on either server:

cdr list repl

Example output:

CURRENTLY DEFINED REPLICATES
-------------------------------
REPLICATE:     rep_state
STATE:         Active
CONFLICT:      Ignore
FREQUENCY:     immediate
QUEUE SIZE:    0
PARTICIPANT:   stores_demo:informix.state
OPTIONS:       transaction,ats,fullrow

A replicate status of Active indicates that the replicate is defined and running on both servers.


• Insert a new record on the primary server if using Primary/Target syntax, or from either server if using Update Anywhere syntax.