Prevent deleting archived data from the Source table

[Attunity_user]

Hello,

We set up tasks for the real-time refresh and also build historical data in the target.

If we select not to truncate the table (do nothing) on a task because of maintaining historical data in the target table that are getting purged in the source, we will never be able to choose the reload to fix a mismatch, or in some other cases or issues, the reload is an easier fix. Besides us, does anybody else follow a similar task design to prevent data from getting deleted from the source and not have an option to reload if needed?  I like to hear from others.

Thanks,

AU

[Attunity_user]

Hi Sashi,

I think if we reload, all records will be transferred and not only new records.  Because we are trying to resolve data mismatch or wanted to fix itwith an easy option of reload.

>>Based on the settings, during Full load, only the new records will be inserted as per the PK.

Below option will not be possible to go with.

>>Outside of Replicate on Target DB, we can have triggers on the table to restrict Replicate user from deleting the historical or soft deleted/changed records (have to maintain unique flag to identify those) and also can restrict truncate permission on the table. 

Thanks

[shashi_holla]

If we choose Do nothing in the Full Load settings, only the new data should be inserted and it's mentioned in the user guide as well:

[Attunity_user]

Hi Lyka,

If we filter on a date to reload for some older dates, will it interrupt the CDC post reload  because of choosing only the selected dates from older months to refill the missing records or can it resume from the last timestamp?

<< If you are already using the MERGE and UPSERT option then you should not get an constraint error for duplicates or missing rows

Shouldn’t the error handling apply only to CDC and if we do the full load, the merge/upsert will not apply?

Thanks

[Attunity_user]

Hi,

Only the new meaning newly created record or an older record missing in the target?  I will test it again because the last time this option was tried (Do nothing in the Full Load settings) and with that setting, the missing record did not insert.

Thanks

[Heinvandenheuvel]

@shashi_holla  - this is trick y wording.

"Only the new data" means ALL the currently available data in the source table.

It odes NOT mean 'all data since last full load' or similar.

That 'new' data may indeed be brand spanking new, never seen before on target, but it is more likely to be a mix of data previously loaded by full load or cdc as wel as actual new data.

Hein.

[lyka]

Hi @Attunity_user 

Please let us know if any of the suggested solution worked. If you need further assistance, you can also open a support case

Thanks

Lyka

[Attunity_user]

Hi Lyka,

Could you answer my question for the following because you mentioned "If you are already using the MERGE and UPSERT option then you should not get a constraint error for duplicates or missing rows."?

QA: Shouldn’t the error handling apply only to CDC and if we do the full load, the merge/upsert will not apply?

Thanks,

AU

[lyka]

Hi AU,

You are correct. Apply error handling will only apply to CDC.  So for full load, if you are expecting duplicates, then you have to disable the constraint

Thanks

Lyka

[Dana_Baldwin]

Hi @Attunity_user 

If any responses have solved the issue, please mark it as "accept as solution".

Thanks in advance,

Dana

[Attunity_user]

Hi Lyka,

Removing constraints will cause duplicates.   The only way to reload data when the target table has historical data is either to remove existing data from the target or rename the existing table.

Thanks,

AU