Q Replication and Event Publishing

Creating Q subscriptions for peer-to-peer replication with two servers

In peer-to-peer replication with two servers, changes that are made to one copy of a table are replicated to a second copy of that table, and changes from the second copy are replicated back to the first copy. In peer-to-peer replication, all rows and columns are replicated, and the column names in each copy of the table must match.

Before you begin

Before you create Q subscriptions for peer-to-peer replication, you must perform the following actions:

On each server that will participate in peer-to-peer replication, 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.
Create the two replication queue maps that will transport data between the pair of servers. 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.)

Restrictions

Stored procedures cannot participate in peer-to-peer replication.
If the source table includes a LONG VARCHAR column type, that table cannot participate in a peer-to-peer replication. For peer-to-peer replication, the Replication Center or ASNCLP must add two columns to the source table. DB2® for z/OS® does not allow the replication administration tools to add columns to a table that includes a LONG VARCHAR column.
For peer-to-peer and bidirectional configurations, do not use the IMPORT utility. The IMPORT utility logs the inserts and, therefore, the inserts will be recaptured.
In peer-to-peer and bidirectional replication, you must use the same constraints on both the source and target.
Identity columns in target tables that are part of the Q subscription must be defined as GENERATED BY DEFAULT.
Peer-to-peer replication is not supported on systems that use IBM® HourGlass to alter the date and time that is returned when a time request is made. This software alters the version columns that are required for peer-to-peer.

About this task

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

Procedure

To create Q subscriptions for peer-to-peer replication with two servers, use one of the following methods:

Method Description

Method	Description
ASNCLP command-line program	Use the CREATE QSUB command for peer-to-peer replication. For example, the following commands set the environment and create two peer-to-peer Q subscriptions for the EMPLOYEE table at servers SAMPLE and SAMPLPEER: `SET SUBGROUP "p2p2group"; SET PEER NODE 1 SERVER DBALIAS SAMPLE SCHEMA GREEN; SET PEER NODE 2 SERVER DBALIAS SAMPLPEER SCHEMA MAGENTA; SET CONNECTION SOURCE SAMPLE.GREEN TARGET SAMPLPEER.MAGENTA REPLQMAP "SAMPLE_GREEN_TO_SAMPLPEER_MAGENTA"; SET CONNECTION SOURCE SAMPLPEER.MAGENTA TARGET SAMPLE.GREEN REPLQMAP "SAMPLPEER_MAGENTA_TO_SAMPLE_GREEN"; SET TABLES (SAMPLE.GREEN.GREEN.EMPLOYEE); CREATE QSUB SUBTYPE P;` The SET CONNECTION commands specify the two replication queue maps that are used. The SET TABLES command specifies the EMPLOYEE table at the SAMPLE server. A matching copy of the table will be created at the SAMPLPEER server.
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. 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.

ASNCLP command-line program

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

SET SUBGROUP "p2p2group";

SET PEER NODE 1 SERVER DBALIAS SAMPLE
SCHEMA GREEN;
SET PEER NODE 2 SERVER DBALIAS SAMPLPEER
SCHEMA MAGENTA;

SET CONNECTION SOURCE SAMPLE.GREEN
TARGET SAMPLPEER.MAGENTA REPLQMAP
"SAMPLE_GREEN_TO_SAMPLPEER_MAGENTA";
SET CONNECTION SOURCE SAMPLPEER.MAGENTA
TARGET SAMPLE.GREEN REPLQMAP
"SAMPLPEER_MAGENTA_TO_SAMPLE_GREEN";

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

CREATE QSUB SUBTYPE P;

The SET CONNECTION commands specify the two replication queue maps that are used. The SET TABLES command specifies the EMPLOYEE table at the SAMPLE server. A matching copy of the table will be created at the SAMPLPEER server.

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.

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.

Send your feedback | Information roadmap | The Q+SQL Replication Forum

Last updated: 2013-10-25