Starting with SharePlex 8.6.3 and up, the repair command now has a new option “insertonly”. This article illustrates the functioning of this option.
As the name of the option implies, the option “insertonly” will only add the missing rows to the target by way of INSERTs when running repair on a table. It will not remove extra rows from the target by way of DELETEs, nor will it alter any rows on target by way of UPDATEs to bring them in sync with the source rows. The most common application of this option is to fix the target table when a user process or application removed rows from the target, erroneously or otherwise. The target table may or may not be in sync with the source table as no attempt is made to apply any updates or deletes on the target in case of any discrepancies.
A simple test shown below illustrates the point:
Initially the source and target tables have the rows as follows:
Source:
SQL> select * from sha86.table1;
NUM_COL
----------
1
2
3
4
Target:
SQL> select * from sha86.table1;
NUM_COL
----------
5
2
3
The repair is run as follows:
sp_ctrl (alvsupu15:5438)> repair sha86.table1 insertonly
Once repair finishes, the target will show the following:
SQL> select * from sha86.table1;
NUM_COL
----------
5
2
3
1
4
As you will note, it only applies the missing rows on the target. In our case rows with values 1 and 4 were missing so it applied them on the target table. There is an extra row on the target with a value of 5 which is not removed. It does not sync the target table in the sense that the extra rows on target are not removed. Likewise, it will not apply UPDATEs to any target rows to sync them with the source rows.
The following is a note in the .sql file which the repair created. It aptly mentions that only INSERTs will be applied:
/* Note: insert only flag is ON. UPDATE & DELETE will be skipped */
Though the .sql file goes further and lists all the DML that are needed to bring the target table in sync, in essence it only applies the INSERTs. So one should not get confused with the file and infer that it will apply other DML that are required to bring the target table in sync. Here is the remainder of our .sql file from the above repair:
/* source rowid='AAA04yAAEAAAXaDAAA' */
insert into "SHA86"."TABLE1" A (
"NUM_COL"
) values (
'1');
/*;;*/
/* source rowid='AAA04yAAEAAAXaDAAD' */
insert into "SHA86"."TABLE1" A (
"NUM_COL"
) values (
'4');
/*;;*/
delete from "SHA86"."TABLE1" where rowid = 'AAA/fcAAHAAANsUAAA';
/*
* Compare Results
*
* 4 source and 3 target rows compared successfully .
* 3 out-of-sync row(s) found and repaired.
* The table is now in sync.
* The SQL statements above were applied to bring this
* table back in sync
*
* Inserts : 2
* Updates : 0
* Deletes : 1
*
*/
Note that the table will not be removed from "show sync" list of Out of Sync tables as we do not resync the complete table with "insertonly" option.
