db2平台下oracle goldengate配置支持truncate操作测试记录

1、测试环境：

os：suse linux 10

db：db2 9.7.5

gg：goldengate 11.1.1.0.0

2、测试目的：

源端执行truncate操作，目的端进程报错（参见本文附录部分内存），即默认不支持truncate操作；

测试源端加上参数GETTRUNCATES、目的端加上参数IGNORETRUNCATES后，会忽略掉源端的truncate操作，进程不再报错；

即支持truncate操作：源端执行truncate，目的端也需要执行一次truncate，确保两端数据一致。

测试通过后，调整生产环境中的goldengate配置。

3、测试时间：

2013年3月1日

4、测试记录：

4.1、源端插入4条记录：

db2inst1@test2:~> db2 "insert into db2inst1.test14 values('1','test')"

DB20000I  The SQL command completed successfully.

db2inst1@test2:~> db2 "insert into db2inst1.test14 values('2','test')"

DB20000I  The SQL command completed successfully.

db2inst1@test2:~> db2 "insert into db2inst1.test12 values('1','test')"

DB20000I  The SQL command completed successfully.

db2inst1@test2:~> db2 "insert into db2inst1.test12 values('2','test')"

DB20000I  The SQL command completed successfully.

4.2、正常GG同步到目的端：

db2inst1@test1:~> db2 "select * from db2inst1.test14"

ID          NAME     

----------- ----------

          1 test     

          2 test     

  2 record(s) selected.

db2inst1@test1:~> db2 "select * from db2inst1.test12"

ID          NAME     

----------- ----------

          1 test     

          2 test     

  2 record(s) selected.

4.3、添加GG参数支持truncate操作：

源端进程停止后编辑添加

GETTRUNCATES

目的端进程停止后编辑添加

IGNORETRUNCATES

4.4、源端执行truncate操作

db2 "LOAD FROM /dev/null OF DEL REPLACE INTO db2inst1.test14 NONRECOVERABLE"

db2 "LOAD FROM /dev/null OF DEL REPLACE INTO db2inst1.test12 NONRECOVERABLE"

check约束（先父表后子表）

db2inst1@test2:~> db2 "SET INTEGRITY FOR db2inst1.test14 IMMEDIATE CHECKED"

DB20000I  The SQL command completed successfully.

db2inst1@test2:~> db2 "SET INTEGRITY FOR db2inst1.test12 IMMEDIATE CHECKED"

DB20000I  The SQL command completed successfully.

4.5、启动进程

GGSCI (test2) 11> start extdatat

Sending START request to MANAGER ...

EXTRACT EXTDATAT starting

GGSCI (test2) 12> start dpedatat

Sending START request to MANAGER ...

EXTRACT DPEDATAT starting

GGSCI (test2) 13> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     RUNNING     DPEDATAT    00:00:00      00:07:55   

EXTRACT     RUNNING     EXTDATAT    00:11:54      00:00:05   

REPLICAT    STOPPED     REPGT       00:00:00      1726:51:43

GGSCI (test1) 15> start reptest

Sending START request to MANAGER ...

REPLICAT REPTEST starting

GGSCI (test1) 16> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     STOPPED     DPEGT       00:00:00      1726:52:44 

EXTRACT     STOPPED     EXTGT       00:00:00      1726:52:49 

REPLICAT    RUNNING     REPTEST     00:08:29      00:00:01

4.6、目的端检查数据：

db2inst1@test1:~> db2 "select * from db2inst1.test14"

ID          NAME     

----------- ----------

          1 test     

          2 test     

  2 record(s) selected.

db2inst1@test1:~> db2 "select * from db2inst1.test12"

ID          NAME     

----------- ----------

          1 test     

          2 test     

  2 record(s) selected.

4.7、目的端执行truncate

db2inst1@test1:~> db2 "LOAD FROM /dev/null OF DEL REPLACE INTO db2inst1.test14 NONRECOVERABLE"

db2inst1@test1:~> db2 "LOAD FROM /dev/null OF DEL REPLACE INTO db2inst1.test12 NONRECOVERABLE"

db2inst1@test1:~> db2 "SET INTEGRITY FOR db2inst1.test14 IMMEDIATE CHECKED"

DB20000I  The SQL command completed successfully.

db2inst1@test1:~> db2 "SET INTEGRITY FOR db2inst1.test12 IMMEDIATE CHECKED"

DB20000I  The SQL command completed successfully.

db2inst1@test1:~>

4.8、检查数据

db2inst1@test2:~> db2 "select * from db2inst1.test14"

ID          NAME     

----------- ----------

  0 record(s) selected.

db2inst1@test2:~> db2 "select * from db2inst1.test12"

ID          NAME     

----------- ----------

  0 record(s) selected.

db2inst1@test1:~> db2 "select * from db2inst1.test14"

ID          NAME      

----------- ----------

  0 record(s) selected.

db2inst1@test1:~> db2 "select * from db2inst1.test12"

ID          NAME     

----------- ----------

  0 record(s) selected.

db2inst1@test1:~>

5、测试结果：

源端加上参数GETTRUNCATES、目的端加上参数IGNORETRUNCATES后，会忽略掉源端的truncate操作，进程不再报错。

即支持truncate操作：源端执行truncate，目的端也执行一次truncate，确保两端数据一致。

6、附录：报错信息

2013-03-01 09:12:24  WARNING OGG-01425  Missing context item number 2, 'ErrSQLErrorCode', for message issued from line 15544 of '/mn

t/ecloud/workspace/Build_OpenSys_r11.1.1.0.0_078_[37283]/perforce/src/app/er/rep.c'.

2013-03-01 09:12:24  WARNING OGG-01425  Missing context item number 3, 'ErrSQLErrorText', for message issued from line 15544 of '/mn

t/ecloud/workspace/Build_OpenSys_r11.1.1.0.0_078_[37283]/perforce/src/app/er/rep.c'.

2013-03-01 09:12:24  WARNING OGG-01004  Aborted grouped transaction on 'DB2INST1.TEST14', Database error 0 ().

2013-03-01 09:12:24  WARNING OGG-01003  Repositioning to rba 1479 in seqno 14.

2013-03-01 09:12:24  WARNING OGG-01151  Error mapping from DB2INST1.TEST14 to DB2INST1.TEST14.

2013-03-01 09:12:24  WARNING OGG-01003  Repositioning to rba 1479 in seqno 14.

Source Context :

  SourceModule            : [er.main]

  SourceID                : [/mnt/ecloud/workspace/Build_OpenSys_r11.1.1.0.0_078_[37283]/perforce/src/app/er/rep.c]

  SourceFunction          : [take_rep_err_action]

  SourceLine              : [15780]

  ThreadBacktrace         : [9] elements

                          : [/home/db2inst1/gg/replicat(CMessageContext::AddThreadContext()+0x26) [0x70a036]]

                          : [/home/db2inst1/gg/replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0

x700ad2]]

                          : [/home/db2inst1/gg/replicat(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, DBString<777> const&, DBStrin

g<777> const&, CMessageFactory::MessageDisposition)+0x9b) [0x6ac89b]]

                          : [/home/db2inst1/gg/replicat() [0x550e93]]

                          : [/home/db2inst1/gg/replicat() [0x5557d7]]

                          : [/home/db2inst1/gg/replicat() [0x559eaa]]

                          : [/home/db2inst1/gg/replicat(main+0xe9c) [0x55bedc]]

                          : [/lib64/libc.so.6(__libc_start_main+0xe6) [0x7facd9836c36]]

                          : [/home/db2inst1/gg/replicat(__gxx_personality_v0+0x19a) [0x4ce81a]]

2013-03-01 09:12:24  ERROR   OGG-01296  Error mapping from DB2INST1.TEST14 to DB2INST1.TEST14.