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