SQL replication setup

最新推荐文章于 2022-10-27 12:52:05 发布

AugLost

最新推荐文章于 2022-10-27 12:52:05 发布

阅读量1.5k

点赞数

分类专栏： DB2

DB2 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

DB2 DPROP
实例        数据库        host            server
inst97    sourcedb    localhost    60000
inst97    targetdb   localhost     60000
创建测试库：
[inst97@ha01 ~]$ mkdir /db2data/sourcedb
[inst97@ha01 ~]$ db2sampl -dbpath /db2data/sourcedb -name sourcedb -force -verbose

[inst97@ha01 ~]$ mkdir /db2data/targetdb
[inst97@ha01 ~]$ db2sampl -dbpath /db2data/targetdb -name targetdb -force -verbose

开启归档模式：
[inst97@ha01 ~]$ db2 update db cfg for sourcedb using logarchmeth1 tsm
#backup pending ,记得备份

测试表：
[inst97@ha01 ~]$ db2 "create table t1(t_no int,t_char char(3))"
DB20000I The SQL command completed successfully.
[inst97@ha01 ~]$ db2 "insert into t1 values (1,'a')"
DB20000I The SQL command completed successfully.
[inst97@ha01 ~]$ db2 "insert into t1 values (2,'b')"
DB20000I The SQL command completed successfully.
[inst97@ha01 ~]$ db2 "select * from t1"

T_NO        T_CHAR
----------- ------
          1 a
          2 b

2 record(s) selected.
[inst97@ha01 ~]$ db2 "create unique index inst97.t1 on inst97.t1(t_no)"
DB20000I The SQL command completed successfully.

密码文件：
[inst97@ha01 ~]$ mkdir dprop
[inst97@ha01 ~]$ cd dprop
[inst97@ha01 dprop]$ asnpwd init
[inst97@ha01 dprop]$ asnpwd add alias targetdb id inst97 password "inst97"
[inst97@ha01 dprop]$ asnpwd add alias sourcedb id inst97 password "inst97"

创建capture 控制表：
[inst97@ha01 dprop]$ asnclp
Repl > set server capture to db sourcedb id inst97 password "inst97"
Repl>create control tables for capture server
db2 connect to sourcedb
[db2insta@oc8662238374 ~]$ db2 -tvf replcap.sql
创建apply 控制表：
[inst97@ha01 dprop]$ asnclp
Repl > set server control to db targetdb id inst97 password "inst97"
Repl > create control tables for apply control server
db2 connect to targetdb
[inst97@ha01 dprop]$ db2 -tvf replctl.sql
db2 connect to sourcedb
[inst97@ha01 dprop]$ db2 list tables for schema asn

Table/View                      Schema          Type Creation time
------------------------------- --------------- ----- --------------------------
IBMQREP_IGNTRAN                 ASN             T     2014-02-20-17.05.40.699126
IBMQREP_IGNTRANTRC              ASN             T     2014-02-20-17.05.42.914427
IBMQREP_PART_HIST               ASN             T     2014-02-20-17.05.45.416304
IBMSNAP_CAPENQ                  ASN             T     2014-02-20-17.05.33.725156
IBMSNAP_CAPMON                  ASN             T     2014-02-20-17.05.37.239591
IBMSNAP_CAPPARMS                ASN             T     2014-02-20-17.05.30.148341
IBMSNAP_CAPSCHEMAS              ASN             T     2014-02-20-17.05.15.747234
IBMSNAP_CAPTRACE                ASN             T     2014-02-20-17.05.27.396544
IBMSNAP_PRUNCNTL                ASN             T     2014-02-20-17.05.21.489077
IBMSNAP_PRUNE_LOCK              ASN             T     2014-02-20-17.05.39.632296
IBMSNAP_PRUNE_SET               ASN             T     2014-02-20-17.05.24.829617
IBMSNAP_REGISTER                ASN             T     2014-02-20-17.05.18.505212
IBMSNAP_RESTART                 ASN             T     2014-02-20-17.05.14.292999
IBMSNAP_SIGNAL                  ASN             T     2014-02-20-17.05.34.916539
IBMSNAP_UOW                     ASN             T     2014-02-20-17.05.31.255573

15 record(s) selected.
db2 connect to targetdb
[inst97@ha01 dprop]$ db2 list tables for schema asn

Table/View                      Schema          Type Creation time
------------------------------- --------------- ----- --------------------------
IBMSNAP_APPENQ                  ASN             T     2014-02-20-17.35.41.481663
IBMSNAP_APPLYMON                ASN             T     2014-02-20-17.36.11.838032
IBMSNAP_APPLYTRACE              ASN             T     2014-02-20-17.35.49.642692
IBMSNAP_APPLYTRAIL              ASN             T     2014-02-20-17.36.01.621041
IBMSNAP_APPPARMS                ASN             T     2014-02-20-17.36.04.204388
IBMSNAP_COMPENSATE              ASN             T     2014-02-20-17.36.06.913110
IBMSNAP_FEEDETL                 ASN             T     2014-02-20-17.36.09.196305
IBMSNAP_SUBS_COLS               ASN             T     2014-02-20-17.35.53.717906
IBMSNAP_SUBS_EVENT              ASN             T     2014-02-20-17.35.59.212132
IBMSNAP_SUBS_MEMBR              ASN             T     2014-02-20-17.35.47.066862
IBMSNAP_SUBS_SET                ASN             T     2014-02-20-17.35.44.392105
IBMSNAP_SUBS_STMTS              ASN             T     2014-02-20-17.35.56.667347

12 record(s) selected.
在控制表中插入相关数据

控制表创建完成后，我们需要在控制表中插入相关的数据。

capture创建 registration
[inst97@ha01 dprop]$ asnclp
Repl > asnclp session set to sql replication
Repl > set output capture script "register.sql"
Repl > set server capture to db sourcedb id inst97 password "inst97"
Repl > create registration (db2insta.t1) differential refresh
[inst97@ha01 dprop]$ db2 -tvf register.sql

apply创建 subscription set
[inst97@ha01 dprop]$ db2 connect to targetdb
Repl > asnclp session set to sql replication
Repl > set server capture to db sourcedb id inst97 password "inst97"
Repl > set server control to db targetdb id inst97 password "inst97"
Repl > set server target to db targetdb id inst97 password "inst97"
Repl > set output capture script "capsubset.sql" control script "appsubset.sql"
Repl > create subscription set setname set00 applyqual aq00 activate yes timing interval 1
[inst97@ha01 dprop]$ db2 -tvf appsubset.sql
（更改interval：db2 "update asn.ibmsnap_subs_set set sleep_minutes=1 where apply_qual='AQ00' and set_name='SET00'"）
apply创建 subscription member
Repl > asnclp session set to sql replication
Repl > set server capture to db sourcedb id inst97 password "inst97"
Repl > set server control to db targetdb id inst97 password "inst97"
Repl > set server target to db targetdb id inst97 password "inst97"
Repl > set output capture script "capmember.sql" control script "appmember.sql"
#set profile tbsprofile for object target tablespace options uw using file "/home/zhicheng/apply/tstrg.ts" size 2000 pages
#create member in setname set00 applyqual aq00 activate yes source tab01 target name tab01 definition in tstrg00 create using profile tbsprofile type usercopy cols all registered
Repl > set profile tbsprofile for object target tablespace options uw using file "/home/inst97/dprop/tstrg.ts" size 2000 pages
Repl > create member in setname set00 applyqual aq00 activate yes source inst97.t1 target name inst97.t1_bak definition in tstrg00 create using profile tbsprofile type usercopy cols all registered
[inst97@ha01 dprop]$ db2 -tvf appmember.sql
[inst97@ha01 dprop]$ db2 -tvf repltrg.sql
db2 connect to sourcedb
[inst97@ha01 dprop]$ db2 -tvf capmember.sql

第一次启动capture
[inst97@ha01 dprop]$ nohup asncap capture_server=sourcedb capture_schema=asn capture_path=/home/inst97/dprop startmode=cold &
[inst97@ha01 dprop]$ nohup asncap capture_server=sourcedb capture_schema=asn capture_path=/home/inst97/dprop startmode=warmns &
停止capture
[inst97@ha01 dprop]$ nohup asnccmd capture_server=sourcedb capture_schema=asn stop &
启动apply
[inst97@ha01 dprop]$ nohup asnapply control_server=targetdb apply_qual=aq00 apply_path=/home/inst97/dprop &
停止apply
[inst97@ha01 dprop]$ nohup asnacmd control_server=targetdb apply_qual=aq00 stop &
运行结果：
db2 connect to sourcedb
[inst97@ha01 dprop]$ db2 "insert into t1 values (3,'C')"
[inst97@ha01 dprop]$ db2 "select * from t1"

ID          NAME
----------- ----------
          1 a
          2 b
          3 C
3 record(s) selected.
[inst97@ha01 dprop]$ db2 "select * from cdt1"

IBMSNAP_COMMITSEQ IBMSNAP_INTENTSEQ IBMSNAP_OPERATION T_NO T_CHAR
------------------------------------- ------------------------------------- ----------------- ----------- ------
x'54D0DE70000000010000' x'00000000000002F675A2' I 3 C

1 record(s) selected.

db2 connect to targetdb
[inst97@ha01 dprop]$ db2 "select * from t1_bak"

ID          NAME
----------- ----------
          1 a
          2 b
          3 C

3 record(s) selected.