Qlik

[Neelima111]

Hello,

We have Oracle source and Snowflake as target.

DDL changes  are applied to the Oracle Source for monthly releases.

We have noticed that  the DDL changes are not migrated to snowflake in several occasions and the behavior is very inconsistent.

In the most recent release, the following DDL changes on Source Oracle have not been migrated to snowflake when the CDC task resumed.

• Add Column
• Modify PK
• Add Partition and Localize Index
• Modify Nullability

Please provide resolution for this issue to have DDL changes automictically migrate without intervention (intervention in most cases required reload target tables to capture DDL changes.)

thank you so much.

[lyka · Accepted solution]

Hello,

Replicate only supports the following DDL statements:

Supported DDL statements include:

• Create table
• Drop table
• Rename table
• Add column
• Drop column
• Rename column
• Change column data type

There are also some limitations like:

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

For a complete list please refer to:

https://help.qlik.com/en-US/replicate/May2022/Content/Replicate/Main/Endpoints/DDLStatements.htm#Sup...

Thanks

Lyka

[MdFazil]

Hello @lyka, Does these DDL only for log based CDC enabled table or for all CDC methods?

Regards
Fazil M

[SushilKumar]

Hello @MdFazil 

Seems above info is more related to Oracle endpoint. You can start the QR from the timestamp with the resume timestamp .so it will force metadata refresh which helps you to deal in most cases required reload target tables to capture DDL changes. 

And do check for Supplemental logging for the updated table as well.  if supplemental logging is enabled with all colls and you have added columns in that table you have to delete and readd the supplemental logging as QR only enforce pk Supplemental logging. 

Regards,

Sushil Kumar

[MoeE]

Hi Lyka,

Are changes to the datatype length supported? The datatype is not changed.

Regards,

Mohammed

[john_wang]

Hello Mohammed, @MoeE 

Are you talking about how to modify the target table column length? See my article: How to double the column size in the target database.

thanks,

John.

[MoeE]

Hi John,

Thanks for the response. Nope, a client is having an issue where changing the datatype length on the source is not propagated to the target table. It seems I have been able to recreate this behaviour as well. Is this expected behaviour? The source is Oracle and target is SQL server.

Regards,

Mohammed

[john_wang]

Hello Mohammed, @MoeE 

Could you please share the exact SQL statements? Please note that not all data type length changes are replicated, as far as I recall.

thanks,

John.