Column constraint change is not replicated (Oracle to MSSQL)

[martinkaru]

The replication task is working, we change a column from NOT NULL -> NULL and the task gets suspended.

The task's DDL Handling policy says: "When source table is altered: ALTER target table"

At the moment, the tool is version 2022.11.0.208, we will upgrade it next week

Qlik Replicate 

[john_wang]

Hello @martinkaru ,

Thanks for reaching out to Qlik Community!

The behavior cannot be reproduced in my labs. In my Replicate 2023.5.0.701 task "Ora-to-SQL", The Oracle source side DDL:

alter table testddl modify (name varchar(20) null);

We got below lines in task log file:

2024-02-16T23:21:02:72303 [SOURCE_CAPTURE ]V: Build DDL event with operation code 101 for table 'SCOTT.TESTDDL', part 1 from 1, SQL statement is <alter table testddl modify (name varchar(20) null)> (oradcdc_parse.c:3677)

&

2024-02-16T23:21:02:817054 [SOURCE_CAPTURE ]V: DDL for object 'SCOTT.TESTDDL' is ignored (oracle_endpoint_capture.c:2235)

In summary, Qlik Replicate will ignore this type DDL rather than get table suspended or other error status.

Please let us know what's the result after Replicate upgrade.

Thanks,

John.

[martinkaru]

Hi, @john_wang!

Thanks for the reply! We upgraded Qlik, however, the problem persisted.

More exact examples: (Oracle to Azure SQL)

---

Handling End of table 'confact'.'CF2_RELATION' loading failed by subtask 1 thread 1
Commit rows failed
RetCode: SQL_SUCCESS SqlState: 23000 NativeError: 515 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot insert the value NULL into column 'REL_DOSSIER', table 'sqldb-ingestion02.confact.CF2_RELATION'; column does not allow nulls. INSERT fails.

---

and

Table 'APPHAVRECHT'.'CF2_RELATION' (subtask 1 thread 1) is suspended.
RetCode: SQL_SUCCESS
SqlState: 23000
NativeError: 515
Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot insert the value NULL into column 'REL_DOSSIER', table 'sqldb-ingestion02.confact.CF2_RELATION'; column does not allow nulls. INSERT fails.; Commit rows failed; Handling End of table 'confact'.'CF2_RELATION' loading failed by subtask 1 thread 1

---

The DDL changes transfer is on

[Heinvandenheuvel]

>>> Cannot insert the value NULL into column 'REL_DOSSIER', table 'sqldb-ingestion02.confact.CF2_RELATION'; column does not allow nulls.

So what you are saying is that contrary to the topic title "Column constraint change is not replicated " the constraint was replicated. After that an insert failed because it (against expectations) did have a null value resulting in "Cannot insert the value NULL into column 'REL_DOSSIER', table 'sqldb-ingestion02.confact.CF2_RELATION'; column does not allow nulls". This proves that the constraint was replicated and honored. Agree?

Now I suspect that the source did NOT have a null value, but somehow the column value, thru Replicate, became null. The most likely reason for this is 'empty string handling, failing UTF converts. You may want to experiment with 'advanced' - 'internal parameters' on the Oracle source endpoints. elements like:

alwaysReplaceEmptyString                 true,
emptyStringValue                         "
emptyVarCharStringValue                  "",
onlyForVarCharReplaceEmptyString          false,

Be sure to first construct a little test with clear, known source values. Use "DUMP" on the Oracle side and HEX() or is it SELECT CAST(xxx  AS VARBINARY()) on the target size.

Hein.