CDC复制软件标准模式和自适应模式的区别测试

总结：CDC的标准模式和自适应模式，类似于mysql主从复制的slave_exec_mode = STRICT模式和idempotent模式。自适应模式和源获胜机制很像，以源端为准，目标端找不到该行则跳过，
如果插入和更新遇到主键冲突则会把目标端的原行删除然后插入源端的行

先创建表 test_biaozhun，test_zishiying并插入数据

db2 "create table test_biaozhun(id int not null primary key,name varchar(10))"
db2 "create table test_zishiying(id int not null primary key,name varchar(10))"
$ db2 "select * from test_biaozhun"

ID          NAME      
----------- ----------
          1 zhangsan  
          2 lisi      
          3 zhaowu    
          4 maliu     

  4 record(s) selected.

$ db2 "select * from test_zishiying"

ID          NAME      
----------- ----------
          1 zhangsan  
          2 lisi      
          3 zhaowu    
          4 maliu     

  4 record(s) selected.

给这两表表搭建复制，分别是标准和自适应

（1）标准复制测试：
 
 1.目标端删除id=1的行，源端再删除id=1的行，预定会报错
[db2inst1@t3-ucm-ucm-rdb ~]$ db2 "delete from test_biaozhun where id=1"
DB20000I  The SQL command completed successfully.
无法从表 DB2INST1.TEST_BIAOZHUN 中删除一行。ID: [1] 。 
IBM InfoSphere Data Replication 已应用一批 1 行，JDBC 驱动程序发回报告已对这批中的条目 0 更新 0 行。这可能意味着，无法在数据库中找到这批中的该行。
请查看文档，关闭批处理或减小批处理大小，然后重试操作以缩小无法应用的行数。

2.目标端插入(1，lisi),源端插入(1，zhangsan88),源端更新id=1的行，目标端也更新成功了，说明CDC认为主键一样就是完全一样的行

源端：

$ db2 "update test_biaozhun set id=88 where id=1"
DB20000I  The SQL command completed successfully.

目标端：

[db2inst1@t3-ucm-ucm-rdb ~]$ db2 "select * from test_biaozhun"

ID          NAME      
----------- ----------
         88 zhangsan88

  1 record(s) selected.

 2。目标端删除id=2的行，源端再更新id=2的行，预定会报错
[db2inst1@t3-ucm-ucm-rdb ~]$ db2 "delete from test_biaozhun where id=2"
DB20000I  The SQL command completed successfully.
无法更新表 DB2INST1.TEST_BIAOZHUN 中的行。ID: [2] 。 
IBM InfoSphere Data Replication 已应用一批 1 行，JDBC 驱动程序发回报告已对这批中的条目 0 更新 0 行。这可能意味着，无法在数据库中找到这批中的该行。
请查看文档，关闭批处理或减小批处理大小，然后重试操作以缩小无法应用的行数。

3.目标端插入id=1的行，源端再插入id=1的行，预定会报错
db2 "insert into test_biaozhun values (1,'zhangsan')"
无法在表 DB2INST1.TEST_BIAOZHUN 中添加行。ID: [1] 。 
数据库操作失败。 
发生 SQL 异常。SQL 错误代码为“-4229”。SQL 状态为：[null]。错误消息为：
[jcc][102][10040][4.19.80] Batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch.
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null 
Caused by: 
发生 SQL 异常。SQL 错误代码为“-803”。SQL 状态为：23505。错误消息为：
One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, 
unique constraint or unique index identified by "1" constrains table "DB2INST1.TEST_BIAOZHUN" from having duplicate values for the index key.. 
SQLCODE=-803, SQLSTATE=23505, DRIVER=4.19.80

4.目标端插入id=2的行，源端再更新id=1的行成为id=2，预定会报错
db2 "insert into test_biaozhun values (2,'lisi')"
无法更新表 DB2INST1.TEST_BIAOZHUN 中的行。ID: [1] 。 
数据库操作失败。 
发生 SQL 异常。SQL 错误代码为“-4229”。SQL 状态为：[null]。错误消息为：
[jcc][102][10040][4.19.80] Batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch.
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null 
Caused by: 
发生 SQL 异常。SQL 错误代码为“-803”。SQL 状态为：23505。错误消息为：
One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, 
unique constraint or unique index identified by "1" constrains table "DB2INST1.TEST_BIAOZHUN" from having duplicate values for the index key.. 
SQLCODE=-803, SQLSTATE=23505, DRIVER=4.19.80

5.)如果在源端删除或者更新不存在的一行会怎么样？测试预定不会报错。不存在改行就不会生成任何物理日志或者逻辑日志，自然不会在目标端复制该sql

$ db2 "delete from test_biaozhun where id=9999"
SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a 
query is an empty table.  SQLSTATE=02000
$ db2 "update test_biaozhun set id=8888 where id=9999"
SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a 
query is an empty table.  SQLSTATE=02000

自适应复制测试：
   1.目标端删除id=1的行，源端再删除id=1的行，预定不会报错
  $ db2 "delete from test_zishiying where id=1"
   DB20000I  The SQL command completed successfully.
   2.目标端删除id=2的行，源端再更新id=2的行，预定不会会报错
[db2inst1@t3-ucm-ucm-rdb ~]$ db2 "delete from test_zishiying where id=2"

   3.目标端插入id=1的行，源端再插入id=1的行，预定不会报错，而且目标端将原id=1的行删除，     然后插入了源端的行
  目标端 db2 "insert into test_zishiying values (1,'zhangsan')"

  源端 db2 "insert into test_zishiying values (1,'zhangsan88')"
  目标端：
   [db2inst1@t3-ucm-ucm-rdb ~]$ db2 "select * from test_zishiying"

  ID          NAME      
 ---------- ----------
          1 zhangsan88
          3 zhaowu    
          4 maliu     
         10 lisi      

  4 record(s) selected.
  而我们再次在目标端手动插入id=1的行时，会报主键冲突
  [db2inst1@t3-ucm-ucm-rdb ~]$ db2 "insert into test_zishiying values (1,'zhangsan')"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or 
foreign key update caused by a DELETE statement are not valid because the 
primary key, unique constraint or unique index identified by "1" constrains 
table "DB2INST1.TEST_ZISHIYING" from having duplicate values for the index 
key.  SQLSTATE=23505

4.目标端插入id=2的行，源端再更新id=1的行成为id=2，预定不会报错，而且会更新成源端的行
 目标： db2 "insert into test_zishiying values (2,'lisi')"
 源端：
 $ db2 "insert into test_zishiying values (1,'zhangsan88')"
DB20000I  The SQL command completed successfully.
$ db2 "update test_zishiying set id=2 where id=1"
DB20000I  The SQL command completed successfully.
目标：[db2inst1@t3-ucm-ucm-rdb ~]$ db2 "select * from test_zishiying"

ID          NAME      
----------- ----------
          2 zhangsan88

5.接下来的测试让人大跌眼镜，自适应模式完全依靠主键为唯一标准，只要主键一致就认为是完全一样的行，目标端(1,'lisi')，源端是(1,'lisi88')，只要源删除id=1的行，目标端就把(1,'lisi')也删除了。
源端：
$ db2 "insert into test_zishiying values(1,'lisi88')"
DB20000I  The SQL command completed successfully.
$ db2 "delete from test_zishiying where id=1"
DB20000I  The SQL command completed successfully.
$ 
目标端：
[db2inst1@t3-ucm-ucm-rdb ~]$ db2 "insert into test_zishiying values(1,'lisi')"
DB20000I  The SQL command completed successfully.
[db2inst1@t3-ucm-ucm-rdb ~]$ db2 "insert into test_zishiying values(2,'lisi')"
DB20000I  The SQL command completed successfully.
[db2inst1@t3-ucm-ucm-rdb ~]$ db2 "select * from test_zishiying"                 

ID          NAME      
----------- ----------
          2 lisi      

  1 record(s) selected.
  
 6。 目标端是（1 ，lisi），（2， lisi），源端是(1,'lisi88')，源端将(1,'lisi88')更新成2,'lisi88')，目标端则直接更新（1 ，lisi）为（2 ，lisi88）并删除了主键冲突的（2， lisi）
  源端：
  $ db2 "insert into test_zishiying values(1,'lisi88')"
DB20000I  The SQL command completed successfully.
$ db2 "select * from test_zishiying"

ID          NAME      
----------- ----------
          1 lisi88    

  1 record(s) selected.

$ 
  
  目标端：
  [db2inst1@t3-ucm-ucm-rdb ~]$ db2 "select * from test_zishiying"

ID          NAME      
----------- ----------
          1 lisi      
          2 lisi      

  2 record(s) selected.
  
  
源库更新后db2 "update test_zishiying set id=2 where id=1"
[db2inst1@t3-ucm-ucm-rdb ~]$ db2 "select * from test_zishiying"

ID          NAME      
----------- ----------
          2 lisi88    

  1 record(s) selected.