DB2 SQL Replication 配置方法

[測試環(huán)境]

DB2 9.7.0.11
LINUX 6.5
Source Table: EMPLOYEE(ID, FAMILYNAME, GIVENNAME)
Target Table: TRGEMPLOYEE，只復(fù)制源表的第1和第3列；不需要預(yù)先定義，會自動生成
數(shù)據(jù)庫名：TESTDB，本例子為源庫和目標(biāo)庫是同一數(shù)據(jù)庫

（如果源與目標(biāo)不在同一物理主機上，則需要分別在每臺物理主機上進行遠程的catalog操作）

[具體過程]

Step1: 確保TESTDB數(shù)據(jù)庫采用的是歸檔日志，create employee表并插入兩行數(shù)據(jù)

1.   創(chuàng)建數(shù)據(jù)庫

$ db2 "create database testdb on /home/db2inst1/testdb using codeset gbk territory CN pagesize 32 k"

2.   將數(shù)據(jù)庫改為采用歸檔日志模式

$ db2 update db cfg for testdb using LOGARCHMETH1 LOGRETAIN

3.   連接數(shù)據(jù)庫并查看是否更新為LOGRETAIN（修改后需重啟數(shù)據(jù)庫使參數(shù)生效）

$ db2 connect to testdb

$ db2 get db cfg | grep -i LOGARCHMETH1
 First log archive method                 (LOGARCHMETH1) = LOGRETAIN

4.   備份數(shù)據(jù)庫（修改歸檔模式之后需要備份一次數(shù)據(jù)庫）

$ db2 backup db testdb

5.   創(chuàng)建表空間，指定表空間名，頁大小以及表空間大小

$ db2 "CREATE LARGE TABLESPACE tbs_test

      PAGESIZE 32768 MANAGED BY DATABASE

      USING (FILE '/home/db2inst1/testdb/tbs_test' 500M )"

6.   創(chuàng)建源表（注意源表一定要有primary key或者unique index，否則后面操作會失?。?/span>

$ db2 "create table employee(id int NOT NULL, FAMILYNAME char(20), GIVENNAME char(20), PRIMARY KEY (ID) )"

7.   在源表中插入數(shù)據(jù)

$ db2 "insert into employee values(1,'a','aa'),(2,'b','bb')"

Step2: 連到數(shù)據(jù)庫，并運行以下腳本：

1.   創(chuàng)建一個腳本（內(nèi)容包括設(shè)置 server、創(chuàng)建control表、創(chuàng)建REGISTRATION、創(chuàng)建預(yù)定集、創(chuàng)建預(yù)定集成員）

$ cat> sqlrep.asnclp

以下為腳本內(nèi)容：(藍色字體為變量，可根據(jù)實際情況設(shè)定)

# identify databases involved.  
# ('Control' and 'Target' are usually the same)
SET SERVER CAPTURE TO DB TESTDB ID db2inst1 PASSWORD "pwd";
SET SERVER CONTROL TO DB TESTDB ID db2inst1 PASSWORD "pwd";
SET SERVER TARGET TO DB TESTDB ID db2inst1 PASSWORD "pwd";
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;

# Add meta data tables to hold information about
# your source and target tables.
CREATE CONTROL TABLES FOR CAPTURE SERVER;
CREATE CONTROL TABLES FOR APPLY CONTROL SERVER;

# Identify source table(s).
# Changed data will be 'staged' (stored) in a 'CD' table
CREATE REGISTRATION (DB2INST1.EMPLOYEE) DIFFERENTIAL REFRESH STAGE CDEMPLOYEE COLS (id, GIVENNAME);

# A subscription maps a source table to a target table.
# Subscriptions are grouped in sets.
# Every subscription must be in a set, so we make a set here:
CREATE SUBSCRIPTION SET SETNAME SET00 APPLYQUAL MYQUAL1 ACTIVATE YES TIMING INTERVAL 1 START DATE "2008-01-01" TIME "01:00:00.000000";

# Each subscription is a member of a set.
# If needed, you can create the target table as we do here:
SET PROFILE TBSPROFILE FOR OBJECT TARGET TABLESPACE OPTIONS UW USING FILE "/home/db2inst1/sqllib/TSTRG.TS" SIZE 700 PAGES;
CREATE MEMBER IN SETNAME SET00 APPLYQUAL MYQUAL1 ACTIVATE YES

SOURCE EMPLOYEE

TARGET NAME TRGEMPLOYEE

DEFINITION IN TSTRG00 CREATE USING PROFILE TBSPROFILE

TYPE USERCOPY COLS ALL REGISTERED;

# Now, run the file through the asnclp command
# and you are ready to replicate data.
QUIT;

2.   執(zhí)行DB2 復(fù)制命令執(zhí)行以上腳本

$ asnclp -f sqlrep.asnclp

Step3: 啟動capture和apply

1.   啟動capture(建議可放至后臺，這樣不用占用一個窗口)

$  asncap capture_server=testdb 

（后臺運行：$ nohup asncap capture_server=testdb &）

以下為正常的輸出，可通過nohup.out查看，如果你是用后臺跑的話
2017-09-08-01.48.04.703588 ASN0600I  "Capture" : "" : "Initial" : Program "capture 9.7.11 (Build 9.7.0.11 Level s150922, PTF IP23944)" is starting.

2017-09-08-01.48.06.817804 ASN0100I  CAPTURE "ASN" : "WorkerThread" : The Capture version "capture 9.7.11 (Build 9.7.0.11 Level s150922, PTF IP23944), DB2 v9.7.11" program initialized successfully.

2017-09-08-01.48.06.817850 ASN0109I  CAPTURE "ASN" : "WorkerThread". The Capture program has successfully initialized and is capturing data changes for "1" registrations. "0" registrations are in a stopped state. "0" registrations are in an inactive state.

2.   啟動apply

$ asnapply control_server=testdb apply_qual=MYQUAL1

（后臺運行：$ nohup asnapply control_server=testdb apply_qual=MYQUAL1 &）

以下為正常的輸出，可通過nohup.out查看，
2017-09-08-01.50.33.673457 ASN0600I  "Apply" : "" : "Initial" : Program "apply 9.7.11 (Build 9.7.0.11 Level s150922, PTF IP23944)" is starting.

2017-09-08-01.50.35.733818 ASN1045I  APPLY "MYQUAL1" : "Initial" : The Apply version "9.7.11" program was started using database "TESTDB".

Step4:觀察結(jié)果（可新開一窗口查看）：

1.   連接數(shù)據(jù)庫

$ db2 connect to testdb

2.   顯示此schema下的所有表（因為前面我們把這幾個表在db2inst1下建立）

$ db2 list tables for schema db2inst1

Table/View                      Schema          Type  Creation time   ------------------------------- --------------- ----- --------------------------

CDEMPLOYEE                      DB2INST1        T    2017-09-08-00.50.36.243736

EMPLOYEE                        DB2INST1        T    2017-09-08-00.41.29.577792

TRGEMPLOYEE                     DB2INST1        T    2017-09-08-00.50.37.243767

<---自動生成目標(biāo)表TRGEMPLOYEE

  3 record(s) selected.

3.   顯示表空間

$ db2 list tablespaces | more

 Tablespace ID                        = 5
 Name                              = TSTRG00  

<--自動生成目標(biāo)表空間TSTRG00
 Type                               = Database managed space
 Contents                            = All permanent data. Large table space.
 State                              = 0x0000
 Detailed explanation:
    Normal  

4.   顯示源庫與目標(biāo)庫信息

$ db2 "select APPLY_QUAL, SET_NAME,SOURCE_SERVER,TARGET_SERVER, STATUS from ASN.IBMSNAP_SUBS_SET"

APPLY_QUAL      SET_NAME        SOURCE_SERVER    TARGET_SERVER    STATUS
------------------ ------------------ ------------------ ------------------ ------
MYQUAL1        SET00          TESTDB         TESTDB         0

  1 record(s) selected.

5.   顯示源表與目標(biāo)表信息

$ db2 "select APPLY_QUAL, SET_NAME, substr(SOURCE_TABLE, 1,20) as SOURCE_TABLE, substr(TARGET_TABLE, 1,20) as TARGET_TABLE, MEMBER_STATE from  ASN.IBMSNAP_SUBS_MEMBR" 

APPLY_QUAL      SET_NAME        SOURCE_TABLE      TARGET_TABLE      MEMBER_STATE
------------------ ------------------ -------------------- -------------------- ------------
MYQUAL1        SET00          EMPLOYEE         TRGEMPLOYEE       L           
 
1 record(s) selected.

6.   查看目標(biāo)表內(nèi)容

$ db2 "select * from TRGEMPLOYEE"

ID       GIVENNAME           
----------- ---------------
      1  aa                  
      2  bb                  

  2 record(s) selected.

7.   在源表中插入一行數(shù)據(jù)

$ db2 "insert into EMPLOYEE values(3,'c','cc')"

等1分鐘

8.   查看目標(biāo)表內(nèi)容

$ db2 "select * from TRGEMPLOYEE" 

ID       GIVENNAME           
----------- --------------------
      1  aa                  
      2  bb                  
      3  cc        <-- 1分鐘后查看，已經(jīng)被復(fù)制過來了

  3 record(s) selected.
（在1分鐘的復(fù)制過程中可查看CDEMPLOYEE表，此表顯示第一次創(chuàng)建源表后的增量信息，注意在replication停止后此CD表會清空，直到下一次啟用replication后再重新寫入）

9.   可通過以下方法查看源表與目標(biāo)表的區(qū)別

$ asntdiff db=testdb where="target_table = 'TRGEMPLOYEE' and apply_qual = 'MYQUAL1' and set_name = 'SET00'"

2017-09-08-02.51.52.188642 ASN0600I  "AsnTDiff" : "" : "Initial" : Program "asntdiff 9.7.11 (Build 9.7.0.11 Level s150922, PTF IP23944)" is starting.

2017-09-08-02.51.52.259454 ASN4012I  "AsnTDiff" : "ASN" : "Initial" : The program is comparing tables using the list of parameters following this message.

 TDIFF   TABLE = "ASN"."ASNTDIFF" ;

 MAXIMUM COUNT = 100000 ;

  CONNECT TO TESTDB ;

  SELECT "GIVENNAME" AS "GIVENNAME",    "ID" AS "ID" FROM "DB2INST1"."EMPLOYEE"  ORDER BY 2 ;

  CONNECT TO TESTDB ;

  SELECT "GIVENNAME",    "ID" FROM "DB2INST1"."TRGEMPLOYEE"  ORDER BY 2 ;

  CREATE TABLE "ASN"."ASNTDIFF" (

    "DIFF "         CHAR(4),

    "ID"               INTEGER

  ) ;

2017-09-08-02.51.52.788416 ASN4006I  "AsnTDiff" : "ASN" : "Initial" : Between the source table and the target table, there are "5" common rows, "0" rows that are unique to the source table, and "0" rows that are unique to the target table.

2017-09-08-02.51.52.788515 ASN4011I  "AsnTDiff" : "ASN" : "Initial" : No differences were found between the source and target tables.

10.  如果發(fā)現(xiàn)源表與目標(biāo)表出現(xiàn)差異，可使用以下方法修復(fù)

$ asntrep db=testdb where="target_table = 'TRGEMPLOYEE' and apply_qual = 'MYQUAL1' and set_name = 'SET00'"

Step5:停止capture和apply

1.   停止capture

$ asnccmd capture_server=testdb stop

以下為正常的輸出
2017-09-08-01.08.28.738902 ASN0600I  "AsnCcmd" : "" : "Initial" : Program "capcmd 9.7.11 (Build 9.7.0.11 Level s150922, PTF IP23944)" is starting.

2017-09-08-01.08.30.750633 ASN0522I  "AsnCcmd" : "ASN" : "Initial" : The program received the "STOP" command.

2.   停止apply

$ asnacmd apply_qual=MYQUAL1 control_server=testdb stop

以下為正常的輸出

2017-09-08-01.08.39.308891 ASN0600I  "AsnAcmd" : "" : "Initial" : Program "applycmd 9.7.11 (Build 9.7.0.11 Level s150922, PTF IP23944)" is starting.

2017-09-08-01.08.41.320192 ASN0522I  "AsnAcmd" : "MYQUAL1" : "Initial" : The program received the "STOP" command.

[比較SQL Replication & Q Replication]

SQL Replication

 Q Replication

“SQL”復(fù)制又稱為“DB2 復(fù)制”，是為 DB2 開發(fā)的兩種數(shù)據(jù)復(fù)制類型中的一種，它是通過 SQL 進行的復(fù)制。

DB2 復(fù)制中的另一種”Q 復(fù)制”是通過 WebSphere MQ 隊列進行的。在進行 SQL 復(fù)制時，Capture 程序讀取 DB2 恢復(fù)日志以獲取對指定源表的更改。該程序?qū)⒏谋４娴椒旨壉碇?，Apply 程序并行讀取更改并應(yīng)用于目標(biāo)事務(wù)。所以這兩種復(fù)制很明顯，唯一的區(qū)別只在于是否通過中間件MQ來實現(xiàn)。