Handle Purge – Full Load + CDC from DB2 to Redshift (Implementation Questions)

Bishnu123 — Tue, 08 Jul 2025 16:13:16 GMT

Hi Team,

I’m working on a use case where I need to implement a full load followed by CDC from DB2 to Redshift, and I want to make sure we handle DB2 purges correctly while retaining historical data in Redshift.

✅ Scenario Summary

We perform a full load from DB2 table emp to Redshift table emp before the DB2 team purges older records.
After the purge, we enable CDC (insert, update, delete) to a separate Redshift table: emp_incremental.
We use a MERGE process to sync CDC changes from emp_incremental into emp, using a last_updated_dt timestamp.
Natural deletes from CDC should be processed normally (we do not retain those).
Our goal is to preserve all data that existed before purge, but allow regular CDC to continue afterward.

❓Implementation Questions

Can I implement this from a single Qlik Replicate task?
Can I map the same DB2 table (emp) twice — once for full load only to Redshift table emp, and once for CDC only to Redshift table emp_incremental?
Or is it recommended to use two separate tasks for this?
How do I configure the task(s) to:
- Perform full load only to emp (once, before purge)
- Apply only CDC (insert/update/delete) to emp_incremental (after purge)
Will Qlik capture deletes that happen during or after the purge (from DB2 logs) and apply them to emp_incremental?
How can I define and apply a last_updated_dt = GETDATE() field in both full load and CDC using the Derived Column transformation?
What are the best practices for ensuring this approach works reliably — especially for purge scenarios — without introducing soft delete columns?

Thanks in advance for your help!

Best regards,

Bishnu

Re: Handle Purge – Full Load + CDC from DB2 to Redshift (Implementation Questions)

john_wang — Wed, 09 Jul 2025 00:58:35 GMT

Hello Bishnu, @Bishnu123

Qlik Replicate supports this scenario with built-in functionality — you simply need to enable the Full Load and Store Changes options as shown below:

With these settings enabled, Qlik Replicate will:

Perform an initial load from the emp table to the target emp table
Continuously capture all INSERT/UPDATE/DELETE operations into the emp_incremental table
Optionally, you can add a last_updated_dt column using an expression like:

strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')

To sync CDC changes from emp_incremental back to emp, you can either:

Use your application logic to apply the changes, or
Create a VIEW that joins emp and emp_incremental, instead of using a MERGE operation

Regarding your questions:

A single task with straightforward settings is sufficient
See explanation above
Yes, it is supported
Addressed above
Could you clarify what you mean by the "purge" command?

Regards,

John.

topic Re: Handle Purge – Full Load + CDC from DB2 to Redshift (Implementation Questions) in Qlik Replicate

Handle Purge – Full Load + CDC from DB2 to Redshift (Implementation Questions)

✅ Scenario Summary

❓Implementation Questions

Re: Handle Purge – Full Load + CDC from DB2 to Redshift (Implementation Questions)

Regarding your questions: