How does compare /repair work? (4219995)

How does compare /repair work?

When you issue a compare /repair command from sp_ctrl on the source system for a table in Shareplex replication, A snapshot of the table is created on the source system and a marker is inserted into the data stream. When this marker gets to the target is either locks the target table [REPAIR OPTION] or creates a snapshot [COMPARE OPTION]. The snapshots/tables are then ordered by the key for the table and a checksum for the number of rows in the batch size (default 10,000 rows) is calculated, if the checksums are the same then next batch size of rows are processes. If the checksums are different the c/r process will compare each individual row. After finishing the compare on each row in the batch, a new checksum is calculated on the next batch of rows.

The compare repair option issues the following corrective SQL statements:

• If a row exists on the source side but not the target side, SharePlex issues an INSERT statement.

• If a row exists on the target side but not the source side, SharePlex issues a DELETE statement.

• If a target row differs from the source row, and the key columns match, SharePlex issues an UPDATE statement based on the source values.

• If you are using the [repair] option for a partitioned table, the default behavior of the repair process is to repair rows by using INSERTs and DELETEs only.

Repairs requiring UPDATEs are converted to a DELETE followed by an INSERT to prevent errors should an UPDATE cause a row to change partitions and row movement is not enabled for the table.

Since the target table is locked for the duration of the compare when a repair option is specified, consideration should me made to run compares with the repair option that will not effect your business priorities. Compare can be broken up into portions using a where clause on the key columns for a table e.g. the AP table has a primary key on the account column

compare table owner.tablename to owner.tablename target SysB@o.sid where 'where clause' repair

You can change the repair behavior so that SharePlex repairs partitioned tables by using INSERTs, UPDATEs, and DELETEs as appropriate. For more information, see the SP_DEQ_PART_TABLE_UPDATE parameter below:

SP_DEQ_PART_TABLE_UPDATE

This parameter affects the repair behavior of the compare table and compare config commands when they are issued for partitioned target tables.

• When this parameter is set to the default of 0, partitioned target tables are repaired using INSERTs and DELETEs only. Repairs requiring UPDATEs are converted to a DELETE followed by an INSERT to prevent errors when an UPDATE could cause a row to change partitions and row movement is not enabled for the table.

• When this parameter is set to 1, partitioned tables are repaired using INSERTs, UPDATEs, and DELETEs as appropriate. Use this mode only when you know UPDATEs will not result in a row changing partitions in the target table or when row movement is enabled for the target table.

Set this parameter on the target system.

Default: 0 (do not repair with UPDATEs)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately available for the next comparison