topic Re: suspendTableWithComputedColumn internal parameter in Qlik Replicate

suspendTableWithComputedColumn internal parameter

iti-attunity-sup — Mon, 24 Jun 2024 08:23:21 GMT

Hello Experts,

I would like to ask some questions regarding suspendTableWithComputedColumn internal parameter.

I had understood as follows based on the descriptions of the articles(*).

In the source endpoints which do not support computed columns for CDC, such as SQL Server
setting suspendTableWithComputedColumn as true would suspend only the table with a computed column and when it is false, the whole task will stop.

(*)
https://community.qlik.com/t5/Official-Support-Articles/Replication-of-MS-SQL-tables-that-include-computed-columns/ta-p/1850667

https://community.qlik.com/t5/Qlik-Replicate/Advanced-setting-tab-for-the-Microsoft-Azure-SQL-Database-as/td-p/2067075

However as far as I have confirmed in house using MS SQL Server, no errors occur during CDC even if the table has computed columns.
As is described in the online documentation, INSERTs to computed columns will appear as NULL on the target and UPDATEs will be ignored.
The table does not get suspended nor the task does not also stop.

* I confirmed it in v2023.11.

https://help.qlik.com/en-US/replicate/November2023/Content/Replicate/Main/AWS%20RDS%20MSSQL/aws_limitations_source_SQLServerDB.htm

Replication of computed columns is supported in Full Load replication tasks only. During CDC, 
INSERTs to computed columns will appear as NULL on the target and UPDATEs will be ignored.

1. I would like to know what kind of situation setting suspendTableWithComputedColumn as true would be effective (suspend the table) in SQL Server.

2. Is it possible to detect the task contains table with computed columns in SQL Server?
I wonder we can not notice that the table has such columns because the column is inserted as NULL or updates are silently ignored without any errors.

As a background of this question, customer currently had this setting disabled.
Now the customer is trying to migrate on-premise server to RDS and rechecking the parameters.
Since it is enabled by default for MS SQL Server endpoint, we would like to determine if it is better to be enabled.

Any advice would be appreciated.

Regards,
Kyoko Tajima

Re: suspendTableWithComputedColumn internal parameter

john_wang — Wed, 26 Jun 2024 03:17:56 GMT

Hello Tajima-san, @iti-attunity-sup

Thanks for reaching out to Qlik Community!

Please allow us some time,We are going to confirm the behavior and get back to you with our findings shortly.

Regards,

John.

Re: suspendTableWithComputedColumn internal parameter

john_wang — Wed, 26 Jun 2024 11:17:48 GMT

Hello Tajima-san, @iti-attunity-sup

Per my understanding there is enhancement around computed columns replication for SQL Server source endpoint:

1- The computed column(s) values are replicated to target side during Full Load stage;

2- During CDC, INSERTs to computed columns will appear as NULL on the target and UPDATEs will be ignored;

3- Looks to me the internal parameter suspendTableWithComputedColumn is deprecated in 2023.11 newer builds and 2024.5 GA. Its value (true or false) does not impact the task behavior.

4- No warning No error presents upon source side tables INSERT/UPDATE/DELETE operations even the table has computed column(s), all works smoothly in Qlik Replicate 2023.11/2024.5.

5- The below limitation applies to Qlik Replicate 2022.11 and lower versions: During change processing, any tables with computed columns will be suspended.

Replication of computed columns is supported in Full Load replication tasks only. During CDC, 
INSERTs to computed columns will appear as NULL on the target and UPDATEs will be ignored.

From Qlik Replicate 2023.5 and up the above limitation was removed.

BTW, it's easy to find out all the computed columns in a database. A sample of the SQL:

SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
c.name AS column_name,
OBJECT_NAME(c.object_id) AS table_name,
TYPE_NAME(user_type_id) AS data_type,
definition
FROM sys.computed_columns c
JOIN sys.objects o ON o.object_id = c.object_id
ORDER BY schema_name,
table_name,
column_id;

Hope this helps.

John.