DB2 SQL Replication 配置方法

[測試環境]

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

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

[具體過程]

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

1.   創建數據庫

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

2.   將數據庫改為采用歸檔日志模式

$ db2 update db cfg for testdb using LOGARCHMETH1 LOGRETAIN

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

$ db2 connect to testdb

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

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

$ db2 backup db testdb

5.   創建表空間，指定表空間名，頁大小以及表空間大小

$ db2 "CREATE LARGE TABLESPACE tbs_test

      PAGESIZE 32768 MANAGED BY DATABASE

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

6.   創建源表（注意源表一定要有primary key或者unique index，否則后面操作會失敗）

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

7.   在源表中插入數據

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

Step2: 連到數據庫，并運行以下腳本：

1.   創建一個腳本（內容包括設置 server、創建control表、創建REGISTRATION、創建預定集、創建預定集成員）

$ cat> sqlrep.asnclp

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

# 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.   執行DB2 復制命令執行以上腳本

$ 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:觀察結果（可新開一窗口查看）：

1.   連接數據庫

$ 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

<---自動生成目標表TRGEMPLOYEE

  3 record(s) selected.

3.   顯示表空間

$ db2 list tablespaces | more

 Tablespace ID                        = 5
 Name                              = TSTRG00  

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

4.   顯示源庫與目標庫信息

$ 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.   顯示源表與目標表信息

$ 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.   查看目標表內容

$ db2 "select * from TRGEMPLOYEE"

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

  2 record(s) selected.

7.   在源表中插入一行數據

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

等1分鐘

8.   查看目標表內容

$ db2 "select * from TRGEMPLOYEE" 

ID       GIVENNAME           
----------- --------------------
      1  aa                  
      2  bb                  
      3  cc        <-- 1分鐘后查看，已經被復制過來了

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

9.   可通過以下方法查看源表與目標表的區別

$ 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.  如果發現源表與目標表出現差異，可使用以下方法修復

$ 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”復制又稱為“DB2 復制”，是為 DB2 開發的兩種數據復制類型中的一種，它是通過 SQL 進行的復制。

DB2 復制中的另一種”Q 復制”是通過 WebSphere MQ 隊列進行的。在進行 SQL 復制時，Capture 程序讀取 DB2 恢復日志以獲取對指定源表的更改。該程序將更改保存到分級表中，Apply 程序并行讀取更改并應用于目標事務。所以這兩種復制很明顯，唯一的區別只在于是否通過中間件MQ來實現。