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

[Bishnu123]

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

1. 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?

2. 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)

3. Will Qlik capture deletes that happen during or after the purge (from DB2 logs) and apply them to emp_incremental?

4. How can I define and apply a last_updated_dt = GETDATE() field in both full load and CDC using the Derived Column transformation?

5. 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

[john_wang · Accepted solution]

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:

1. A single task with straightforward settings is sufficient

2. See explanation above

3. Yes, it is supported

4. Addressed above

5. Could you clarify what you mean by the "purge" command?

Regards,

John.