Comparing odd or even numbered key value in a peer-to-peer replication environment. (4330511)

Comparing odd or even numbered key value in a peer-to-peer replication environment.

Compare needs special consideration when run in a master-master replication environment. In certain setups the trusted source (primary) may have odd numbered key values populated via sequence generator and the secondary may have even numbered key values or vice versa. Running compare on such a setup can result in the removal of rows on secondary with key values that do not match the key values from the primary. This is despite following the guidelines listed in the Reference Guide section titled Using compare config with peer-to-peer replication

Running compare in a peer-to-peer environment can result in data inconsistency unless special procedures are followed.

Compare can be run with a where clause that will only select the odd numbered key values(or even, as the case may be) when comparing the rows. For example, if the source key values always are odd numbered, then the following compare will only compare and repair the rows with odd numbered key values and leave the rows with even numbered key values intact on the target:

compare table owner.sourcetable to owner.targettable targettargetserver@o.targetsSIDwhere mod(key_column,2)=1 repair

Likewise, the following will only repair the rows with even numbered key values:

compare table owner.sourcetable to owner.targettable targettargetserver@o.targetsSIDwhere mod(key_column,2)=0 repair

Some of the other things that can be done include running compare without any repair option and then manually sifting through the .sql file generated to see if the repairs need to be applied. Moreover, it is preferable to test the compare command given above by running it first without the repair option, that too in a test environment, before deploying it in a production environment.

The conflict resolution routines will not be helpful in compare as they are only invoked by Post process which falls outside of the purview of compare.

Please also refer to Reference Guide section titled Using compare table with peer-to-peer replication which is pasted here for your reference:

Using compare table with peer-to-peer replication

To use the compare table command in a peer-to-peer replication environment, decide which system is the trusted source system the one with valid data and which is the target, or secondary, system containing data that will be repaired, if needed. Before you run the comparison, follow these steps:

1. Stop user access on the target system and wait for replicated operations to post to the source database. Users can continue to access the source database.
2. Issue the qstatus command on the source and target systems. (For more information about the qstatus command, see page 135).
3. When there are 10 or fewer messages in the queues, run the compare table command on the source system.
4. Do one of the following:
For a comparison without a repair, you can permit user access to both databases after the sp_desvr and sp_declt processes have started.
For a comparison with a repair, make the users wait until the target tables repair is finished before permitting access to that table.
Depending on the databases configuration and the way applications access them, you can use the [where clause] option to repair selected rows in a table without locking users out.