Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
VTTR
Contributor
Contributor

Matching Nullability in Target and Change Table

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)
Labels (1)
3 Replies
john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
sureshkumar
Support
Support

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
Support
Support

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





Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!