|
最近碰到一个三点 SQL 复制的问题,APPLY 状态为 -1 (The replication failed), 我用一个测试来还原真相,希望对大家有所帮助。
1.首先介绍一下环境
Suse Linux 下的 DB2 v9.1.0.6,db2inst1/2/3 代表三个不同的点
被复制表的结构
create table sr.s_tab(id integer not null PRIMARY KEY,content varchar(10) not null Unique )
2.错误发生以后检查各个点上的 apply 状态和 Apply Log
db2inst1@ibmswg01:~> db2 "select set_name, status, lastsuccess from asn.ibmsnap_subs_set with ur"
SET_NAME STATUS LASTSUCCESS
------------------ ------ --------------------------
PEER2TOPEER1 -1 2010-02-03-01.38.38.925787
PEER3TOPEER1 0 2010-02-03-01.44.40.263213
2 record(s) selected.
db2inst2@ibmswg01:~> db2 "select set_name, status, lastsuccess from asn.ibmsnap_subs_set with ur"
SET_NAME STATUS LASTSUCCESS
------------------ ------ --------------------------
PEER1TOPEER2 -1 2010-02-03-01.38.41.502129
PEER3TOPEER2 0 2010-02-03-01.45.12.215878
db2inst3@ibmswg01:~> db2 "select set_name, status, lastsuccess from asn.ibmsnap_subs_set with ur"
SET_NAME STATUS LASTSUCCESS
------------------ ------ --------------------------
PEER1TOPEER3 -1 2010-02-03-01.38.45.122122
PEER2TOPEER3 0 2010-02-03-01.45.35.124049
Apply Log 如下所示
2010-02-03-01.38.51.244350 <CPDOUPD(CA/05)> ASN1001E APPLY "SAMPLEAQ" : "WorkerThread".
The Apply program encountered an SQL error. The ERRCODE is "CA0105". The SQLSTATE is "23505".
The SQLCODE is "-803". The SQLERRM is "2??SR.S_TAB". The SQLERRP is "SQLDMISR". Te server name is "".
The table name is "S_TAB".
3. 查看 SR.S_TAB 表的数据
db2inst1@ibmswg01:~/repl_setup> db2 "select * from sr.s_tab"
ID CONTENT
----------- ----------
1 a
3 c
2 b
4 ab
4 record(s) selected.
db2inst2@ibmswg01:~/repl_setup> db2 "select * from sr.s_tab"
ID CONTENT
----------- ----------
2 b
1 a
3 c
5 ab
4 record(s) selected.
db2inst3@ibmswg01:~/repl_setup> db2 "select * from sr.s_tab"
ID CONTENT
----------- ----------
1 a
3 c
2 b
5 ab
4 record(s) selected.
这个测试场景非常简单(真实环境复杂很多),大家大概也就能猜出问题所在,欢迎大家各抒己见,明天我再来说说解决办法吧 |
|