topic Re: How to Periodically delete records from Change capture tables in Qlik Replicate

How to Periodically delete records from Change capture tables

matheb04 — Tue, 19 Apr 2022 13:56:12 GMT

What is the option available to periodically truncate / (keep n number of records) the change capture table created by Qlik replicate?

Re: How to Periodically delete records from Change capture tables

PGN — Tue, 19 Apr 2022 14:49:54 GMT

It all depends on your specific needs and how you're using them. We use an Informatica workflow to process the entries several time a day and it deletes the rows as it processes them. I believe there are date/time stamps in the rows that facilitate the filtering for selecting which rows to process. In some cases, we have workflows that process entire sets of CDC tables and then do a truncate table on them once the workflow completes.

Re: How to Periodically delete records from Change capture tables

KellyHobson — Tue, 19 Apr 2022 17:47:31 GMT

Hey @matheb04 ,

Depending on your target endpoint type, another option to consider is Change Data Partitioning.

Task Settings-> Change Processing-> Store Changes Settings -> Change Data Partitioning

In a standard replication task, changes are replicated to the target in no particular order. Change Data Partitioning enables processing of Change Data from many tables in a consistent fashion. You can define the duration of partitions as well as the partitioning base time, thereby ensuring overall consistency of the partitioned data

Additionally, you can set a partition retention time which can be triggered periodically via API call.

Ref: https://help.qlik.com/en-US/replicate/November2021/Content/Global_Common/Content/SharedEMReplicate/Customize%20Tasks/store_changes_settings.htm

scroll to "Change Data Partitioning" section for more info like the supported endpoints

Best,

Kelly

Re: How to Periodically delete records from Change capture tables

Michael_Litz — Tue, 19 Apr 2022 17:50:01 GMT

There is no built in method from replicate to manage the records in the change capture tables.

You will need to develop a process from outside of replicate.

Michael

Re: How to Periodically delete records from Change capture tables

rendyajisoko — Wed, 20 Apr 2022 02:01:24 GMT

Agreed, maybe you can make Store Procedure to monthly housekeeping your records at some tables.

Re: How to Periodically delete records from Change capture tables

lyka — Wed, 20 Apr 2022 15:55:08 GMT

Hello,

Please let us know if any of the suggested solution works for you

Thanks

Lyka