CNDBA社区
测试环境
IP db2 version dbname dbalias mqversion qname
源端: 192.168.56.110 11.1.4.6 sourcedb DB110 9.0.5.0 DB110
目标端 192.168.56.111 11.1.4.6 targetdb DB111 9.0.5.0 DB111
dbalias必须和qname相同
1.源端创建测试表t1
[db2inst1@node01 ~]$ db2 connect to sourcedb
Database Connection Information
Database server = DB2/LINUXX8664 11.1.4.6
SQL authorization ID = DB2INST1
Local database alias = SOURCEDB
[db2inst1@node01 ~]$ db2 "create table t1 (id int,name varchar(10))"
[db2inst1@node01 ~]$ db2 "insert into t1 values (1,'a')"
[db2inst1@node01 ~]$ db2 "insert into t1 values (2,'b')"
[db2inst1@node01 ~]$ db2 "insert into t1 values (3,'c')"
[db2inst1@node01 ~]$ db2 "insert into t1 values (4,'d')"
[db2inst1@node01 ~]$ db2 "insert into t1 values (5,'e')"
目标端创建库，但是不插入数据
[db2inst1@node02 ~]$ db2 connect to targetdb
Database Connection Information
Database server = DB2/LINUXX8664 11.1.4.6
SQL authorization ID = DB2INST1
Local database alias = TARGETDB
[db2inst1@node02 ~]$ db2 "create table t1 (id int,name varchar(10))"
DB20000I The SQL command completed successfully.
2.在源端和目标端将db2inst1加入mqm组中
[root@node01 ~]# usermod -G mqm db2inst1
[root@node01 ~]# id db2inst1
uid=1000(db2inst1) gid=1000(db2iadm1) groups=1000(db2iadm1),1003(mqm)
[root@node02 ~]# usermod -G mqm db2inst1
[root@node02 ~]# id db2inst1
uid=1000(db2inst1) gid=1000(db2iadm1) groups=1000(db2iadm1),1003(mqm)
3.源端和目标端编目数据库
db2 catalog tcpip node node110 remote 192.168.56.110 server 50000
db2 catalog tcpip node node111 remote 192.168.56.111 server 50000
db2 catalog db sourcedb as DB110 at node node110
db2 catalog db targetdb as DB111 at node node111
db2 terminate
db2 connect to DB110 user db2inst1 using wwwwww
db2 terminate
db2 connect to DB111 user db2inst1 using wwwwww
db2 terminate
4.创建mq队列管理器
[db2inst1@node01 ~]$ cat 1_create_mq_object
##################################################
ASNCLP SESSION SET TO Q REPLICATION;
#CREATE MQ SCRIPT RUN NOW
CREATE MQ SCRIPT
CONFIG TYPE U
MQSERVER 1 NAME DB110 MQHOST "192.168.56.110",
MQSERVER 2 NAME DB111 MQHOST "192.168.56.111";
QUIT;
##################################################
[db2inst1@node01 ~]$ asnclp -f 1_create_mq_object
在源端执行命令
[db2inst1@node01 ~]$ sh qrepl.db110.mq_aixlinux.sh
在目标端执行命令
[db2inst1@node02 ~]$ sh qrepl.db111.mq_aixlinux.sh
5.源端和目标端生成密码文件
[db2inst1@node01 ~]$ asnpwd init
2021-10-05-11.05.22.208520 ASN1981I "Asnpwd" : "" : "Initial". The program completed successfully using password file "asnpwd.aut".
[db2inst1@node01 ~]$ asnpwd add alias DB110 id db2inst1 password wwwwww
2021-10-05-11.06.05.809424 ASN1981I "Asnpwd" : "" : "Initial". The program completed successfully using password file "asnpwd.aut".
[db2inst1@node01 ~]$ asnpwd add alias DB111 id db2inst1 password wwwwww
2021-10-05-11.06.18.344766 ASN1981I "Asnpwd" : "" : "Initial". The program completed successfully using password file "asnpwd.aut".
6.创建Q复制的表
[db2inst1@node01 ~]$ vi 2_create_control_table
ASNCLP SESSION SET TO Q REPLICATION;
SET SERVER CAPTURE TO DBALIAS DB110 ID db2inst1 PASSWORD "wwwwww";
SET SERVER TARGET TO DBALIAS DB111 ID db2inst1 PASSWORD "wwwwww";
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";
QUIT;
[db2inst1@node01 ~]$ asnclp -f 2_create_control_table
====
CMD: ASNCLP SESSION SET TO Q REPLICATION;
====
====
CMD: SET SERVER CAPTURE TO DBALIAS DB110 ID db2inst1 PASSWORD "wwwwww";
====
====
CMD: SET SERVER TARGET TO DBALIAS DB111 ID db2inst1 PASSWORD "wwwwww";
====
====
CMD: SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
====
====
CMD: CREATE CONTROL TABLES FOR CAPTURE SERVER;
====
ASN2482I The MQDEFAULTS option to pick default values for WebSphere MQ objects was chosen in the CREATE CONTROL TABLES command. The ASNCLP program will assign the following defaults: queue manager: "DB110"; administration queue: "ASN.ADMINQ"; restart queue: "ASN.RESTARTQ".
ASN1956I ASNCLP : Generating SQL script files for action: "CREATE CONTROL TABLES".
ASN1955I ASNCLP : Using the following files: "qreplcap.sql" for the Capture SQL script, "replctl.sql" for the control SQL script, "qreplapp.sql" for the target SQL script, and "qreplmsg.log" for the log file.
--- ASNCLP Version 11.01.00 Build date 2021-02-19 21:15:14
<ClpInfo2Log:: Preparing to run script.>
<ClpInfo2Log:: Now running SQL...>
ASN1514I The replication action ended at "Tuesday, October 5, 2021 11:08:59 AM CST" with "1" successes, "0" errors, and "0" warnings.
<ClpInfo2Log:: The SQL command completed successfully.>
====
CMD: CREATE CONTROL TABLES FOR APPLY SERVER USING PWDFILE "asnpwd.aut";
====
ASN1956I ASNCLP : Generating SQL script files for action: "CREATE CONTROL TABLES".
ASN1955I ASNCLP : Using the following files: "qreplcap.sql" for the Capture SQL script, "replctl.sql" for the control SQL script, "qreplapp.sql" for the target SQL script, and "qreplmsg.log" for the log file.
--- ASNCLP Version 11.01.00 Build date 2021-02-19 21:15:14
<ClpInfo2Log:: Preparing to run script.>
<ClpInfo2Log:: Now running SQL...>
ASN1514I The replication action ended at "Tuesday, October 5, 2021 11:09:06 AM CST" with "1" successes, "0" errors, and "0" warnings.
<ClpInfo2Log:: The SQL command completed successfully.>
====
CMD: QUIT;
====
ASN1953I ASNCLP : Command completed.
7.在源端和目标端检查控制表
[db2inst1@node01 ~]$ db2 connect to DB110 USER DB2INST1 USING wwwwww
Database Connection Information
Database server = DB2/LINUXX8664 11.1.4.6
SQL authorization ID = DB2INST1
Local database alias = DB110
[db2inst1@node01 ~]$ db2 list tables for all | grep -i ibmq
IBMQREP_ADMINMSG ASN T 2021-10-05-11.09.02.446084
IBMQREP_CAPENQ ASN T 2021-10-05-11.09.02.325946
IBMQREP_CAPENVINFO ASN T 2021-10-05-11.09.03.334137
IBMQREP_CAPMON ASN T 2021-10-05-11.09.01.707725
IBMQREP_CAPPARMS ASN T 2021-10-05-11.08.59.412859
IBMQREP_CAPQMON ASN T 2021-10-05-11.09.02.007098
IBMQREP_CAPTRACE ASN T 2021-10-05-11.09.01.392812
IBMQREP_COLVERSION ASN T 2021-10-05-11.09.04.964415
IBMQREP_EXCLSCHEMA ASN T 2021-10-05-11.09.04.494471
IBMQREP_IGNTRAN ASN T 2021-10-05-11.09.02.755726
IBMQREP_IGNTRANTRC ASN T 2021-10-05-11.09.03.054067
IBMQREP_PART_HIST ASN T 2021-10-05-11.09.03.467260
IBMQREP_SCHEMASUBS ASN T 2021-10-05-11.09.04.131894
IBMQREP_SENDQUEUES ASN T 2021-10-05-11.08.59.933157
IBMQREP_SIGNAL ASN T 2021-10-05-11.09.01.090264
IBMQREP_SRCH_COND ASN T 2021-10-05-11.09.00.931456
IBMQREP_SRC_COLS ASN T 2021-10-05-11.09.00.644110
IBMQREP_SUBS ASN T 2021-10-05-11.09.00.307720
IBMQREP_SUBS_PROF ASN T 2021-10-05-11.09.03.849175
IBMQREP_TABVERSION ASN T 2021-10-05-11.09.04.639726
[db2inst1@node01 ~]$ db2 list tables for all | grep -i ibmq | wc -l
20
[db2inst1@node01 ~]$ db2 connect to DB111 USER DB2INST1 USING wwwwww
Database Connection Information
Database server = DB2/LINUXX8664 11.1.4.6
SQL authorization ID = DB2INST1
Local database alias = DB111
[db2inst1@node01 ~]$ db2 list tables for all | grep -i ibmq
IBMQREP_APPENVINFO ASN T 2021-10-05-11.09.10.559247
IBMQREP_APPEVENTS ASN T 2021-10-05-11.09.11.643341
IBMQREP_APPEVTDEFS ASN T 2021-10-05-11.09.11.373796
IBMQREP_APPLYCMD ASN T 2021-10-05-11.09.12.862015
IBMQREP_APPLYCMDOUT ASN T 2021-10-05-11.09.14.257730
IBMQREP_APPLYENQ ASN T 2021-10-05-11.09.10.440788
IBMQREP_APPLYMON ASN T 2021-10-05-11.09.09.489824
IBMQREP_APPLYPARMS ASN T 2021-10-05-11.09.06.747733
IBMQREP_APPLYTRACE ASN T 2021-10-05-11.09.09.185264
IBMQREP_DONEMSG ASN T 2021-10-05-11.09.13.783978
IBMQREP_EXCEPTIONS ASN T 2021-10-05-11.09.08.652661
IBMQREP_MCGMON ASN T 2021-10-05-11.09.12.555526
IBMQREP_MCGPARMS ASN T 2021-10-05-11.09.12.279571
IBMQREP_MCGSYNC ASN T 2021-10-05-11.09.11.923687
IBMQREP_RECVQUEUES ASN T 2021-10-05-11.09.07.080181
IBMQREP_ROLLBACK_R ASN T 2021-10-05-11.09.10.992891
IBMQREP_ROLLBACK_T ASN T 2021-10-05-11.09.10.705565
IBMQREP_SAVERI ASN T 2021-10-05-11.09.10.237182
IBMQREP_SPILLEDROW ASN T 2021-10-05-11.09.09.869775
IBMQREP_SPILLQS ASN T 2021-10-05-11.09.08.301291
IBMQREP_TAB_PROF ASN T 2021-10-05-11.09.13.284475
IBMQREP_TARGETS ASN T 2021-10-05-11.09.07.488930
IBMQREP_TRG_COLS ASN T 2021-10-05-11.09.07.961232
[db2inst1@node01 ~]$ db2 list tables for all | grep -i ibmq | wc -l
23
8.创建ReplQrep(复制队列映射)和QSUB(复制预订)
[db2inst1@node01 ~]$ cat 3_create_qrepmap_qsub
##################################################
ASNCLP SESSION SET TO Q REPLICATION;
SET SERVER CAPTURE TO DBALIAS DB110 ID db2inst1 PASSWORD "wwwwww";
SET SERVER TARGET TO DBALIAS DB111 ID db2inst1 PASSWORD "wwwwww";
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
CREATE REPLQMAP SAMPLE_ASN_TO_TARGETDB_ASN;
CREATE QSUB USING REPLQMAP SAMPLE_ASN_TO_TARGETDB_ASN (SUBNAME SUB0001 DB2INST1.T1 OPTIONS HAS LOAD PHASE I exist TARGET NAME DB2INST1.T1 LOAD TYPE 2);
QUIT;
[db2inst1@node01 ~]$ asnclp -f 3_create_qrepmap_qsub
[db2inst1@node01 scripts]$ asnclp
Repl > ASNCLP SESSION SET TO Q REPLICATION
Repl > SET SERVER CAPTURE TO DBALIAS DB110 ID db2inst1 PASSWORD "wwwwww"
Repl > SET SERVER TARGET TO DBALIAS DB111 ID db2inst1 PASSWORD "wwwwww"
Repl > list qsub for qcapture schema asn
NAME Source Table Target Table Target server (schema) Type State State Time State Info SendQ All Changed Rows Before Values Changed Cols only Has Load Phase
------- ------------- ------------- ---------------------- ----- ----- -------------------------- ---------- --------------------------- ---------------- ------------- ----------------- --------------
SUB0001 DB2INST1.T1 DB2INST1.T1 TARGETDB (ASN) Unidi N 2021-10-05 19:07:42.416704 - ASN.DB110_TO_ASN.DB111.DATA N Y Y I
Number of records 1
Repl > START QSUB SUBNAME SUB0001
====
CMD: START QSUB SUBNAME SUB0001;
====
ASN1956I ASNCLP : Generating SQL script files for action: "START Q SUBSCRIPTION".
ASN1955I ASNCLP : Using the following files: "qreplcap.sql" for the Capture SQL script, "replctl.sql" for the control SQL script, "qreplapp.sql" for the target SQL script, and "qreplmsg.log" for the log file.
--- ASNCLP Version 11.01.00 Build date 2021-02-19 21:15:14
ASN1514I The replication action ended at "Tuesday, October 5, 2021 9:18:53 PM CST" with "1" successes, "0" errors, and "0" warnings.
9.启动源端capture
[db2inst1@node01 scripts]$ nohup asnqcap capture_server="DB110" capture_schema="ASN" capture_path="/home/db2inst1/scripts" startmode=cold &
[db2inst1@node01 scripts]$ asnqccmd capture_server="DB110" capture_schema="ASN" status show details
2021-10-05-21.21.04.968409 ASN0600I "AsnQCcmd" : "" : "Initial" : Program "mqpubcmd 11.4.0 (Build 11.1.4.6 Level s2102191900, PTF DYN2102191900AMD64)" is starting.
Q Capture program status
Server name (SERVER) = DB110
Schema name (SCHEMA) = ASN
Program status (STATUS) = Up
Time since program started (UP_TIME) = 0d 0h 0m 43s
Log file location (LOGFILE) = /home/db2inst1/scripts/db2inst1.DB110.ASN.QCAP.log
Number of active Q subscriptions (ACTIVE_QSUBS) = 0
Log reader currency (CURRENT_LOG_TIME) = 1970-01-01-08.00.00.000000
Last committed transaction published (LSN) (ALL_PUBLISHED_AS_OF_LSN) = 0000:0000:0000:1671:0000:0000:0003:CFE1
Current application memory (CURRENT_MEMORY ) = 0 bytes
Path to database log files (DB2LOG_PATH) = /db2log/NODE0000/LOGSTREAM0000/
Oldest database log file needed for Q Capture restart (OLDEST_DB2LOG) = Run this command: "/home/db2inst1/sqllib/bin/db2flsn -q -db DB110 000000000003cfe1" as a user with read permission to the log files.
Current database log file captured (CURRENT_DB2LOG) = Run this command: "/home/db2inst1/sqllib/bin/db2flsn -q -db DB110 000000000003cfe9" as a user with read permission to the log
files.
10.目标端启动apply
[db2inst1@node02 scripts]$ nohup asnqapp apply_server="DB111" apply_schema="ASN" apply_path="/home/db2inst1/scripts" &
[db2inst1@node02 scripts]$ asnqacmd apply_server="DB111" apply_schema="ASN" status show details
2021-10-05-21.22.50.013568 ASN0600I "AsnQAcmd" : "" : "Initial" : Program "asnqacmd 11.4.0 (Build 11.1.4.6 Level s2102191900, PTF DYN2102191900AMD64)" is starting.
Q Apply program status
Server name (SERVER) = DB111
Schema name (SCHEMA) = ASN
Program status (STATUS) = Up
Time since program started (UP_TIME) = 0d 0h 0m 24s
Log file location (LOGFILE) = /home/db2inst1/scripts/db2inst1.DB111.ASN.QAPP.log
Number of active Q subscriptions (ACTIVE_QSUBS) = 1
Time period used to calculate average (INTERVAL_LENGTH) = 0h 0m 20.637s
Receive queue : ASN.DB110_TO_ASN.DB111.DATA
Number of active Q subscriptions (ACTIVE_QSUBS) = 1
All transactions applied as of (time) (OLDEST_TRANS) = 2021-10-05-21.22.27.000000
Restart point for Q Capture (MAXCMTSEQ) (MAXCMTSEQ) = 615C:5193:0000:0000:0000:0000:0000:0000
All transactions applied as of (LSN) (ALL_APPLIED_AS_OF_LSN) = 0000:0000:0000:0000:0000:0000:0000:0000
Oldest in-progress transaction (OLDEST_INFLT_TRANS) = 1900-01-01-00.00.00.000000
Average end-to-end latency (END2END LATENCY) = 0h 0m 0.0s
Average Q Capture latency (CAPTURE_LATENCY) = 0h 0m 0.0s
Average WSMQ latency (QLATENCY) = 0h 0m 0.0s
Average Q Apply latency (APPLY_LATENCY) = 0h 0m 0.0s
Current memory (CURRENT_MEMORY ) = 0 Bytes
Current queue depth (QDEPTH) = 0
Current queue percentage full (Q_PERCENT_FULL) = 0%
Agents processing transaction (PROCESSING_TRANSACTION) = NONE
Agents waiting for transaction (WAITING_FOR_TRANSACTION) = BR00000AG001, BR00000AG002, BR00000AG003, BR00000AG004, BR00000AG005, BR00000AG006, BR00000AG007, BR00000AG008, BR00000AG009, BR00000AG010, BR00000AG011, BR00000AG012, BR00000AG013, BR00000AG014, BR00000AG015, BR00000AG016
Agents processing internal messages (PROCESSING_INTERNAL_MESSAGES) = NONE
Agents in initializing state (INITIALIZING) = NONE
11.目标端查看是否把t1表同步过来
[db2inst1@node02 scripts]$ db2 "select * from t1"
ID NAME
----------- ----------
1 a
2 b
3 c
4 d
5 e
5 record(s) selected.
版权声明：本文为博主原创文章，未经博主允许不得转载。
QREP
