Creating Q subscriptions for bidirectional replication

You can create Q subscriptions that specify what changes to capture from either of two tables, what queues to use for exchanging change messages, and how to process the messages. Changes to either of the two tables replicate to the other table.

Before you begin

Before you begin

Plan how you want to group replication queue maps and Q subscriptions.
On the server that has the first copy of the table, create the control tables for the Q Capture and Q Apply programs. The control tables for the Q Capture and Q Apply programs that are on each individual server must have the same schema.
On the server that has the second copy of the table, create the control tables for the Q Capture and Q Apply programs.
Create the two replication queue maps that will transport data between each server. You need one replication queue map for replicating data from the first copy of the table to the second, and one for replicating data from the second copy of the table back to the first. (You can do this task before you create Q subscriptions or while you create Q subscriptions.)

About this task

Restrictions

Stored procedures cannot participate in bidirectional replication.
Because before values of LOB columns are not replicated in bidirectional replication, conflicts for LOB columns are not detected.
Identity columns in target tables that are part of the Q subscription must be defined as GENERATED BY DEFAULT.
Q subscriptions for tables that have referential integrity relationships with each other should be created at the same time (in the same CREATE QSUB command when you are using the ASNCLP command-line program or in the same session with the Create Q Subscriptions wizard in the Replication Center).

About this task

One Q subscription is created to replicate transactions from the first copy of the table to the second copy of the table, and another Q subscription is created to replicate transactions from the second copy of the table back to the first copy. When you create Q subscriptions for bidirectional replication using the ASNCLP command-line program or the Replication Center, the administration tool creates both Q subscriptions at one time.

Procedure

Procedure

To create Q subscriptions for bidirectional replication, use one of the following methods:

Method Description

ASNCLP command-line program

Method	Description
ASNCLP command-line program	Use the CREATE QSUB command for bidirectional replication. For example, the following commands set the environment and create two bidirectional Q subscriptions for the EMPLOYEE table at servers SAMPLE and SAMPLE2: SET SUBGROUP "bidirgroup"; SET BIDI NODE 1 SERVER DBALIAS SAMPLE SCHEMA RED; SET BIDI NODE 2 SERVER DBALIAS SAMPLE2 SCHEMA BLUE; SET CONNECTION SOURCE SAMPLE.RED TARGET "SAMPLE2".BLUE REPLQMAP "SAMPLE_RED_TO_SAMPLE2_BLUE"; SET CONNECTION SOURCE SAMPLE2.BLUE TARGET SAMPLE.RED REPLQMAP "SAMPLE2_BLUE_TO_SAMPLE_RED"; SET TABLES (SAMPLE.RED.RED.EMPLOYEE); CREATE QSUB SUBTYPE B FROM NODE SAMPLE.RED SOURCE ALL CHANGED ROWS Y HAS LOAD PHASE I TARGET CONFLICT RULE C CONFLICT ACTION F FROM NODE SAMPLE2.BLUE SOURCE ALL CHANGED ROWS N HAS LOAD PHASE E TARGET CONFLICT RULE C CONFLICT ACTION I; The SET CONNECTION statements specify the two replication queue maps that are used. The FROM NODE statements specify options that are unique to each Q subscription.
Replication Center	Use the Create Q Subscriptions wizard. To open the wizard, expand the appropriate Q Capture or Q Apply schema, right-click the Q Subscriptions folder, and select Create. On the Target Tables page, review the target object profile. Modify the profile if necessary so that the target tables for the Q subscriptions meet your needs. The target object profile determines if an existing target table is used or if a new one is created. The Replication Center looks for an object that matches the naming scheme that is defined in the profile. If a matching object does not exist, then the object is created.

Use the CREATE QSUB command for bidirectional replication. For example, the following commands set the environment and create two bidirectional Q subscriptions for the EMPLOYEE table at servers SAMPLE and SAMPLE2:

SET SUBGROUP "bidirgroup";

SET BIDI NODE 1 SERVER DBALIAS SAMPLE SCHEMA RED;
SET BIDI NODE 2 SERVER DBALIAS SAMPLE2 SCHEMA BLUE;

SET CONNECTION SOURCE SAMPLE.RED
TARGET "SAMPLE2".BLUE REPLQMAP
"SAMPLE_RED_TO_SAMPLE2_BLUE";
SET CONNECTION SOURCE SAMPLE2.BLUE
TARGET SAMPLE.RED REPLQMAP
"SAMPLE2_BLUE_TO_SAMPLE_RED";

SET TABLES (SAMPLE.RED.RED.EMPLOYEE);

CREATE QSUB SUBTYPE B
FROM NODE SAMPLE.RED SOURCE
ALL CHANGED ROWS Y HAS LOAD PHASE I
TARGET CONFLICT RULE C CONFLICT ACTION F 
FROM NODE SAMPLE2.BLUE SOURCE
ALL CHANGED ROWS N HAS LOAD PHASE E
TARGET CONFLICT RULE C CONFLICT ACTION I;

The SET CONNECTION statements specify the two replication queue maps that are used. The FROM NODE statements specify options that are unique to each Q subscription.

Replication Center

Use the Create Q Subscriptions wizard. To open the wizard, expand the appropriate Q Capture or Q Apply schema, right-click the Q Subscriptions folder, and select Create.

On the Target Tables page, review the target object profile. Modify the profile if necessary so that the target tables for the Q subscriptions meet your needs.

The target object profile determines if an existing target table is used or if a new one is created. The Replication Center looks for an object that matches the naming scheme that is defined in the profile. If a matching object does not exist, then the object is created.