Step By Step Guide To Q Replication i2i2d

Step by step Guide to Q Replication DB2 9.7 Database

High level Structure of Q Replication between two DB2 databases. SOURCE DB

TARGET DB

Learning objectives • Create and use MQ queue managers, queues, and channels. • Enable a DB2 database for replication. • Set up and operate the Q Capture and Q Apply programs. • Configure the Q Capture and Q Apply programs to use the queues. • Create a Q subscription to map a source table to a target table.

Setting up MQ

The two queue managers interact with the Q Capture and Q Apply programs, queues, and channels to rapidly move data in the form of messages. The figure shows two paths for messages: one for replicated data and communication from the source, and one that the Q Apply program uses to communicate with the Q Capture program. The source-to-target channel is depicted as larger because it carries replicated transactions, which are the bulk of the messaging traffic

Creating the source and target queue managers SOURCE SERVER

• Create the source queue manager: • crtmqm SRC_QM • Start the source queue manager: • strmqm SRC_QM

TARGET SERVER

• Create the target queue manager: • crtmqm TGT_QM • Start the target queue manager: • strmqm TGT_QM

Creating the source queues SRC_QM queue manager: Start an interactive session for The SRC_QM using runmqsc SRC_QM From the session create the Required queues. • queue: used by the Q Capture program to communicate with the Q Apply program at the target DEFINE QLOCAL ('Q') DEFPSIST(YES) • Restart queue : stores restart information for the Q Capture program. DEFINE QLOCAL ('RESTARTQ') DEFPSIST(YES) • Transmission queue: holds messages before they cross the channel DEFINE QLOCAL('TGT_QM') USAGE(XMITQ) DEFPSIST(YES) • Send queue : remote queue definition that the Q Capture program uses to direct messages that contain replicated data to the receive queue at the target. DEFINE QREMOTE('SENDQ') RNAME('RECVQ') RQMNAME('TGT_QM') XMITQ('TGT_QM') DEFPSIST(YES)

Creating the target queues TGT_QM queue manager: Start an interactive session for The SRC_QM using runmqsc TGT_QM From the session create the Required queues. • Receive queue: receives messages from the Q Capture program. DEFINE QLOCAL('RECVQ') DEFPSIST(YES) • Transmission queue: holds messages before they cross the channel DEFINE QLOCAL('SRC_QM') USAGE(XMITQ) DEFPSIST(YES) • Queue: used by the Q Apply program to communicate with the Q Capture program at the source. Remote queue definition that points to the source istration queue: DEFINE QREMOTE('Q') RNAME('Q') RQMNAME('SRC_QM') XMITQ('SRC_QM') DEFPSIST(YES) • Spill queue: is a model queue definition. Spill queues are created dynamically to hold any transactions that arrive from the source while the target table is being loaded. DEFINE QMODEL('IBMQREP.SPILL.MODELQ') MSGDLVSQ(FIFO) DEFTYPE(PERMDYN)

Creating MQ channels Define Channel objects at the source Start an interactive session for The SRC_QM using runmqsc SRC_QM From the session create the Required Channels. • Define the sender channel from the SRC_QM queue manager to the TGT_QM queue manager. Use the T/IP transmission protocol DEFINE CHL ('SRC_QM.TO.TGT_QM') CHLTYPE(SDR) TRPTYPE(T) CONNAME ('IP_address(port)') XMITQ('TGT_QM') DISCINT(0) • Define a receiver channel at the SRC_QM queue manager from the TGT_QM queue manager. DEFINE CHL ('TGT_QM.TO.SRC_QM') CHLTYPE(RCVR) TRPTYPE(T)

* In this Presentation source Server port is identified as 1450 and destination Server port is identified as 1451. The Ports numbers are subject to availability. Default MQ port is 1414 Contd ->

Creating MQ channels . Contd .. Define Channel objects at the target Start an interactive session for The SRC_QM using runmqsc TGT_QM From the session create the Required Channels. • Define a sender channel from the TGT_QM queue manager to the SRC_QM queue manager. Use the T/IP transmission protocol DEFINE CHL ('TGT_QM.TO.SRC_QM') CHLTYPE(SDR) TRPTYPE(T) CONNAME('IP_address(port)') XMITQ('SRC_QM') DISCINT(0) • Define a receiver channel at the TGT_QM queue manager from the SRC_QM queue manager. DEFINE CHL ('SRC_QM.TO.TGT_QM') CHLTYPE(RCVR) TRPTYPE(T)

* In this Presentation source Server port is identified as 1450 and destination Server port is identified as 1451. The Ports numbers are subject to availability. Default MQ port is 1414

Starting MQ channels and listeners SOURCE SERVER

TARGET SERVER

• Start the source queue manager: strmqm SRC_QM • Start the listener at the source for the receiver end of the channel from the target, by using T/IP protocol for queue manager SRC_QM, port 1451 nohup runmqlsr -t t -m SRC_QM -p 1451 & • Start an interactive session with the SRC_QM queue manager runmqsc SRC_QM • Start the sender channel at the source. start channel (SRC_QM.TO.TGT_QM)

• Start the source queue manager: strmqm TGT_QM • Start the listener at the target for the receiver end of the channel from the source, by using T/IP protocol for queue manager TGT_QM, port 1450 nohup runmqlsr -t t -m TGT_QM -p 1450 & • Start an interactive session with the TGT_QM queue manager runmqsc TGT_QM • Start the sender channel at the source. start channel (TGT_QM.TO.SRC_QM)

You set up and started the MQ objects for unidirectional replication using two queue managers to simulate replication between two remote database. Next, you will set up the Q Replication programs.

Setting up Q Replication In this module, you configure your databases and create the control tables, replication queue maps, and Q subscriptions. • The Q Capture program at the source database and Q Apply program at the target database use control tables to store important information such as which tables to capture data from, which queues to use, and which tables to apply replicated data to. Creating control tables is also known as creating a schema. • Replication queue maps identify the IBM® MQ queues that are used for sending and receiving data. • Q subscriptions identify options such as rows and columns that are replicated or published. The Q subscriptions also identify options for loading target tables. Steps Discussed: • Creating the source and target control tables • Enabling the source database for replication • Creating a replication queue map • Creating a Q subscription

Creating the source and target control tables The Q Capture program at the source database and Q Apply program at the target database use control tables to store important information such as which tables to capture data from, which queues to use, and which tables to apply replicated data to. There are two methods to create the control tables ASNCLP command-line program. For the complete list of the command options please go the link Here

Replication Center

Use the CREATE CONTROL TABLES FOR command to create a new set of Capture or Apply control tables. For example, the following commands set the environment and create Capture control tables: SET SERVER CAPTURE TO DB SAMPLE SET OUTPUT CAPTURE SCRIPT "capctrl.sql"; SET LOG "capctrl.err"; SET RUN SCRIPT LATER; CREATE CONTROL TABLES FOR CAPTURE SERVER IN UW UOW TSUOW100 OTHERS TSASN100; Use either the Create Control Tables or Create Control Tables Quick windows for Capture and Apply