Post

1 follower Follow
0
Avatar

No keys on primary with duplicate rows

Question

A third party application has tables with no primary or unique keys and there are duplicate rows of data in the table. How can Replicate handle this if we want all rows updated on the target when we update all rows on source?

Answer

There is a business decision that needs to be made. If an update is done on a source table (no unique/no primary key) that updates 3 rows, is it desired to have the same change made on the target?

  • If so, then you would need to set up a conflict hander for these tables with no primary keys or unique keys.
  • If not, then these would need to be manually resolved because Replicate will see this as a conflict and pause the APPLY process until action can be taken manually.

To propagate this multiple row update for a table, the conflict handler that would be needed is below. If you have a set you can put them in a file and call that file. For example: dbvrep>@conflict_handlers

Handlers are:

dbvrep> SET_CONFLICT_HANDLERS FOR TABLE <owner.tablename> FOR DELETE ON TOO_MANY TO SQL s/$/ and rownum = 1/
dbvrep> SET_CONFLICT_HANDLERS FOR TABLE <owner.tablename> FOR UPDATE ON TOO_MANY TO SQL s/$/ and rownum = 1/
dbvrep> shutdown apply

Restart APPLY from command line on the target host.

Please also refer to the User Guide section below for more detail:

https://dbvisit.atlassian.net/wiki/display/ugd7/Configuring+conflict+handling?focusedCommentId=35061784#comment-35061784

General information on Conflict Resolution can also be found in the User Guide:

https://dbvisit.atlassian.net/wiki/display/ugd7/Conflicts+and+Conflict+Resolution

Donna Zehl Answered

Please sign in to leave a comment.