topic Re: Apply Change Settings (Ignore Drop) in Qlik Replicate

Apply Change Settings (Ignore Drop)

guilherme-matte — Tue, 18 Apr 2023 02:06:09 GMT

Hello guys

We have a case in which our source table gets dropped every once in a while and its reloaded with new records but same structure. My doubt is, if i enable the option to ignore the DROP statement in the task Change Processing options, whenever the table is recreated does CDC captures the new records from this "new" table, what would happen in this case?

Kind regards!

Re: Apply Change Settings (Ignore Drop)

SwathiPulagam — Tue, 18 Apr 2023 02:13:51 GMT

Hi @guilherme-matte ,

The recommended approach varies based on the source endpoint being used. Because for Oracle the object might get changed when the DDL is dropped at the source. So it is always recommended to do a reload when the DDL gets dropped at the source.

Thanks,

Swathi

Re: Apply Change Settings (Ignore Drop)

john_wang — Tue, 18 Apr 2023 02:42:10 GMT

Hi @guilherme-matte ,

Agree with Swathi. Besides that, the new records in the 'new' table will be captured in CDC stage as well (depends on the method how the records insert/load/reload to the source table), so:

1- if the target table does not have PK/Unique Index etc constraint, then we got these rows in target table;

2- if the target tables prevent duplicate rows (because of PK/UI constraints etc) then we got applying warning/error. In general the default behavior is recording these violation records into apply exceptions tables and going on the applying; or we may custom the error handling behavior, eg switch to UPSERT mode in this scenario etc.

Hope this helps.

Regards,

John.

Re: Apply Change Settings (Ignore Drop)

guilherme-matte — Tue, 18 Apr 2023 03:14:52 GMT

Thanks for the Reply @john_wang and @SwathiPulagam ,

But if i leave enabled the Drop Table feature, does it creates it again whenever the table is re-created at the source? Or it would be the case of reloading the whole task in this case?

Note: the database for this scenario is a SQL Server, the table does have a PK as well.

@john_wang this UPSERT switch is made through which option?

Cheers

Re: Apply Change Settings (Ignore Drop)

Heinvandenheuvel — Wed, 19 Apr 2023 02:08:26 GMT

Did you just try it? That's the best thing to start with in your own environment.

Next when the results do not match your expectation please do ask here making clear what you did, what you expected, what you saw, the replicate version/platform, the source and target DB's with version if you can).

On SQLserver you wouldn't get very far getting:

--- Cannot drop the table 'dbo.test' because it is being used for replication.

On Oracle the table object ID will change and the running task is only looking for the old ID and will not see the new change.

Now without thinking about it too much about it I expected I just needed a stop+resume, for a table dropped and recreated with ignore drop on CDC in the task. I was a little surprised that did NOT pick up the changes in a crude test. To see new changes I had to re-load loosing the old contents which the CDC 'ignore drop' had protected. I suppose I should have change my full-load setting to 'do nothing' to keep the old data if needed. Hmmm. maybe I'll try some more after a beer or two, but not now. You try!

Reload will get everything working, which is simple and safe. It may be more productive to work on the mission definition and goals than the technology: Why/Whed are tables on source re-created. What is the desired action on the target? Should/Could the replication be stopped when this is happening? Those are more business questions than Replicate question within the constraints of what Replicate can do.

hope this helps some,

Hein.