本篇文章主要介绍了"DB2搭建SQL复制环境"，主要涉及到表空间方面的内容，对于DB2感兴趣的同学可以参考一下：
摘要： 本文在同一个数据下，搭建了一个SQL复制的环境，并做了相关的测试~[背景知识]在搭建环境之前，您需要了解SQL复制中基本的概念，比如capture, a...
摘要： 本文在同一个数据下，搭建了一个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 c 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 c 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.
[错误及解决]
