DB2 SQL Replication 配置方法

Replication Server 产品包括 sql 复制和 Q 复制两种技术。相比于 SQL 复制，Q 复制的数据传输技术借助于 IBM 队列机制，从而提高了数据的传输效率和可靠性，可以达到每秒复制几千个表、几十万行数据 , 在几千英里的距离下端到端的延迟不到 1 秒（从源端 DB2 commit 到目标端 DB2 commit）。Change Data Capture(CDC) 主要采用基于日志的变更数据捕获技术以实现对关键业务系统的实时数据复制，同时不影响业务系统性能。CDC 最大优点在于其对异构数据库的支持和与 IBM 产品的集成，为客户在数据迁移，整合，同步，动态数据仓库等应用中提供了卓越的解决方案。这次分享讲的是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来实现。