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 Informix® database server installed, but no databases defined.

  1. On all servers, set the CDR_QDATA_SBSPACE configuration parameter.
  2. 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
  3. 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
  4. Create a grid that includes all replication servers in the domain as members of the grid:
    cdr define grid grid1 --all
  5. 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.
  6. 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.
  7. 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.
  8. 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.
  9. 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();
  10. 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.
  11. 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.
  12. 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
    
  13. 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.