Migrating from SQL Replication to Q Replication

You can migrate your source and target servers from SQL Replication to Q Replication without a full refresh of target tables, and with only a brief outage in replication.

About this task

In this procedure, you set up Q Replication, stop the Capture and Apply programs, and then start the Q Capture program so that it begins reading transactions at the point where the Capture program left off. You can obtain the starting points from a SQL Replication control table.

Note: If you prefer to use a tool to help you migrate from unidirectional SQL replication to unidirectional Q Replication, download the AsnSql2Q.zip file from the InfoSphere® Replication Server samples collection on IBM® My developerWorks®. The sample tool analyzes your SQL Replication subscriptions and generates scripts to set up Q Replication based on your existing options. It also includes MQSC scripts for setting up WebSphere® MQ and documentation to help jump start your migration.

Procedure

Use one of the following methods to define the WebSphere MQ objects that are needed for Q Replication:

Method Description

Method	Description
ASNCLP command-line program	Use the CREATE MQ SCRIPT command to define the objects that you need for a particular configuration. The following example generates a script that creates WebSphere MQ objects for a unidirectional replication configuration where the source and target servers are remote with different queue managers: `CREATE MQ SCRIPT CONFIG TYPE U MQSERVER 1 NAME SOURCEDB MQHOST "9.30.54.118", MQSERVER 2 NAME TARGETDB MQHOST "9.30.54.119";`
Replication Center	Use the MQ Script Generator. Click on the Replication Center menu bar and then select your configuration type.

ASNCLP command-line program

Use the CREATE MQ SCRIPT command to define the objects that you need for a particular configuration. The following example generates a script that creates WebSphere MQ objects for a unidirectional replication configuration where the source and target servers are remote with different queue managers:

CREATE MQ SCRIPT CONFIG TYPE U
MQSERVER 1 NAME SOURCEDB MQHOST "9.30.54.118",
MQSERVER 2 NAME TARGETDB MQHOST "9.30.54.119";

Replication Center Use the MQ Script Generator. Click on the Replication Center menu bar and then select your configuration type.

Create control tables for the Q Capture and Q Apply programs.

Create Q subscriptions for all of your SQL Replication source tables. Specify that the Q subscriptions start automatically when the Q Capture program starts, and also specify no loading of target tables:

Method	Description
ASNCLP program	In the CREATE QSUB command, use the START AUTOMATICALLY YES and HAS LOADPHASE NO keywords.
Replication Center	On the Loading the Target Table page of the Create Q Subscriptions wizard, click None: The target table will not be loaded and Start all Q subscriptions automatically.

This step also involves creating replication queue maps.

Stop the Capture program.
Make sure that the Apply program applies all changes that the Capture program sent before it stopped, and then stop Apply.
Start the Q Capture program with the lsn and maxcmtseq parameters to specify that Q Capture start reading the DB2® recovery log at the point where the Capture program left off. Obtain the values for these two parameters from the IBMSNAP_RESTART table:
lsn

Use the value in the MIN_INFLIGHTSEQ column.

maxcmtseq

Use the value in the MAX_COMMITSEQ column.
Start the WebSphere MQ queue managers at the source and target systems. You can use the following command:
```
strmqm queue_manager_name
```
Start the WebSphere MQ channels and listeners between the source and target systems.
Start the Q Apply program.