DB2 Replication
- Q Replication
- Setup - unidirectional Q Replication
SQL Replication

DB2 Replication

Q Replication

WebSphere MQ Objects

Source Server :

1) Queue manager
2) Local administration queue
3) Local restart queue
4) Remote queue definition for Q Capture data messages
5) Local transmission queue
6) Sender channel from Source QMGR to Target QMGR using the local transmission queue
7) Receiver channger from target Q MGR

#!/bin/sh

echo "ALTER QMGR MAXMSGL(4914304)" > qrepl.qrep1.mq_temp.mqsc
echo "DEFINE QLOCAL('ASN.RESTARTQ') put(enabled) get(enabled)" >> qrepl.qrep1.mq_temp.mqsc
echo "DEFINE QLOCAL('ASN.ADMINQ') PUT(ENABLED) GET(ENABLED) SHARE" >> qrepl.qrep1.mq_temp.mqsc
echo "DEFINE QREMOTE('ASN.SRC_TO_TGT.DATA') RNAME ('ASN.SRC_TO_TGT.DATA') RQMNAME(QM_QREP02) XMITQ(QM_QREP02) PUT(ENABLED)" >> qrepl.qrep1.mq_temp.mqsc
echo "DEFINE QLOCAL('QM_QREP02') USAGE(XMITQ) MAXDEPTH(500000) MAXMSGL(4914304)" >> qrepl.qrep1.mq_temp.mqsc
echo "DEFINE CHL('CH.QREP01_TO_QREP02') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME('1.1.1.81(1414)') XMITQ('QM_QREP02') DISCINT(0) CONVERT(NO) MAXMSGL(4914304)" >> qrepl.qrep1.mq_temp.mqsc
echo "DEFINE CHL('CH.QREP02_TO_QREP01') CHLTYPE(RCVR) TRPTYPE(TCP)" >> qrepl.qrep1.mq_temp.mqsc
echo "START CHL('CH.QREP01_TO_QREP02')" >> qrepl.qrep1.mq_temp.mqsc
echo "DEFINE LISTENER('SRC_LSNR') TRPTYPE(TCP) PORT(1414) CONTROL(QMGR)" >> qrepl.qrep1.mq_temp.mqsc
echo "START LISTENER('SRC_LSNR')" >> qrepl.qrep1.mq_temp.mqsc

crtmqm -lp 30 -ls 10 -lf 16384 -x 100000 QM_QREP01
strmqm QM_QREP01
runmqsc QM_QREP01 < qrepl.qrep1.mq_temp.mqsc

Required objects in MQ - Target Server :

1) Queue manager
2) Remote administration queue pointing to source server
3) Spill queue for the data messages
4) Local queue for Q Apply to get data messages from Q Capture
5) Local transmission queue
6) Sender channel from Target to source QMGR using source transmission queue
7) Receiver channel from Source QMGR

#!/bin/sh

echo "ALTER QMGR MAXMSGL(4914304)" > qrepl.qrep2.mq_temp.mqsc
echo "DEFINE QREMOTE('ASN.ADMINQ') RNAME('ASN.ADMINQ') RQMNAME('QM_QREP01') XMITQ('QM_QREP02') PUT(ENABLED)" >> qrepl.qrep2.mq_temp.mqsc
echo "DEFINE QLOCAL('ASN.SRC_TO_TGT.DATA') GET(ENABLED) PUT(ENABLED) DEFSOPT(SHARED) MAXDEPTH(500000)" >> qrepl.qrep2.mq_temp.mqsc
echo "DEFINE QLOCAL('QM_QREP02') USAGE(XMITQ) MAXDEPTH(500000) MAXMSGL(4914304)" >> qrepl.qrep2.mq_temp.mqsc
echo "DEFINE CHL('CH.QREP02_TO_QREP01') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME('1.1.1.80(1414)') XMITQ('QM_QREP02') DISCINT(0) CONVERT(NO)  MAXMSGL(4914304)" >> qrepl.qrep2.mq_temp.mqsc
echo "DEFINE CHL('CH.QREP01_TO_QREP02') CHLTYPE(RCVR) TRPTYPE(TCP)  MAXMSGL(4914304)" >> qrepl.qrep2.mq_temp.mqsc
echo "DEFINE QMODEL('IBMQREP.SPILL.MODELQ') DEFSOPT(SHARED) MAXDEPTH(500000) MSGDLVSQ(FIFO) DEFTYPE(PERMDYN) MAXMSGL(4914304)" >> qrepl.qrep2.mq_temp.mqsc
echo "START CHL('CH.QREP02_TO_QREP01')" >> qrepl.qrep2.mq_temp.mqsc
echo "DEFINE LISTENER('TGT_LSNR') TRPTYPE(TCP) PORT(1414)  CONTROL(QMGR)" >> qrepl.qrep2.mq_temp.mqsc
echo "START LISTENER('TGT_LSNR')" >> qrepl.qrep2.mq_temp.mqsc

crtmqm -lp 30 -ls 10 -lf 16384 -x 100000 QM_QREP02
strmqm QM_QREP02
runmqsc QM_QREP02 < qrepl.qrep2.mq_temp.mqsc

Start-up MQ Environment :

1) Start source QMGR
2) Start target QMGR
3) Start source channel
4) Start target channel

Test MQ Queues :

# Send message
/opt/mqm/samples/bin/amqsput <TARGETQUEUE> <QMGR>

# Receive a message
/opt/mqm/samples/bin/amqsget <TARGETQUEUE> <QMGR>

# ! Incorrect setup throws ASN2288W error code

SOURCE - Q Capture

Create control tables with Replication center :

Start Q Capture :

asnqcap capture_server=<SOURCE DB>

Stop Q Capture :

asnqcmd capture_server=<SOURCE DB> stop

TARGET - Q Apply

Create control tables :

Start Q Apply :

asnqapp apply_server=<TARGET DB>

Stop Q Apply :

asnqacmd apply_server=<TARGET DB> stop

Start processing of replication queue :

asnqacmd apply_server=<APPLY_SERVER> startq=<REPLICATION_QUEUE>

Check status of replication queues :

asnqacmd apply_server=<APPLY_SERVER> status show details

db2 "select substr(REPQMAPNAME,1,20),recvq,substr(CAPTURE_SCHEMA,1,8), 
sendq,state,state_time from asn.IBMQREP_RECVQUEUES"

Query Apply trace :

db2 "select * from asn.ibmqrep_applytrace where OPERATION <> 'INFO' and TRACE_TIME > CURRENT TIMESTAMP - 7 minutes"

Setup - unidirectional Q Replication

1. Create MQ Objects :

ASNCLP SESSION SET TO Q REPLICATION;

CREATE MQ SCRIPT RUN NOW
CONFIG TYPE U
MQSERVER 1 NAME SAMPLE MQHOST "9.30.54.118",
MQSERVER 2 NAME TARGETDB MQHOST "9.30.54.119",

QUIT;

2. Setup Q Replication :

ASNCLP SESSION SET TO Q REPLICATION;
SET SERVER CAPTURE TO DBALIAS SAMPLE ID db2admin PASSWORD "passw0rd";
SET SERVER TARGET TO DBALIAS TARGETDB ID db2admin PASSWORD "passw0rd";
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;

CREATE CONTROL TABLES FOR CAPTURE SERVER;
CREATE CONTROL TABLES FOR APPLY SERVER USING PWDFILE "asnpwd.aut";

CREATE REPLQMAP SAMPLE_ASN_TO_TARGETDB_ASN;

CREATE QSUB USING REPLQMAP SAMPLE_ASN_TO_TARGETDB_ASN
(SUBNAME EMPLOYEE0001 db2admin.EMPLOYEE OPTIONS HAS LOAD PHASE I
KEYS (EMPNO) LOAD TYPE 1);

QUIT;

SQL Replication

Full refresh of target tables

Construct query based on column subscription :

db2 "select varchar(target_table,25) table,listagg(target_name,',') within group(order by colno) from asn.IBMSNAP_SUBS_COLS group by target_table order by target_table with ur"

Example load :

db2 "declare BLKSTDESC cursor database DBBSFT_P user uft_prod using entr0p1a for select BLKSTDESC_CRDID,BLKSTDESC_DESC,x'00000000000000000000',x'00000000000000000000','U',CURRENT_TIMESTAMP from BUONISCHEMA.BLKSTDESC"
db2 load from BLKSTDESC of cursor messages /home/mw_prod/BUONISICURI/fullrefresh.log replace into BUONISICURI_APPLY.BLKSTDESC_APPLY

SQL Apply

List Apply subscription sets with Apply qualifiers :

db2 "select set_name,apply_qual, source_server,target_server from asn.ibmsnap_subs_set order by set_name, apply_qual with ur"

List tables in Apply qualifier :

db2 "select apply_qual,varchar(source_owner,20) SOURCE_SCHEMA,varchar(source_table,30) SOURCE_TABLE, varchar(target_owner,25) target_schema, varchar(target_table,30) target_table from asn.IBMSNAP_SUBS_MEMBR order by apply_qual,source_table with ur"

Check last run, last success and synchtime for Apply Qualifiers :

db2 "select APPLY_QUAL,LASTRUN,LASTSUCCESS,SYNCHTIME from asn.IBMSNAP_SUBS_SET"

ADD Columns into replication

Stop SQL Capture :

asnccmd capture_server=<DB> capture_schema=<SCHEMA> stop

Modify Source table (DDL/alter table add column ..)

Modify CD table :

asnclp
set server capture to db <DB>
set capture schema source DWH
alter registration add <SOURCE_SCHEMA>.<SOURCE_TABLE> cols (<COLUMN> image after, <COLUMN> image after)

--run the script

Modify Apply table :

asnclp
set server capture to db <SOURCE_DB> id <ID>
set server control to db <APPLY_DB>
set server target to db <APPLY_DB>
alter member add cols in setname <SETNAME> applyqual <APPLY QUALIFIER> source <SOURCE_SCHEMA>.<SOURCE_TABLE> target <APPLY_SCHEMA>.<APPLY_TABLE> COLS (EXPRESSION <SOURCE_COLUMN> TARGET <TARGET_COLUMN>, EXPRESSION <SOURCE_COLUMN> TARGET <TARGET_COLUMN>)

CDC

Access Server

Start Access Server from the installation path :

./dmaccessserver &

Create new SYSADMIN user without expiring :

./dmcreateuser cdcadmin "CDC Administrator" "CDC Administrative user" E3ec37df0e SYSADMIN TRUE FALSE TRUE

Replication Server

Configuration tool

<CDC/Install/Path>/bin/dmconfigurets

Table of Contents