I am replicating data from AS400 to MySQL with IBM CDC Replication.
Source: AS400 (CDC Engine for DB2 for i) ==> Target: MySQL (CDC Engine for FlexRep)
Then I have created a subscription with the following table mapping as mirror replication:
source.item ==> target.item
Apparently, source.item is from AS400 and target.item is from MySQL.
target.item has unique key constraint on COL1 to COL8.
When I run this subscription, I am getting the following error:
Failed to add a row to table target.item. COL1: [IT] COL2: [2] COL3: [T999] COL4: [H203 ] COL5: [ ] COL6: [20180831] COL7: [9999999] COL8: [FPL].
Database operation failed.
A SQL exception has occured. The SQL error code is '1172'. The SQL state is: 42000. The error message is:
Result consisted of more than one row.
At first I was wondering if there are duplicated records in source table with COL1='IT' COL2='2' COL3='999' COL4='H203 ' COL5=' ' COL6='20180831' COL7='999999' COL8='FPL', since replicating duplicated key value records will violate the unique key constraint in target, but then there is only one record with such key values in the source table.
One more thing to mention is that we have a derived column expression in the table mapping:
NEW_COL ==> %GETCOL(COL_WANTED, "source.lookup", "NOT FOUND", DESCRPF, 5, COL1, COL2, COL3, COL4, COL5)
Basically we are trying to lookup the column COL_WANTED from source.lookup with matching value of COL1 to COL5.
I reckon that it is possible for this "joining/lookup" operation to yield multiple result records causing the error.
But then I have taken away this derived column expression and the same error still prompts.
Any suggestion will be greatly appreciated. Thanks in advance.
------------------------------
Kevin Lee
------------------------------
#DataIntegration#DataReplication