====== 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 # Receive a message /opt/mqm/samples/bin/amqsget # ! Incorrect setup throws ASN2288W error code ==== SOURCE - Q Capture ==== **Create control tables with Replication center :** **Start Q Capture :** asnqcap capture_server= **Stop Q Capture :** asnqcmd capture_server= stop ==== TARGET - Q Apply ==== **Create control tables :** **Start Q Apply :** asnqapp apply_server= **Stop Q Apply :** asnqacmd apply_server= stop **Start processing of replication queue :** asnqacmd apply_server= startq= **Check status of replication queues :** asnqacmd 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= capture_schema= stop **Modify Source table (DDL/alter table add column ..)** **Modify CD table :** asnclp set server capture to db set capture schema source DWH alter registration add . cols ( image after, image after) --run the script **Modify Apply table :** asnclp set server capture to db id set server control to db set server target to db alter member add cols in setname applyqual source . target . COLS (EXPRESSION TARGET , EXPRESSION TARGET ) ===== 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 ** /bin/dmconfigurets