db2_qreplication [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== DB2 Replication ======

===== Q Replication =====
==== WebSphere MQ Objects ====
**Source Server :**
<sxh bash>
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
</sxh>

<sxh bash>
#!/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
</sxh>

**Required objects in MQ - Target Server :**
<sxh bash>
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
</sxh>

<sxh bash>
#!/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
</sxh>

**Start-up MQ Environment :**
<sxh bash>
1) Start source QMGR
2) Start target QMGR
3) Start source channel
4) Start target channel
</sxh>

**Test MQ Queues :**
<sxh bash>
# 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
</sxh>

==== SOURCE - Q Capture ====
**Create control tables with Replication center :**
<sxh bash>

</sxh>

**Start Q Capture :**
<sxh bash>
asnqcap capture_server=<SOURCE DB>
</sxh>

**Stop Q Capture :**
<sxh bash>
asnqcmd capture_server=<SOURCE DB> stop
</sxh>

==== TARGET - Q Apply ====
**Create control tables :**
<sxh bash>

</sxh>

**Start Q Apply :**
<sxh bash>
asnqapp apply_server=<TARGET DB>
</sxh>

**Stop Q Apply :**
<sxh bash>
asnqacmd apply_server=<TARGET DB> stop
</sxh>


**Start processing of replication queue :**
<sxh bash>
asnqacmd apply_server=<APPLY_SERVER> startq=<REPLICATION_QUEUE>
</sxh>

**Check status of replication queues :**
<sxh bash>
asnqacmd apply_server=<APPLY_SERVER> status show details
</sxh>

or 

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

**Query Apply trace :**
<sxh bash>
db2 "select * from asn.ibmqrep_applytrace where OPERATION <> 'INFO' and TRACE_TIME > CURRENT TIMESTAMP - 7 minutes"
</sxh>

===== Setup - unidirectional Q Replication =====
**1. Create MQ Objects :**
<sxh bash>
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;
</sxh>

**2. Setup Q Replication :**
<sxh bash>
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;
</sxh>




====== SQL Replication ======




===== Full refresh of target tables =====
**Construct query based on column subscription : **
<sxh bash>
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"
</sxh>

Example load : 
<sxh bash>
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
</sxh>

===== SQL Apply  =====
**List Apply subscription sets with Apply qualifiers :**
<sxh bash>
db2 "select set_name,apply_qual, source_server,target_server from asn.ibmsnap_subs_set order by set_name, apply_qual with ur"
</sxh>

**List tables in Apply qualifier :**
<sxh bash>
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"
</sxh>


**Check last run, last success and synchtime for Apply Qualifiers :**
<sxh bash>
db2 "select APPLY_QUAL,LASTRUN,LASTSUCCESS,SYNCHTIME from asn.IBMSNAP_SUBS_SET"
</sxh>

==== ADD Columns into replication ====
**Stop SQL Capture :**
<sxh bash>
asnccmd capture_server=<DB> capture_schema=<SCHEMA> stop
</sxh>

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

**Modify CD table :**
<sxh bash>
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
</sxh>

**Modify Apply table :**
<sxh bash>
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>)
</sxh>

===== CDC =====

===== Access Server =====
**Start Access Server from the installation path :**
<sxh bash>
./dmaccessserver &
</sxh>

**Create new SYSADMIN user without expiring :**
<sxh bash>
./dmcreateuser cdcadmin "CDC Administrator" "CDC Administrative user" E3ec37df0e SYSADMIN TRUE FALSE TRUE
</sxh>

==== Replication Server ====
**Configuration tool **
<sxh bash>
<CDC/Install/Path>/bin/dmconfigurets
</sxh>