Example of setting up a replication system with a grid

This comprehensive example sets up a replication domain, creating a grid, creating a database, creating a replicated table, and loading data.

This example creates a replication domain and grid that contain four replication servers: serv1, serv2, serv3, serv4. Each server computer has the HCL OneDB™ database server installed, but no databases defined.

On all servers, set the CDR_QDATA_SBSPACE configuration parameter.

Edit the sqlhosts files on all four servers so that they each have the following information:

#dbservername   nettype   hostname              servicename   options
gserv1          group     -                     -             i=143
serv1           ontlitcp  ny.usa.com            1230          g=gserv1
gserv2          group     -                     -             i=144
serv2           ontlitcp  tokyo.japan.com       1231          g=gserv2
gserv3          group     -                     -             i=145
serv3           ontlitcp  rome.italy.com        1232          g=gserv3
gserv4          group     -                     -             i=146
serv4           ontlitcp  perth.australia.com   1233          g=gserv4

Define each server as a replication server by running the cdr define server command:

cdr define server -c gserv1 -I gserv1
cdr define server -c gserv2 -S gserv1 -I gserv2
cdr define server -c gserv3 -S gserv1 -I gserv3
cdr define server -c gserv4 -S gserv1 -I gserv4

Create a grid that includes all replication servers in the domain as members of the grid:
```
cdr define grid grid1 --all
```
Authorize the user bill to run commands on the grid and designate the server gserv1 as the source server from which grid commands can be run:
```
cdr enable grid --grid=grid1 --user=bill --node=gserv1
```
Tip: User informix does not have permission to run grid operations unless you include it in the user list.

Run cdr list grid to see the grid configuration:

Grid               Node               User
------------------ ------------------ ----------------
grid1              gserv1*            bill
                   gserv2
                   gserv3
                   gserv4

The asterisk indicates that gserv1 is the source server for the grid.

Run the cdr list replicateset command to see the grid replicate set information:
```
Ex T REPLSET                  PARTICIPANTS
-----------------------------------------------
Y  Y  grid1                         
```
The replicate set has the same name as the grid. It does not yet contain any participants.

Create two dbspaces named dbsp2 and dbsp3 in which to fragment a table:

database sysmaster;

EXECUTE FUNCTION ifx_grid_function('grid1', 
		'task("create dbspace","dbsp2",
          "/db/chunks/dbsp2","2G","0")');

EXECUTE FUNCTION ifx_grid_function('grid1', 
		'task("create dbspace","dbsp3",
          "/db/chunks/dbsp3","8G","0")');

The dbspaces are created on all four servers.

Create database named retail and a table named special_offers with replication enabled:

database sysmaster;

EXECUTE PROCEDURE ifx_grid_connect('grid1', 1);

CREATE DATABASE retail WITH LOG;

CREATE TABLE special_offers(
    offer_description varchar(255),
    offer_startdate   date,
    offer_enddate     date,
    offer_rules       lvarchar,
    offer_type        char(16))
    WITH CRCOLS
FRAGMENT BY EXPRESSION
    offer_type = "GOLD" IN dbsp2,
    REMAINDER IN dbsp3;

EXECUTE PROCEDURE ifx_grid_disconnect();

Run the cdr list grid --verbose grid1 command to see information about the statements on each server:

Grid               Node               User
------------------ ------------------ ----------------
grid1              gserv1*            bill
                   gserv2
                   gserv3
                   gserv4
Details for grid grid1

Node:gserv1 Stmtid:1 User:bill Database:retail 2010-05-27 15:21:57
CREATE DATABASE retail WITH LOG;
ACK gserv1 2010-05-27 15:21:57
ACK gserv2 2010-05-27 15:21:58
ACK gserv3 2010-05-27 15:21:59
ACK gserv4 2010-05-27 15:21:59

Node:gserv1 Stmtid:1 User:bill Database:retail 2010-05-27 15:21:57
CREATE TABLE special_offers(
    offer_description varchar(255),
    offer_startdate   date,
    offer_enddate     date,
    offer_rules       lvarchar
    offer_type        char(16))
    WITH CRCOLS
FRAGMENT BY EXPRESSION
    offer_type = "GOLD" IN dbsp2
    REMAINDER IN dbsp3;
ACK gserv1 2010-05-27 15:21:57
ACK gserv2 2010-05-27 15:21:58
ACK gserv3 2010-05-27 15:21:59
ACK gserv4 2010-05-27 15:21:59

Both statements succeeded on all four servers.

Run cdr list replicate to see the replicate information:

CURRENTLY DEFINED REPLICATES
---------------------------------------------
REPLICATE:        gserv1_1
STATE:            Active
CONFLICT:         Timestamp
FREQUENCY:        immediate
QUEUE SIZE:       0
PARTICIPANT:      retail:bill.special_offers
OPTIONS:          
REPLTYPE:         Master,Grid

The replicate was created and is active.

Run the cdr list replicate brief gserv1_1 command to see the participants:

REPLICATE  TABLE                              SELECT
----------------------------------------------------------------
gserv1_1   retail@gserv1:bill.special_offers  select * from 
                                               bill.special_offers
gserv1_1   retail@gserv2:bill.special_offers  select * from 
                                               bill.special_offers
gserv1_1   retail@gserv2:bill.special_offers  select * from 
                                               bill.special_offers
gserv1_1   retail@gserv2:bill.special_offers  select * from 
                                               bill.special_offers

Load data onto one of the replication servers and Enterprise Replication replicates the data to the other servers. For more information, see Load and unload data.