DB2搭建SQL复制环境

摘要： 本文在同一个数据下，搭建了一个SQL复制的环境，并做了相关的测试~

[背景知识]

在搭建环境之前，您需要了解SQL复制中基本的概念，比如capture, apply, control table, CD表, registration, subscription set, member.如不了解，请先行参考下面的链接
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.swg.im.iis.db.repl.intro.doc/topics/iiyrcintrssql.html?lang=en

[测试环境]

DB2 9.7.0.6
AIX 6.1
Source Table: EMPLOYEE(ID, FAMILYNAME, GIVENNAME)
Target Table: TRGEMPLOYEE，只复制源表的第1和第3列；不需要预先定义，会自动生成
数据库名：sample，且源库和目标库是同一数据库

[具体过程]

Step1: 确保SAMPLE数据库采用的是归档日志，create employee表并插入两行数据

$ db2 create db sample
$ db2 update db cfg for sample using LOGARCHMETH1 LOGRETAIN
$ db2 backup db sample
$ db2 connect to sample
$ db2 get db cfg | grep -i LOGARCHMETH1
 First log archive method                 (LOGARCHMETH1) = LOGRETAIN
 Options for logarchmeth1                  (LOGARCHOPT1) =

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

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

Step2: 连到数据库，并运行以下脚本：

oasnclp -f sqlrep.asnclp
其中，sqlrep.asnclp内容如下，命令包括：设置 server、创建control表、创建REGISTRATION、创建预定集、创建预定集成员

# identify databases involved.  
# ('Control' and 'Target' are usually the same)
SET SERVER CAPTURE TO DB SAMPLE ID e97q6c PASSWORD "pswd";
SET SERVER CONTROL TO DB SAMPLE ID e97q6c PASSWORD "pswd";
SET SERVER TARGET  TO DB SAMPLE ID e97q6c PASSWORD "pswd";
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 (E97Q6C.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/db2users/e97q6c/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;

Step3: 启动capture和apply

SESSION1:
$  asncap capture_server=sample
2015-12-06-13.37.30.324008 ASN0600I  "Capture" : "" : "Initial" : Program "capture 9.7.6" is starting.
2015-12-06-13.37.31.094227 ASN0561W  "Capture" : "ASN" : "Initial" : The program's application code page "819" is not the same as the code page "1208" of database "SAMPLE".
2015-12-06-13.37.33.322770 ASN0100I  CAPTURE "ASN" : "WorkerThread" : The Capture version "9.7.6" program initialized successfully.
2015-12-06-13.37.33.323242 ASN0109I  CAPTURE "ASN" : "WorkerThread". The Capture program has successfully initialized and is capturing data changes for "0" registrations. "0" registrations are in a stopped state. "1" registrations are in an inactive state.

Session2:
$ asnapply control_server=sample apply_qual=MYQUAL1
2015-12-06-13.37.50.461285 ASN0600I  "Apply" : "" : "Initial" : Program "apply 9.7.6" is starting.
2015-12-06-13.37.52.857361 ASN1045I  APPLY "MYQUAL1" : "Initial" : The Apply version "9.7.6" program was started using database "SAMPLE".

Step4: 新开一窗口，观察结果：

Session3:
$ db2 connect to sample

$ db2 list tables
Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
CDEMPLOYEE                      E97Q6C          T     2015-12-06-13.36.57.185199
EMPLOYEE                        E97Q6C          T     2015-12-06-13.35.16.448487
TRGEMPLOYEE                     E97Q6C          T     2015-12-06-13.36.58.301661  <---自动生成目标表TRGEMPLOYEE

  3 record(s) selected.

$ db2 list tablespaces | more
 Tablespace ID                        = 3
 Name                                 = TSTRG00   <--自动生成目标表空间TSTRG00
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal  
  
$ 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              SAMPLE             SAMPLE                  0

  1 record(s) selected.

$ 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.

$ db2 "select * from TRGEMPLOYEE"

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

  2 record(s) selected.

$ db2 "insert into EMPLOYEE values(3,'c','cc')"
DB20000I  The SQL command completed successfully.

//Wait 1 minutes

$ db2 "select * from TRGEMPLOYEE" 

ID          GIVENNAME           
----------- --------------------
          1 aa                  
          2 bb                  
          3 cc        <-- 一分钟后查看，已经被复制过来了

  3 record(s) selected.
  

Step5:停止capture和apply

Session3:
$ asnccmd capture_server=sample stop
2015-12-06-14.14.59.465632 ASN0600I  "AsnCcmd" : "" : "Initial" : Program "capcmd 9.7.6" is starting.
2015-12-06-14.15.01.487184 ASN0522I  "AsnCcmd" : "ASN" : "Initial" : The program received the "STOP" command.

$ asnacmd apply_qual=MYQUAL1 control_server=sample stop
2015-12-06-14.15.07.164747 ASN0600I  "AsnAcmd" : "" : "Initial" : Program "applycmd 9.7.6" is starting.
2015-12-06-14.15.09.185527 ASN0522I  "AsnAcmd" : "MYQUAL1" : "Initial" : The program received the "STOP" command.

[错误及解决]

employee表一定要有primary key或者unique index，否则会失败。
第一次试验的时候，创建表定义如下
$ db2 "create table employee(id int, FIRSTNAME char(20), GIVENNAME char(20))"

结果在启动apply的时候，报出以下错误
$ asnapply control_server=sample apply_qual=MYQUAL1
2015-12-06-10.48.25.284625 ASN0600I  "Apply" : "" : "Initial" : Program "apply 9.7.6" is starting.
2015-12-06-10.48.25.943151 ASN1045I  APPLY "MYQUAL1" : "Initial" : The Apply version "9.7.6" program was started using database "SAMPLE".
2015-12-06-10.48.26.080955 ASN1008E  APPLY "MYQUAL1" : "WorkerThread". The subscription set with Apply qualifier "MYQUAL1" and set name "SET00" is not defined correctly. ERRCODE is "43080D".

[参考资料]  

https://www.ibm.com/developerworks/community/files/basic/anonymous/api/library/87742721-7cfd-4134-bba6-c23dc23fd5b1/document/cd1a8685-22f8-490c-8bc3-d3b87a292036/media