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
or
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