How to ignore drop statement on a source for Store Changes option?

[eksmirnova]

Hello,

We have SQL Server as a source. The replicate task is defined with stored changes and DDL option apply to the change table.  When drop statement executed on the source our target _CT table got dropped. 

How to ignore drop statement on the source with "Store Changes Processing" is On? We want to apply other DDL changes except drop statement. 

[SwathiPulagam]

Hi @eksmirnova ,

There is no such option to just ignore the drop statement. 

I encourage you to submit a feature request here if this is something you would like to see in the product: https://community.qlik.com/t5/Ideas/idb-p/qlik-ideas

Thanks,

Swathi

[shashi_holla]

Hi @eksmirnova 

As @SwathiPulagam mentioned, with the Store changes option we cannot specifically choose which DDL to ignore. But as an alternative, on the target DB we can remove the drop access/permission to the Replicate user.

Thank you,

[eksmirnova]

What will happen to the Qlik task in that case? Will it suspend that table when drop statement happened or task will fail?  

[shashi_holla]

There would be a warning on the task mentioning the drop statement failed. But the task would still be running.

[Heinvandenheuvel]

I'd consider that a bug, not a feature request. Compare the DROP to a TRUNCATE. After a Truncate the change table will simply have a 'Truncated' row. There should be a similar DDL event in the change table for DROP and CREATE. After all, those changes were not dropped. They still have happened and should stay. By contrast on a drop the base table data is gone from source and target and should be gone.  Just imagine the CT table is used for auditing. It should not be possible to 'hide' a malicious change by dropping the base table.

[I should just try but I will not just now but]  Are there truly no applicable "Store Changes Settings" in the Task Settings? Like DDL options: [Ignore], or Change Table Creation:  [Keep old changes and store new changes in existing change table]? 

At the very least the Apply Changes Settings "DDL Handling Policy" - When source table is dropped: [Ignore DROP] should be honored is it not?

From the doc : "Handling truncate operations
TRUNCATE operations will not truncate the Change Table. Instead, an additional record will be added to the
table with operation=TRUNCATE.".   Seems to me Replicate should offer something like this for drop and create.

fwiw,

Hein

[john_wang]

Hello @eksmirnova , copy @Heinvandenheuvel , @shashi_holla , @SwathiPulagam ,

Agree with Hein. May we know what's the target side DB type and version? If we we are lucky enough we may find out a workaround.

Regards,

John.

[eksmirnova]

Target is Snowflake on Azure

[john_wang · Accepted solution]

Hello @eksmirnova ,

Thanks for the update.

To be honest I totally agree with Hein, please open support ticket to get enhancement/fix. In summary, the DDL precision control in Store Changes mode can be sharper, eg distinguishing the DROP TABLE from other DDLs.

I can modify the DROP TABLE syntax in my test labs but it may introduce potential issues, it's not recommended to use it in your PROD system. The details:

1.  Add an internal parameter "$info.query_syntax.drop_table" in Snowflake target endpoint, and set its value to a dummy query eg "select 1". Sample:

      2.  Please take note after redirect the DROP TABLE SQL, it impacts the product designed behavior hence it's not recommended to use in your PROD env. The best approach is Qlik R&D provides fixes and/or enhancements for the behavior.

Best Regards,

John.