DB2 DDL操作引起的GoldenGate错误
源端：
进程：rtop1、ptop1(pump)
数据：
D:\>db2 select * from topicis.bb
ID
-----------
1
2
3
3 条记录已选择。
目标端：
进程：rtop1
数据：
[topicis@localhost ~]$ db2 "select * from topicis.bb"
ID
-----------
2
3
1
3 record(s) selected.
修改源端表结构：
D:\>db2 alter table topicis.bb add column name varchar(10)
DB20000I SQL 命令成功完成。
D:\>db2 select * from topicis.bb
ID NAME
----------- ----------
1 -
2 -
3 -
3 条记录已选择。
这里，extract进程没有报错。对于DB2，GoldenGate不支持DDL语句。Replicat进程也没有报错。
下面执行DML操作：
D:\>db2 update topicis.bb set name='a' where id=1
DB20000I SQL 命令成功完成。
D:\>db2 select * from topicis.bb
ID NAME
----------- ----------
1 a
2 -
3 -
3 条记录已选择。
这样，源端没有报错，目标端产生了错误：
2013-02-04 10:23:30 ERROR OGG-01168 Encountered an update for target table TOPICIS.BB, which has no unique key defined. KEYCOLS can be used to define a key. Use ALLOWNOOPUPDATES to process the update without applying it to the target database. Use APPLYNOOPUPDATES to force the update to be applied using all columns in both the SET and WHERE clause.
提示使用ALLOWNOOPUPDATES或APPLYNOOPUPDATES参数，下面看两个参数的作用：
ALLOWNOOPUPDATES：control how Replicat responds to a “no-op” operation.
什么是”no-op” operation操作呢？
A no-op operation is one in which there is no effect on the target table.For example:
l The source table has a column that does not exist in the ta rget table, or has a column that was excluded from replication (with a COLSEXCEPT clause). In either case, if that source column is updated, there will be no target column name to use in the SET clause within the Replicat SQL statement.
l An update is made that sets a column to the same value as the current one. The database does not log the new value, because it did not really change. However, Oracle GoldenGate extracts the operation as a change record because the primary key was logged — but there is no column value for the SET clause in the Replicat SQL statement.
我这里就是第一种情况，源端topicis.aa表有name列，而目标端没有，当修改name列时，目标端找不到该列，就认为是”no-op” operations.
With ALLOWNOOPUPDATES, Replicat ignores the operation instead of abending. The statistics reported by Replicat will show that an update was made, but the database will not be updated.
ALLOWNOOPUPDATES参数的作用是将这些”no-op”操作忽略，从而使replicat进程不abend。
APPLYNOOPUPDATES:
Use APPLYNOOPUPDATES to force a “no-op” update to be applied using all columns in both the SET and WHERE clauses.
APPLYNOOPUPDATES uses whatever data is in the trail. If there is a primary key update record, it uses the before columns from the source. If there is a regular (non-key) update, it assumes that the after value is the same as the before value (otherwise it would be a primary key update).
下面分别试一下这两个参数：
GGSCI (localhost.localdomain) 18> edit params rtop1
--Control Replicat to ignores the "no-op" operations instead of abending
ALLOWNOOPUPDATES
GGSCI (localhost.localdomain) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RTOP1 00:00:00 00:00:01
GGSCI (localhost.localdomain) 24> stats rtop1
*** Latest statistics since 2013-02-04 10:56:08 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
“The statistics reported by Replicat will show that an update was made, but the database will not be updated.”
实际上表没有被update：
[topicis@localhost ~]$ db2 "select * from topicis.bb"
ID
-----------
2
3
1
3 record(s) selected.
下面测试另外一个参数APPLYNOOPUPDATES:
GGSCI (localhost.localdomain) 26> stop rtop1
GGSCI (localhost.localdomain) 27> edit params rtop1
--Control Replicat to ignores the "no-op" operations instead of abending
--ALLOWNOOPUPDATES
--Control Replicat to force "no-op" operations to be applied using all columns in both the set and where clause
APPLYNOOPUPDATES
GGSCI (localhost.localdomain) 29> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RTOP1 00:00:23 00:00:01
源端操作：
D:\>db2 update topicis.bb set name='b' where id=2
DB20000I SQL 命令成功完成。
GGSCI (localhost.localdomain) 31> stats rtop1
*** Latest statistics since 2013-02-04 11:04:57 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
[topicis@localhost ~]$ db2 "select * from topicis.bb"
ID
-----------
2
3
1
3 record(s) selected.
两个参数的效果是一样的。
但是这两个参数只能解决Replicat进程不处于abend状态。两边的表结构一直这样处于不一致状态，这样肯定不行。由于GoldenGate目前对于DB2不支持DDL，所以解决问题的根本办法就是让目标端表结构和源端表结构一致。
方法是先在源端extract进程中指定TABLEEXCLUDE参数，从”TABLE *”语句中除去topicis.bb表。然后使该表在两端一致化后，再将表添加进来。
GGSCI (localhost.localdomain) 36> stop rtop1
Sending STOP request to REPLICAT RTOP1 ...
Request processed.
GGSCI (localhost.localdomain) 37> edit params rtop1
--APPLYNOOPUPDATES
源端：
GGSCI (liuxiaohui) 108> stop *top1
Sending STOP request to EXTRACT ETOP1 ...
Request processed.
Sending STOP request to EXTRACT PTOP1 ...
Request processed.
GGSCI (liuxiaohui) 109> edit params etop1
--Explicitly exclude tables from a wildcard specification
TABLEEXCLUDE topicis.bb
--Specify tables to be captured
TABLE topicis.*;
这里，测试环境中可以直接停掉extract和replicat进程，对表进行同步后再开启。然而，在生产环境中，对于一个较大的表，同步过程需要一定的时间，不可能等同步完成后再启动进程。所以必须先添加上TABLEEXCLUDE参数，启动两端进程，这样，同步过程影响到的只是这一张表。
GGSCI (liuxiaohui) 110> start *top1
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT ETOP1 starting
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT PTOP1 starting
GGSCI (liuxiaohui) 114> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ETOP1 00:00:00 00:00:02
EXTRACT RUNNING PTOP1 00:00:00 00:00:09
对bb表进行同步。我这里直接给目标端bb表添加name列。生产环境中，不知道对表具体有哪些DDL操作，而且DDL操作后也会有很多DML操作，所以必须停止该表的活动后将表导到目标端。
[topicis@localhost ~]$ db2 "alter table topicis.bb add column name varchar(10)"
DB20000I The SQL command completed successfully.
然后就可以将TABLEEXCLUDE参数去掉了。
GGSCI (liuxiaohui) 118> edit params etop1
--TABLEEXCLUDE topicis.bb
GGSCI (liuxiaohui) 120> start *top1
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT ETOP1 starting
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT PTOP1 starting
GGSCI (liuxiaohui) 121> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ETOP1 00:00:00 00:00:02
EXTRACT RUNNING PTOP1 00:00:00 00:00:49
目标端：
GGSCI (localhost.localdomain) 40> start rtop1
Sending START request to MANAGER ...
REPLICAT RTOP1 starting
GGSCI (localhost.localdomain) 41> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RTOP1 00:00:00 00:00:01
测试一下再对源端操作：
D:\>db2 update topicis.bb set name='c'
DB20000I SQL 命令成功完成。
D:\>db2 select * from topicis.bb
ID NAME
----------- ----------
1 c
2 c
3 c
3 条记录已选择。
发现数据没同步过去，extract进程abend了。
GGSCI (liuxiaohui) 123> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED ETOP1 00:00:00 00:00:35
EXTRACT RUNNING PTOP1 00:00:00 00:00:00
看一下report：
GGSCI (liuxiaohui) 124> view report etop1
2013-02-04 11:34:13 ERROR OGG-00823 A key column on the after image was not present in the before image on table TOPICIS.BB column index 1 This is because column(s) were added to the table after it was originally created. This is a known issue with DB2 LUW that can only be addressed by IBM.
2013-02-04 11:34:14 ERROR OGG-01668 PROCESS ABENDING.
错误提示：在TOPICIS.BB表上，由于在初始化创建后添加了列，导致在after image的键列在before image中不存在。
发出如下命令：
GGSCI (liuxiaohui) 130> alter extract etop1 begin now
EXTRACT altered.
GGSCI (liuxiaohui) 131> start etop1
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT ETOP1 starting
GGSCI (liuxiaohui) 132> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ETOP1 00:00:00 00:00:06
EXTRACT RUNNING PTOP1 00:00:00 00:00:07
修改进程后可以启动。
再次发出update命令：
D:\>db2 update topicis.bb set name='zz'
DB20000I SQL 命令成功完成。
GGSCI (liuxiaohui) 180> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ETOP1 00:00:00 00:00:06
EXTRACT RUNNING PTOP1 00:00:00 00:00:06
Extract进程没有abend。查看etop1和ptop1进程，发现操作被进程传递到了目标端。但是目标端就是没有数据，进程也没有报错，report也没有记录任何信息：
[topicis@localhost ~]$ db2 "select * from topicis.bb"
ID NAME
----------- ----------
2 -
3 -
1 -
3 record(s) selected.
没有同步到目标端，而且进程竟然也没有报错。那就说明一定是进程做了某些处理。原来是参数文件中指定了REPERROR(100,DISCARD).
查看一下discard文件：
[topicis@localhost ggs]$ more ./dirrpt/rtop1.dsc
有类似如下内容：
Current time: 2013-02-04 13:01:39
[SQL error 100][IBM][CLI Driver][DB2/LINUXX8664] SQL0100W No row was f
ound for FETCH, UPDATE or DELETE; or the result of a query is an empty
table. SQLSTATE=02000
Operation failed at seqno 13 rba 2148
Discarding record on action DISCARD on error 100
Problem replicating TOPICIS.BB to TOPICIS.BB
Record not found
Mapping problem with compressed key update record (target format)...
*
ID = 1
NAME = a
ID = 1
NAME = zz
说明gg还是认为两端表结构不一致。
将目标端bb表中数据删除，添加初始化进程进行初始化后，同步正常。
D:\>db2 update topicis.bb set name='keeptrying'
DB20000I SQL 命令成功完成。
[topicis@localhost ~]$ db2 "select * from topicis.bb"
ID NAME
----------- ----------
1 keeptrying
2 keeptrying
3 keeptrying
3 record(s) selected.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25744374/viewspace-753898/ ，如需转载，请注明出处，否则将追究法律责任。
