Matching Nullability in Target and Change Table

[VTTR]

Hello Qlik Community,

We are currently trying to replicate various tables from a DB2-ZOS to a DB2-LUW using Qlik Replicate, and we have noticed that the nullability in the target table and the corresponding __ct table are different. Are there ways to keep these consistent?

some snippets of the corresponding log:

Spoiler

00008364: 2024-09-13T09:17:21 [TARGET_LOAD ]T: Create table statement: CREATE TABLE "POC_CDC_QLIK"."NULLTEST_TAANSAKT__ct" ( "header__change_seq" nvarchar(35) NOT NULL, "header__change_oper" nvarchar(1) NOT NULL, "header__change_mask" VARBINARY(128), "header__stream_position" nvarchar(128) NOT NULL, "header__operation" nvarchar(12) NOT NULL, "header__transaction_id" nvarchar(32) NOT NULL, "header__timestamp" TIMESTAMP NOT NULL, "ANSAKT_VNR" INTEGER, "ANSAKT_PB" nvarchar(1), "ANSAKT_SP" SMALLINT, "ANSAKT_GEVO_ID" TIMESTAMP, "ANSAKT_PA_ID" INTEGER, "ANSAKT_GES" nvarchar(3), "ANSAKT_SG" nvarchar(3), "ANSAKT_AKTION" nvarchar(10), "ANSAKT_ANS_DAT" DATE, "ANSAKT_A_KZ" nvarchar(1), "ANSAKT_G_KZ" nvarchar(1), "ANSAKT_CODE" nvarchar(20), "ANSAKT_ZAE_1" SMALLINT, "ANSAKT_ZAE_2" SMALLINT, "ANSAKT_ZAE_3" SMALLINT, "ANSAKT_ZAE_4" SMALLINT, "ANSAKT_E_STAMP" TIMESTAMP, "ANSAKT_L_STAMP" TIMESTAMP, "ANSAKT_RC" SMALLINT, "ANSAKT_WERTE_1" nvarchar(100), "ANSAKT_WERTE_2" nvarchar(100), "ANSAKT_ERIN_REL" nvarchar(1) ) (provider_syntax_manager.c:1815)

Spoiler

00008364: 2024-09-13T09:17:23 [TARGET_LOAD ]T: Create table statement: CREATE TABLE "POC_CDC_QLIK"."NULLTEST_TAANSAKT" ( "ANSAKT_VNR" INTEGER NOT NULL, "ANSAKT_PB" nvarchar(1) NOT NULL, "ANSAKT_SP" SMALLINT NOT NULL, "ANSAKT_GEVO_ID" TIMESTAMP NOT NULL, "ANSAKT_PA_ID" INTEGER NOT NULL, "ANSAKT_GES" nvarchar(3) NOT NULL, "ANSAKT_SG" nvarchar(3) NOT NULL, "ANSAKT_AKTION" nvarchar(10) NOT NULL, "ANSAKT_ANS_DAT" DATE NOT NULL, "ANSAKT_A_KZ" nvarchar(1) NOT NULL, "ANSAKT_G_KZ" nvarchar(1) NOT NULL, "ANSAKT_CODE" nvarchar(20) NOT NULL, "ANSAKT_ZAE_1" SMALLINT, "ANSAKT_ZAE_2" SMALLINT, "ANSAKT_ZAE_3" SMALLINT, "ANSAKT_ZAE_4" SMALLINT, "ANSAKT_E_STAMP" TIMESTAMP, "ANSAKT_L_STAMP" TIMESTAMP, "ANSAKT_RC" SMALLINT NOT NULL, "ANSAKT_WERTE_1" nvarchar(100) NOT NULL, "ANSAKT_WERTE_2" nvarchar(100) NOT NULL, "ANSAKT_ERIN_REL" nvarchar(1) NOT NULL ) (provider_syntax_manager.c:1815)

[john_wang]

Hello @VTTR ,

Welcome to Qlik Community forum and thanks for reaching out here!

In DB2 for z/OS (DB2z) or DB2 for Linux, UNIX, and Windows (DB2 LUW), the nullability of columns in the target table should match the nullability of columns in the source table. However, in the corresponding __ct table, only primary key columns, such as header__change_seq, are marked as "NOT NULL." All other columns derived from the source table (in DB2z or DB2 LUW) are set as nullable.

The reason for this is that even if a column in the source table is defined as "NOT NULL," its value may not appear in the DB2 transaction log if it hasn't been changed. Most databases omit unchanged columns from the transaction log to optimize logging. In some databases, this behavior is configurable; for instance, in Oracle, non-changed columns can be included in the REDO LOG through full supplemental logging.

Hope this helps.

John.

[sureshkumar]

Hello @VTTR 

Have you enabled both "Apply Changes" and "Store Changes" in the task?

I would suggest create a new task with same source and Target and add a problematic table into it with enhanced logs and also add "keepCSVFiles" Internal parameter if supported at the target endpoint and observe the behavior.

Regards,

Suresh

[DesmondWOO]

Hi @VTTR ,

I've also noticed that the nullability of the columns in the target table differs from that in the __ct table. The user guide mentions that 

"LOB columns are always created as nullable on the target database. If you create the target table(s) manually, then you must set all LOB columns to nullable."

Given this, I recommend submitting a support ticket.

Regards,
Desmond