[IBM Informix] Simple Enterprise Replication setup -- Posted by inturi on Thursday, June 5 2008
Objective:


Basic ER commands to setup Enterprise Replication quickly.


How to go ahead:


Suppose you want to bring up two servers(newyork & sanfrancisco) on the same host(your workstation) and then establish a replication environment between those two servers, to achieve that follow the steps mentioned below:


Steps to follow:


1. Set INFORMIXDIR to the correct location and include $INFORMIXDIR/bin
in your PATH.

2. cd $INFORMIXDIR/etc
cp sqlhosts.demo sqlhosts.newyork
cp sqlhosts.demo sqlhosts.sanfrancisco

3. Add the following entries in sqlhosts.newyork & sqlhosts.sanfrancisco

g_newyork group - - i=10
newyork ontlitcp myunixbox service1 g=g_newyork
g_sanfrancisco group - - i=20
sanfrancisco ontlitcp myunixbox service2 g=g_sanfrancisco

Note:


*) Replace myunixbox with the host name of your workstation.

*) Replace service1 & service2 with the services which you define for these
two servers in the /etc/services file of your workstation.

4. Now on server newyork do the following:
set INFORMIXSERVER=newyork
set INFORMIXSQLHOSTS=sqlhosts.newyork
set ONCONFIG=onconfig.newyork
Modify onconfig.newyork and set CDR_QDATA_SBSPACE ny_sb
oninit -iy
onspaces -c -S ny_sb -p /work/dbspaces/ny_sb -s 20000 -o 0

5. Now on server sanfrancisco do the following:
set INFORMIXSERVER=sanfrancisco
set INFORMIXSQLHOSTS=sqlhosts.sanfrancisco
set ONCONFIG=onconfig.sanfrancisco
Modify onconfig.sanfrancisco and set CDR_QDATA_SBSPACE san_sb
oninit -iy
onspaces -c -S san_sb -p /work/dbspaces/san_sb -s 20000 -o 0

6. On server newyork do the following:

runas informix cdr define serv -c newyork -I g_newyork

7. On server sanfrancisco do the following:

runas informix cdr define serv -c sanfrancisco -I g_sanfrancisco -S g_newyork


At this point we suppose that you have already established an environment for replication between server newyork & sanfrancisco.

Now to define replica and all those things follow the following steps.


Steps:


1. On both the servers newyork and sanfrancisco create a database.
Suppose database name is TEST.

2. Create a table named TAB1 on both the servers. This table should
have a field which has to be primary key. Suppose the columns in
this table are:

c1 integer; (primary key)
c2 integer;

Include CDR columns(with crcols) if you are planning to use timestamp
conflict resoltion rule( -C timestamp option as part of 'cdr define repl').
create table TAB1 (c1 integer primary key, c2 integer) with crcols;

3. Now define a replica for this table like this:

runas informix cdr define repl myrepl -C "timestamp" "TEST@g_newyork:
user1.TAB1" "select * from TAB1" "TEST@g_sanfrancisco:user1.TAB1" "select
* from TAB1"

Note: In the above command, replace user1 with TAB1 user name.

4. By this time replica for this table has been defined but it will
be in an INACTIVE mode. You can check this by this command:

cdr list repl myrepl

5. Activate this replica by this command:

runas informix cdr start repl myrepl

Note : All ER commands must be runas informix on unix and informix admin group user on windows platform


6. Now on any of the server invoke dbaccess, in that connect to TEST and goto Query Language, there try to insert a row in table TAB1 and then check in the other server whether replication has taken place or not.

You can do variety of operation on table TAB1 those changes should get reflicated in the table on other server.



Steps to update windows registry to setup ER group entries:
http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp

Search for "Setting Up the Database Server Group Registry Key" in the above link.