Why Active/Active ? - Business RequirementsSENARIO OF TWO NODE QREPObjects needed in WebSphere MQOBJECTS IN QPREPARE ACTIONQ CONTROL TABLES -PARMTABLESCreate Q MapsCreate Q subscriptionsQ HOUSE-KEEPINT WORK ACTIONCHANLE ACTION:MQ QUEUE ACTIONQAPPLYLatency analysisLATENCY OUTLOOKCAPTURE LATENCY ANALYSISQ Apply latencyQREP RECOVERY
現在IDC大行其道，同城/異地雙活甚至多活的概念已經日益普及。12年的時候，寫過一篇mysql中關於MASTER/SLAVE的實現mysql replication---master/salve IN ONE PC。針對不同的DBMS，雙活具體的實現不同，但都基於相同的理論或是基於SQL REPLICATION,或是基於LOG REPLICATION。這裡介紹一下IBM中關於A/A的實現，關於Ｑ的架構以及實現後面專門抽出一章來進行介紹，這裡是實踐貼，使你對Ｑ有一個感性認識。關於本文最後的Q latency部分，也是每一個系統運維人員都必須要掌握的，因為它對於RPO/RTO的重要性不言而喻。
Replication Technologies for Business Continuity
Requirements and trade-offs to consider in selecting technologies:
What needs to be recovered?: Application Data vs. DBMS vs. Entire SystemsHow long does it take? Recovery Time Objective (RTO): One hours or more vs. few secondsHow much data could you lose? Recovery Point Objective (RPO): No data loss vs. seconds of dataDistance required between sites?: 10s of kilometers vs. 100s of kilometersHardware Utilization: Standby vs. ActiveImpact on applications: Direct overhead (synchronous technologies) vs. no impact (async technologies)CPU Overhead: Negligible (hardware e.g., PPRC) vs. Proportional to the workload (transaction replaytechnology)
OBJECT DEFINE DESCRIPTION
BIND REPLICATION PROGRAM
APF AUTHORIZE Q-RELATED PROGRAM
ENABLE Q TABEL DATA CAPTURES
ALTER TABLE QTABLE DATA CAPTURE CHANGES;
CONFIG CDB TABLE
遠程DB2連接使用DRDA協議，在使用之前需要配置對應的CDB信息。CDB 主要是Qapply用來連接Qcapture的屬性配置，第一條SQLP配置了本端的連接信息，後面3條SQL配置了，對端的連接信息。
NODE1:
INSERT INTO SYSIBM.LOCATIONS(LOCATION, LINKNAME, PORT) VALUES ('DB0A', 'DB0A', '');INSERT INTO SYSIBM.IPNAMES(LINKNAME, SECURITY_OUT, USERNAMES, IPADDR) VALUES ('DB0B', 'P', 'O', 'demo.TEST.com');INSERT INTO SYSIBM.USERNAMES(TYPE, LINKNAME, NEWAUTHID, PASSWORD) VALUES ('O', 'DB0B', 'TESTUSER', 'NOTTELLYOU');INSERT INTO SYSIBM.LOCATIONS(LOCATION, LINKNAME, PORT) VALUES ('DB0B', 'DB0B', '9000');NODE2:
INSERT INTO SYSIBM.LOCATIONS(LOCATION, LINKNAME, PORT) VALUES ('DB0B', 'DB0B', '');INSERT INTO SYSIBM.IPNAMES(LINKNAME, SECURITY_OUT, USERNAMES, IPADDR) VALUES ('DB0A', 'P', 'O', 'DEMO.TEST.com');INSERT INTO SYSIBM.USERNAMES(TYPE, LINKNAME, NEWAUTHID, PASSWORD) VALUES ('O', 'DB0A', 'BRIDDEL', 'XXXXXXXX');INSERT INTO SYSIBM.LOCATIONS(LOCATION, LINKNAME, PORT) VALUES ('DB0A', 'DB0A', '8000');定義MQ傳輸使用的各種對象。
DEFINE QLOCAL(MQ1.ADMINQ) DESC('ADMINQ OF MQ2 IN SITEA') PUT(ENABLED) GET(enabled) SHARE DEFSOPT(SHARED) MAXDEPTH(1000) DEFPSIST(YES)DEFINE QLOCAL(MQ1.RESTARQ) DESC('RESTARTQ OF MQ2 IN SITEA') PUT(ENABLE) GET(ENABLE) SHARE DEFSOPT(SHARED) MAXDEPTH(1) INDEXTYPE(MSGID) DEFPSIS(YES)DEFINE QLOCAL(MQ1.SENDFQ.Q1) REPLACE DESC('LOCAL SEND Q FOR FX Q') PUT(ENABLED) GET(ENABLED) SHARE DEFSOPT(SHARED) DEFPSIS(YES) MAXDEPTH(99999) INDEXTYPE(MSGID)DEFINE QMODEL('IBMQREP.SPILL,QMODELQ') REPLACE DEFSOPT(SHARED) MAXDEPTH(99999999) MSGDLVSQ(FIFO) DEFTYPE(PERMDYN)DEFINE CHANNEL('MQ1.TO.MQ2') REPLACE CHLTYPE(SDR) TRPTYPE(TCP) DISCINT(0) DESC('SENDER CHANNEL TO MQ2') XMITQ(XMQ1) CONNAME('10.2.1.1(9001)')
DEFPSIST
YES means that unless instructed otherwise, the Queue Manager logs transactions to these queues and can recover those messages in the event of failure or restart.
All control tables that are located on a node have to have the same schema.both the Q Capture and Q Apply on NODE 1 will use the replication schema ASN1 AND CONTROL TABLE ON NODE USE ASN2.
這裡配置Ｑ腳本是通過 ASNCLP配置的。它既支持交互方式，也支持批量方式。
舉例說明
SET QMANAGER "MQ1A" FOR NODE 1;SET QMANAGER "MQ1B" FOR NODE 2;CREATE CONTROL TABLES FOR NODE 1 USINGCAPPARMSRESTARTQ "MQ1.RESTARTQ"ADMINQ "MQ1.ADMINQ"MONITOR INTERVAL 10000APPPARMSIN ZOS PAGE LOCK DB DEMODB QCNTLAP CREATEROW LOCK DB DEMODB2 QCNTLAR CREATEMONITOR INTERVAL 10000;CREATE CONTROL TABLES FOR NODE 2 USINGCAPPARMSRESTARTQ "MQ2.RESTARTQ"ADMINQ "MQ2.ADMINQ"MONITOR INTERVAL 10000APPPARMSIN ZOS PAGE LOCK DB RBBDEMO QCNTLAP CREATEROW LOCK DB RBBDEMO QCNTLAR CREATEMONITOR INTERVAL 10000;
Two Q Maps were used for the first part of this exercise. Even though a single Q
Map can efficiently contain hundreds of Q Subscriptions, you might want to
separate your subscriptions into multiple Q Maps for administrative purposes.
這裡即確定了表的訂閱關系。
CREATE REPLQMAP BIDIND1_TO_BIDIND2_MAP1 ( NODE 1, NODE 2 ) USINGADMINQ "BIDIND1.ADMINQ"RECVQ "BIDIND1.TO.BIDIND2.DATAQ1"SENDQ "BIDIND1.TO.BIDIND2.DATAQ1"NUM APPLY AGENTS 2;CREATE REPLQMAP BIDIND1_TO_BIDIND2_MAP2 ( NODE 1, NODE 2 ) USINGADMINQ "BIDIND1.ADMINQ"RECVQ "BIDIND1.TO.BIDIND2.DATAQ2"SENDQ "BIDIND1.TO.BIDIND2.DATAQ2"NUM APPLY AGENTS 2;
定義一張表屬於哪一個Qmap,以及是否進行Ｑ復制。
## Use MAP1 for TABLE1 and TABLE2#SET CONNECTION SOURCE DB1A.ASNB1 TARGET DB1C.ASNB2REPLQMAP BIDIND1_TO_BIDIND2_MAP1 ;SET TABLES (DB1.ASN1.TABLE1);CREATE QSUB SUBTYPE UFROM NODE DB1A.ASNB1 SOURCE HAS LOAD PHASE NTARGET CONFLICT ACTION IFROM NODE DB1C.ASNB2 SOURCE HAS LOAD PHASE NTARGET CONFLICT ACTION F;
START CHANNEL(MQ1A.TO.MQ1B)
DISPLAY CHSTATUS(MQ1BA.TO.MQ1B)
START CHANNEL(MQ1B.TO.MQ1A)
DISPLAY CHSTATUS(MQ1B.TO.MQ1A)
+MQ2 CLEAR QLOCA(MQ2.SENDQ.Q1)DISPLAY QLOCAL(MQ2.SENDQ.Q1) CURDEPTH
關於Q latency它與我們搭建Ｑ的目標息息相關，特別是對IDC RPT/ROT 兩個指標的影響巨大。因此分析Ｑ lantency變成了DBA一項必須掌握的技能。要想進行latency分析，你必須明確latency是如何定義的，現有的工具以及如何進行調整。
Control tables for replication monitoring
? IBMQREP_CAPMON
? IBMQREP_CAPQMON
? IBMQREP_APPLYMON
上圖中每一個數字對應的動作如下：
t1 = transaction committed at the source site
t2 = the Q Capture program read the transaction from the log
t3 = the Q Capture program committed the message to the send queue
t4 = the Q Apply program read the message from the receive queue
t5 = the Q Apply successfully committed the transaction at the target site
對應的Ｑ END-TO-END latency=T5-T1
即一個交易在目標端與源端commit之間的時間間隔。影響Q latency的因素一個有10個，具體的分布如下：
需要說明的是背景為藍色的影響因素對Q 的配置以及SQL語句關聯較大。而白色背景部分的影響相對可控，前提是MQ,DBMS WORK WELL.
下面給出上述10個影響因素的定義，對應的monitor信息，以及如何調優。
LOGREAD_API_TIME:IBMQREP_CAPMON The time spent in DB2 returning log records to Q CaptureNUM_END_OF_LOGS:IBMQREP_CAPMON table, lists the number of times that the Q Capture program reached the end of the DB2 logLOGRDR_SLEEPTIME:IBMQREP_CAPMON LOGREADER SLEEP TIME FOR NUM_END_OF+_LOGS OR it reached its memory limit thresholdMQPUT_TIME:IBMQREP_CAPMONThe time delay spent in WebSphere MQ to put new messages in the send queue MQCMIT_TIME: IBMQREP_CAPMON The time delay spent in WebSphere MQ to commit the MQ transactions XMITQDEPTH : IBMQREP_CAPQMON The transmit queue depth valu If the transmit queue depth keeps growing and there is a lot of I/O to the physical pageset of the transmit queue, this means that the capacity of the MQ channel has been exceeded
TRAN_BATCH_SZIE
平均每一個交易的大小 = MQ_BYTES/TRANS_PUBLISHED
平均每一個消息所包含的交易數目=MQ_MESSAGES /TRANS_PUBLISHED，該值應該近似等於TRAN_BATCH_SIZE
WORKQ_WAIT_TIME:IBMQREP_APPLYMON table tracks how long it takes transactions that are ready to be applied at the target site to be picked up by a Q Apply agentRETRY_TIME :IBMQREP_APPLYMON Q Apply is retrying some of the SQL statements included inthe replicated transactions,SUCH RI,UI,TIMEOUT,DEADLOCKThe DBMS_TIME :IBMQREP_APPLYMON tracks the average time spent in DB2 applying the transactions for a given Q Apply monitor interval. The transactions include user tables (replicated tables) and Q Replication tables (control table data that tracks the Q Apply progress)
STARTLRSN: lsn value from where it is safe to start reading the DB2 log
commit lsn: lsn value up to which it is safe to skip committed transactions as Q Capture is reading the log records, because the transactions were previously published.
本文所有的內容均整理自互聯網，僅供參考學習，如有涉及版權問題，請自行刪除本文，謝謝。
