Update-Anywhere Example

This example builds on the primary-target example and creates a simple update-anywhere replication.

In update-anywhere replication, changes to any table in the replicate are replicated to all other tables in the replicate. In this example, any change to the stock table of the stores database on any database server in the replicate will be replicated to the stock table on the other database servers.

In this example, define the repl2 replicate.

To prepare for update-anywhere replication

Define the replicate, repl2:
```
cdr define replicate --conflict=ignore repl2 \ 
"stores@g_usa:informix.stock" "select * from stock" \
"stores@g_italy:informix.stock" "select * from stock"
```
These lines are all one command. The backslashes (\) at the end of the lines indicate that the command continues on the next line.
This step specifies that conflicts should be ignored and describes two participants, usa and italy (including the table and the columns to replicate) in the replicate.
Because neither P (primary) nor R (receive-only) is specified, the replicate is defined as update-anywhere. If any data in the selected columns changes, on either participant, that changed data should be sent to the other participants in the replicate.

Display all the replicates so that you can verify that your definition of repl2 succeeded:

cdr list replicate

The command returns the following information:

CURRENTLY DEFINED REPLICATES
---------------------------------------------------------
REPLICATE:     repl1
STATE:         Active
CONFLICT:      Ignore
FREQUENCY:     immediate
QUEUE SIZE:    0
PARTICIPANT:   g_usa:informix.manufact
               g_italy:informix.manufact

REPLICATE:     repl2
STATE:         Inactive
CONFLICT:      Ignore
FREQUENCY:     immediate
QUEUE SIZE:    0
PARTICIPANT:   g_usa:informix.stock
               g_italy:informix.manufact

Although this output shows that repl2 exists, it does not show the participant modifiers (the SELECT statements) for repl2.

Display the participant modifiers for repl2:

cdr list replicate repl2

This command returns the following information:

REPLICATE  TABLE                         SELECT
------------------------------------------------------------
repl2      stores@g_usa:informix.stock   select * from stock
repl2      stores@g_italy:informix.stock select * from stock

Add the japan database server to the replication system already defined in the previous example:
```
cdr define server --connect=japan --init \
--sync=g_usa g_japan
```
You can use either g_usa or g_italy in the --sync option.
Enterprise Replication maintains identical information on all servers that participate in the replication system. Therefore, when you add the japan database server, information about that server is propagated to all previously-defined replication servers (usa and italy).

Display the replication servers so that you can verify that the definition succeeded:

cdr list server

The command returns the following information:

SERVER    ID STATE    STATUS    QUEUE   CONNECTION CHANGED
----------------------------------------------------------
g_italy    8 Active   Connected 0       JUN 14 14:38:44 2000
g_japan    6 Active   Connected 0       JUN 14 14:38:44 2000
g_usa      1 Active   Local     0

Add the participant and participant modifier to repl2:

cdr change replicate --add repl2 \
"stores@g_japan:informix.stock" "select * from stock"

Display detailed information about repl2 after adding the participant in step 6:

cdr list replicate repl2

The command returns the following information:

REPLICATE  TABLE                         SELECT
------------------------------------------------------------
repl2      stores@g_usa:informix.stock   select * from stock
repl2      stores@g_italy:informix.stock select * from stock
repl2      stores@g_japan:informix.stock select * from stock

Make the replicate active:
```
 cdr start repl2
```

Display a list of replicates so that you can verify that the STATE of repl2 has changed to ACTIVE:

cdr list replicate

The command returns the following information:

CURRENTLY DEFINED REPLICATES
------------------------------------------------------
REPLICATE:     repl1
STATE:         Active
CONFLICT:      Ignore
FREQUENCY:     immediate
QUEUE SIZE:    0
PARTICIPANT:   g_usa:informix.manufact
               g_italy:informix.manufact

REPLICATE:     repl2
STATE:         Active
CONFLICT:      Ignore
FREQUENCY:     immediate
QUEUE SIZE:    0
PARTICIPANT:   g_usa:informix.stock
               g_italy:informix.manufact
               g_japan:informix.manufact

Now you can modify the stock table on one database server and see the change reflected on the other database servers.

To cause a replication

Use DB-Access to insert a line into the stock table on usa:

INSERT INTO stores@usa:stock VALUES (401, “PRC”, “ski boots”, 200.00, 
                “pair”, “pair”);

Observe the change on the italy and japan database servers:

SELECT * from stores@italy:stock;
SELECT * from stores@japan:stock;

To update the stock table on the japan database server

Use DB-Access to change a value in the stock table on japan:

UPDATE stores@japan:stock SET unit_price = 190.00
WHERE stock_num = 401;

Verify that the change has replicated to the stock table on usa and on italy:

SELECT * from stores@usa:stock WHERE stock_num = 401;
SELECT * from stores@italy:stock WHERE stock_num = 401;