QR: RDMS Schema change with Default value

[Jon_Donker]

Hi All,

Just confirming a question and finding from our developers.

In a RDMS (in our case MS SQL and Oracle) if a table is added with a default value; the default value will "not" be replicated across because it is not written to a transaction log.

Eg:

CREATE TABLE dbo.TEST_ADD_COLUMN
(
	ACCOUNT_ID INT PRIMARY KEY,
	TEST_TXT VARCHAR(20)
);

GO

INSERT INTO dbo.TEST_ADD_COLUMN VALUES(1, 'Hello');
INSERT INTO dbo.TEST_ADD_COLUMN VALUES(2, 'World');
-- Start replication
INSERT INTO dbo.TEST_ADD_COLUMN VALUES(3, 'New Row');

---------------------------------------------------------
ALTER TABLE dbo.TEST_ADD_COLUMN
ADD NEW_COLUMN VARCHAR(20) DEFAULT 'NEW COLUMN' NOT NULL;
---------------------------------------------------------

INSERT INTO dbo.TEST_ADD_COLUMN (ACCOUNT_ID, TEST_TXT) VALUES(4, 'Another Row');

The column NEW_COLUMN and its value will not be replicated across; even with insert for ACCOUNT_ID = 4.

To get the default value across we have to do a full reload.

Is this a correct understanding?

[john_wang · Accepted solution]

Hello @Jon_Donker ,

Qlik Replicate can capture the DDL changes and apply it to target side. However some properties eg default values, in your sample 

... DEFAULT 'NEW COLUMN' ...

will not be replicated to target as part of the DDL replication. However if the values were made in a DML, eg

INSERT INTO dbo.TEST_ADD_COLUMN (ACCOUNT_ID, TEST_TXT) VALUES(4, 'Another Row');

Then the values should be replicated as part of the DML replication, if the task CDC is enabled.

Hope this helps.

John.

[Jon_Donker]

Ah yes - I had "ignore DDL changes" option turned on in the store change section.

Thanks.

I'll present back the findings to the team and see if they have any further question.

[john_wang]

Hi @Jon_Donker ,

thanks for the update - anyway, even the DDL changes are set to ignored, the DML should replicate the "data changes" (rather than "metadata changes"). Please set SOURCE_CAPTURE/TARGET_APPLY to Verbose, recreate the issue, decrypt the task log file, then we may tell more from it.

BTW, as the DDL are ignored, now seems the source and target table columns number are not equal: the new column exist in source table only but do not exist in target table so you need add the column manually then rerun the task.

thanks,

John.

[DesmondWOO]

Hi @Jon_Donker ,

I've conducted a test from SQL Server to SQL Server. "DEFAULT" in the DDL won't be replicated. Below is the create table script of the target table:



However, the default value should be replicated. Below is my test result.

Regards,
Desmond

[Heinvandenheuvel]

@Jon_Donker / @DesmondWOO  ""DEFAULT" in the DDL won't be replicated. "

Correct. And there is absolutely no reason for Replicate to do so, Just like it does not propagate additional indexes and such. It's main concern is to get the data across, and it will do that because that default value will be instantiated and physically stored on each source row where it was used and thus the data will be in the log and replicated.