IBM Q复制实施

IBM Q复制实施

测试环境 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 db2inst1uid=1000(db2inst1) gid=1000(db2iadm1) groups=1000(db2iadm1),1003(mqm)[root@node02 ~]# usermod -G mqm db2inst1[root@node02 ~]# id db2inst1uid=1000(db2inst1) gid=1000(db2iadm1) groups=1000(db2iadm1),1003(mqm)

3.源端和目标端编目数据库

db2 catalog tcpip node node110 remote 192.168.56.110 server 50000db2 catalog tcpip node node111 remote 192.168.56.111 server 50000db2 catalog db sourcedb as DB110 at node node110db2 catalog db targetdb as DB111 at node node111db2 terminatedb2 connect to DB110 user db2inst1 using terminatedb2 connect to DB111 user db2inst1 using terminate

4.创建mq队列管理器

[db2inst1@node01 ~]$ cat 1_create_mq_object ##################################################ASNCLP SESSION SET TO Q REPLICATION;#CREATE MQ SCRIPT RUN NOWCREATE MQ SCRIPTCONFIG TYPE UMQSERVER 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 init2021-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 ASN1981I "Asnpwd" : "" : "Initial". The program completed successfully using password file "asnpwd.aut".[db2inst1@node01 ~]$ asnpwd add alias DB111 id db2inst1 password ASN1981I "Asnpwd" : "" : "Initial". The program completed successfully using password file "asnpwd.aut".

6.创建Q复制的表

[db2inst1@node01 ~]$ vi 2_create_control_tableASNCLP SESSION SET TO Q REPLICATION;SET SERVER CAPTURE TO DBALIAS DB110 ID db2inst1 PASSWORD "SERVER TARGET TO DBALIAS DB111 ID db2inst1 PASSWORD "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 "SET SERVER TARGET TO DBALIAS DB111 ID db2inst1 PASSWORD "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:14ASN1514I The replication action ended at "Tuesday, October 5, 2021 11:08:59 AM CST" with "1" successes, "0" errors, and "0" warnings.====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:14ASN1514I The replication action ended at "Tuesday, October 5, 2021 11:09:06 AM CST" with "1" successes, "0" errors, and "0" warnings.====CMD: QUIT;====ASN1953I ASNCLP : Command completed.

7.在源端和目标端检查控制表

[db2inst1@node01 ~]$ db2 connect to DB110 USER DB2INST1 USING 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 ibmqIBMQREP_ADMINMSG ASN T 2021-10-05-11.09.02.446084IBMQREP_CAPENQ ASN T 2021-10-05-11.09.02.325946IBMQREP_CAPENVINFO ASN T 2021-10-05-11.09.03.334137IBMQREP_CAPMON ASN T 2021-10-05-11.09.01.707725IBMQREP_CAPPARMS ASN T 2021-10-05-11.08.59.412859IBMQREP_CAPQMON ASN T 2021-10-05-11.09.02.007098IBMQREP_CAPTRACE ASN T 2021-10-05-11.09.01.392812IBMQREP_COLVERSION ASN T 2021-10-05-11.09.04.964415IBMQREP_EXCLSCHEMA ASN T 2021-10-05-11.09.04.494471IBMQREP_IGNTRAN ASN T 2021-10-05-11.09.02.755726IBMQREP_IGNTRANTRC ASN T 2021-10-05-11.09.03.054067IBMQREP_PART_HIST ASN T 2021-10-05-11.09.03.467260IBMQREP_SCHEMASUBS ASN T 2021-10-05-11.09.04.131894IBMQREP_SENDQUEUES ASN T 2021-10-05-11.08.59.933157IBMQREP_SIGNAL ASN T 2021-10-05-11.09.01.090264IBMQREP_SRCH_COND ASN T 2021-10-05-11.09.00.931456IBMQREP_SRC_COLS ASN T 2021-10-05-11.09.00.644110IBMQREP_SUBS ASN T 2021-10-05-11.09.00.307720IBMQREP_SUBS_PROF ASN T 2021-10-05-11.09.03.849175IBMQREP_TABVERSION ASN T 2021-10-05-11.09.04.639726[db2inst1@node01 ~]$ db2 list tables for all | grep -i ibmq | wc -l20[db2inst1@node01 ~]$ db2 connect to DB111 USER DB2INST1 USING 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 ibmqIBMQREP_APPENVINFO ASN T 2021-10-05-11.09.10.559247IBMQREP_APPEVENTS ASN T 2021-10-05-11.09.11.643341IBMQREP_APPEVTDEFS ASN T 2021-10-05-11.09.11.373796IBMQREP_APPLYCMD ASN T 2021-10-05-11.09.12.862015IBMQREP_APPLYCMDOUT ASN T 2021-10-05-11.09.14.257730IBMQREP_APPLYENQ ASN T 2021-10-05-11.09.10.440788IBMQREP_APPLYMON ASN T 2021-10-05-11.09.09.489824IBMQREP_APPLYPARMS ASN T 2021-10-05-11.09.06.747733IBMQREP_APPLYTRACE ASN T 2021-10-05-11.09.09.185264IBMQREP_DONEMSG ASN T 2021-10-05-11.09.13.783978IBMQREP_EXCEPTIONS ASN T 2021-10-05-11.09.08.652661IBMQREP_MCGMON ASN T 2021-10-05-11.09.12.555526IBMQREP_MCGPARMS ASN T 2021-10-05-11.09.12.279571IBMQREP_MCGSYNC ASN T 2021-10-05-11.09.11.923687IBMQREP_RECVQUEUES ASN T 2021-10-05-11.09.07.080181IBMQREP_ROLLBACK_R ASN T 2021-10-05-11.09.10.992891IBMQREP_ROLLBACK_T ASN T 2021-10-05-11.09.10.705565IBMQREP_SAVERI ASN T 2021-10-05-11.09.10.237182IBMQREP_SPILLEDROW ASN T 2021-10-05-11.09.09.869775IBMQREP_SPILLQS ASN T 2021-10-05-11.09.08.301291IBMQREP_TAB_PROF ASN T 2021-10-05-11.09.13.284475IBMQREP_TARGETS ASN T 2021-10-05-11.09.07.488930IBMQREP_TRG_COLS ASN T 2021-10-05-11.09.07.961232[db2inst1@node01 ~]$ db2 list tables for all | grep -i ibmq | wc -l23

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 "SERVER TARGET TO DBALIAS DB111 ID db2inst1 PASSWORD "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]$ asnclpRepl > ASNCLP SESSION SET TO Q REPLICATIONRepl > SET SERVER CAPTURE TO DBALIAS DB110 ID db2inst1 PASSWORD "> SET SERVER TARGET TO DBALIAS DB111 ID db2inst1 PASSWORD "> list qsub for qcapture schema asnNAME 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 1Repl > 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:14ASN1514I 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 details2021-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 details2021-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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。