topic Addition of columns with Oracle as source in Qlik Replicate

Addition of columns with Oracle as source

chandraprakash_j_volvo — Wed, 17 Jul 2024 06:27:10 GMT

Hello,

Source: Oracle

Target: Databricks (delta)

We are currently replicating 38 tables with store changes enabled and apply changes disabled. We have records for the past 6 months.

From source there may be addition of new columns for few tables in the upcoming weeks.

1. Will this affect the affect the task or may lead to suspend the tables?

2. Is reload is necessary for the tables? Because we don't want to lose the data in the target.

3. If nothing affects what will be the value of the columns before the schema change?

Regards,

Chandraprakash J

Re: Addition of columns with Oracle as source

john_wang — Wed, 17 Jul 2024 07:26:55 GMT

Hello Chandraprakash J, @chandraprakash_j_volvo

Thanks for reaching out to Qlik Community!

By default Qlik Replicate captures DDL changes. When a new column is added to the source Oracle tables, it will also be added to the corresponding target store change table. Any values for this new column before the schema change will be NULL in the target table.

I suggest conducting an acceptance test in a lower environment to confirm this behavior.

Hope this helps.

John.

Re: Addition of columns with Oracle as source

Dana_Baldwin — Wed, 17 Jul 2024 20:50:24 GMT

Hi @chandraprakash_j_volvo

In addition to @john_wang 's reply, if you use the default clause of the add column statement on the source the column will be NULL on the target:

The DDL statement ALTER TABLE ADD/MODIFY <column> <data_type> DEFAULT <> does not replicate the default value to the target and the new/modified column is set to NULL. Note that this may happen even if the DDL that added/modified the column was executed in the past. If the new/modified column is nullable, the source endpoint updates all the table rows before logging the DDL itself. As a result, Qlik Replicate captures the changes but does not update the target. As the new/modified column is set to NULL, if the target table has no Primary Key/Unique Index, subsequent updates will generate a "zero rows affected" message."

Copied from our documentation: Supported DDL statements #Supported DDL statements | Qlik Replicate Help

The link is for our latest release at this time, 2024.5. You can change the version number at the top left of the screen for the version you are using, but this limitation is consistent across versions as it deals with how the source handles/logs the change.

Thanks,

Dana