topic Managing Mixed Supplemental Logging Requirements with "Retrieve All Source Columns on UPDATE" in Qlik Replicate

Managing Mixed Supplemental Logging Requirements with "Retrieve All Source Columns on UPDATE"

ibrahim_sandalli — Fri, 17 Jan 2025 08:59:50 GMT

Hi,

Our replication process involves hundreds of Oracle source database tables. To address varying business needs, we’ve consolidated all replication tables into a single logstream task and created downstream tasks that feed off this logstream. Supplemental logging for source tables is manually managed based on individual requirements.

For 90% of these tables, supplemental logging on PK is sufficient. However, some tasks require an UPSERT configuration, necessitating supplemental logging on all columns for their associated tables.

The challenge arises with the introduction of the "Retrieve All Source Columns on UPDATE" feature. If even a single table requires an UPSERT configuration and we have a single UPSERT task, the logstream task mandates enabling "Retrieve All Source Columns on UPDATE" . This enforces all column supplemental logging on every table in the logstream task. Consequently, when reloading the logstream task or adding new tables to this logstream task, Qlik Replicate requires all column supplemental logging.

This poses issues because DBAs are hesitant to enable all column supplemental logging for all tables due to potential impacts and the lack of necessity for most tables. Convincing them to apply this for all tables is challenging, especially when only a few genuinely require it.

Do we have any option or workaround to manage this situation? For now, the only solution seems like creating two logstream tasks which will increase the complexity and may affect source capture performance.

Kind Regards,

Ibrahim

Re: Managing Mixed Supplemental Logging Requirements with "Retrieve All Source Columns on UPDATE"

DesmondWOO — Thu, 23 Jan 2025 08:19:25 GMT

Hi @ibrahim_sandalli ,

Thank you for reaching out to the Qlik Community.

I'm afraid you'll need to create two LogStream tasks for your scenario. As you mentioned earlier, enabling 'Retrieve All Source Columns on UPDATE' necessitates 'ALL COLUMN logging' on the source tables.

However, the replication task will verify this option when UPSERT mode is enabled. If you disable it, the replication task will treat it as a fatal error and stop.

Regards,
Desmond

Re: Managing Mixed Supplemental Logging Requirements with "Retrieve All Source Columns on UPDATE"

kutay_cilingiroglu — Tue, 28 Jan 2025 07:49:14 GMT

Hi @DesmondWOO ,

In the May and November 2023 versions, the LogStream task could run without making any changes to the supplemental logging level of unnecessary tables. What prompted the decision to implement this design change?

This decision significantly reduces the usability advantage of LogStream and complicates its management. In large systems, DBAs do not typically find it acceptable to enable supplemental logging for all tables.

Best Regards,

Kutay

Re: Managing Mixed Supplemental Logging Requirements with "Retrieve All Source Columns on UPDATE"

SushilKumar — Tue, 28 Jan 2025 11:06:08 GMT

Hello @kutay_cilingiroglu

Supplemental logging enablement can be passed on to QR USER if necessary Privileges are assigned.

if tables have a Primary key then it enables the PK and works accordingly. All Columns are required when participating tables don't have PK in such cases all column logging is used to create a composite key which helps QR locate the row or columns to the Target endpoint to perform DMLS.

As per Industry if the customer is going to Replicate more than 60% of user tables then they Should enable Supplemental logging at the Scheme level. Or if it's less than that the individual tables supplemental logging is required.

Supplemental logging helps to have additional info to perform Delete and Update where pre and post-image of block required. normal insert work without Supplemental logging.

and it's a CDC requirement. Hope your DBA Support your data integration Solution.

Regards,
Sushil Kumar

Re: Managing Mixed Supplemental Logging Requirements with "Retrieve All Source Columns on UPDATE"

kutay_cilingiroglu — Tue, 28 Jan 2025 11:49:27 GMT

Hello @SushilKumar,

Thank you very much for the detailed information. Here, we are referring to a different requirement. Recently, there has been a change in the behavior of the LogStream task connected to Oracle, which was functioning quite reasonably before.

For instance, imagine a LogStream task with 100 tables and two tasks connected to it. Let’s assume the first task replicates 99 tables, while the second one replicates only 1 table. If the second task operates in UPSERT mode, supplemental logging needs to be enabled for all columns across all 100 tables in the LogStream task. This behavior was introduced with the May 2024 version, and we believe understanding the rationale behind this change would be beneficial.

Best regards,

Kutay