Source always wins

784624

Hi

We are doing the initial load through a utility and we are not sure we have all data from the source.

We want to have the following rule when the change capture happens.


1) Update of a record when the primary keys are not present in the target database -> Convert the update statment to INsert statment(Need to have all the columns)

2) Delete a record when the primary keys are not present in the target - Ignore the statement.

3) Insert of a record when the primary keys are present in the target. Overlay the records (Need to touch all columns in the target)

Handlecollisions parameter will overlay the existing records but ignores the update when the keys are not present in the target.

Appreciate your suggestions.

Thanks

-joe

Hi.

1) Use parameter INSERTMISSINGUPDATES

2) Use parameter REPERORR (1403, IGNORE)

3) Database will throw an ORA-001 error when we try to insert something already there if it has a PK or UI

Good luck,
-joe

784624

Joe

Thank you for your comments. Just a quick question

INSERTMISSINGUPDATES --This would insert all columns only if we have non-compressed update option enabled . Could we have this option only for the records which are missing in the target .Making a blanket non-compressed update capture could have a bearing on the performance.


Thanks

-joe

Hi.

INSERTMISSINGUPDATES will convert updates to inserts if the update fails with no data found (1403 in Oracle; 100 ANSI/SQL). A non-compressed update occurs on some systems such as HP Non-Stop (from which GG originated) but in Oracle (which I assume we're talking about here) non-compressed loses it's meaning. That is because Non-Stop updates log all rows regardless of what changed. Oracle only logs that which has changed (before and after images).

So in order to make Oracle log all rows regardless of what was updated you would need to add supplemental logging (add trandata in ggsci speak) to all columns. This will definitely add a lot of overhead and only in rare situations is this done to a subset of tables.

Under common conditions you'll want to make sure you sync up the tables correctly so there will be no need to do such conversions. In other situations one may want to actually convert the missing update into and insert (upsert) but here you'll want to make sure you have supplemental logging on all columns that you must have, such as target columns with NOT NULL constraints.

A more common scenario is to convert everything into and insert (INSERTALLRECORDS) to feed an audit table and add the source commit time and operation type and possible the host name or some such identifier.

Good luck,
-joe

--This would insert all columns only if we have non-compressed update option enabled . Could we have this option only for the records which are missing in the target .Making a blanket non-compressed update capture could have a bearing on the performance.