Full load warning message

[Ole_Dufour]

Subsequent to executing a full load task, the following warning message appears in our Qlik Replicate log:

00270041: 2024-04-30T16:56:43 [ASSERTION ]W: 'LOAD_DATA (33)' event of table 'SIEBEL'.'S_ORG_EXT' with id '2' does not contain all key values, stream position '(null)' (streamcomponent.c:2989)

00270041: 2024-04-30T16:56:43 [ASSERTION ]W: Please use the ASSERTION logger in the verbose mode to log all possible problematic events of all captured tables (streamcomponent.c:2990)

Find attached the verbose log file
What could be the issue?
Thank you very much.

[john_wang]

Hello @Ole_Dufour ,

There is no such warning information included in the uploaded task log file.

We got a similar discussion before, ASSERTION meaning , it seems a supplemental logging setting issue, I'd like to suggest checking if the PK or Unique Index columns were added into supplemental logging correctly.

However we also noticed the line in the task log file:

2024-04-30T09:07:27:690825 [TASK_MANAGER ]I: Task 'BPU_PERSONNE_MORALE_FULL_LOAD' running full load only in fresh start mode

in this scenario it's irrelative to supplemental logging. Please make sure you uploaded the correct task log file.

Regards,

John.

[SushilKumar]

Hello Team,

Thanks for reaching out to Qlik via Qlik Community Support page. Such issue require through analysis of Task Settings, hence request you to reach out to technical Support via a case and provide Diagnostics package .

Please refer to Replicate https://community.qlik.com/t5/Knowledge/How-to-collect-Diagnostics-Package-from-Qlik-Replicate/ta-p/...

Regards,

Sushil Kumar

[Ole_Dufour]

Hello John,

Please correct me if I'm wrong but the file I attached contains a lot of these lines, see attached screen print. For instance line 1352.

I'm attaching the log file again.

Thank you very much,

Ole Dufour

[DesmondWOO]

Hi @Ole_Dufour ,

In general, "does not contain all key values" message occurs during CDC. However, you hit this message during a full load only task. 

According to your provided task log, Replicate issued following query statement:

SELECT "CREATED","ACCNT_FLG","INT_ORG_FLG","NAME","PRTNR_FLG","INVSTR_FLG","CUST_STAT_CD","INTEGRATION_ID","OU_NUM","OU_TYPE_CD" FROM "SIEBEL"."S_ORG_EXT"

Can you run above statement on your Oracle DB successfully?
 
It appears that you have defined transformation and filter rules in the task. I don't have Siebel CRM environment, so I am unsure whether some columns might be related to other tables. Can you try full load again without defining transformation? 

If your SQL statement works successfully, you may also try the Full Load Passthru feature. 

To investigate this issue, we need to check your task setting and DDL of your table. I would recommend creating a support ticket if problem persists.

Regards,
Desmond

[DesmondWOO]

Hi @Ole_Dufour ,

Regarding Passthru feature, please check this post: Enable Passthrough Filter on Replicate.

Regards,
Desmond

[john_wang · Accepted solution]

Hello @Ole_Dufour ,

Besides @DesmondWOO comment, please compare the PK and/or Unique Index between the source and target sides tables,

in source: 'SIEBEL'.'S_ORG_EXT'

in target: 'BPUBASIC'.'S_ORG_EXT'

Regards,

John.

[john_wang · Accepted solution]

Hello @Ole_Dufour ,

From task log file lines #539-#543, we see the source table has 5 Unique Indexes:

Unique index S_ORG_EXT_P1 (ROW_ID)

Unique index S_ORG_EXT_U1 (NAME,LOC,BU_ID,CONFLICT_ID)

Unique index S_ORG_EXT_U2 (BU_ID,NAME,LOC,CONFLICT_ID)

Unique index S_ORG_EXT_U3 (PAR_ROW_ID)

Unique index S_ORG_EXT_V4 (PR_POSTN_ID,ROW_ID)

By default Qlik Replicate will use the first Unique Index (according to index name alphabetical order), so far Unique Index S_ORG_EXT_P1 will be used , the column name is ROW_ID :

Seems you marked another index as PK in the transformation and some columns were removed, Qlik Replicate composed the unload SQL as below, see line #1342:

2024-04-30T09:07:28:475546 [SOURCE_UNLOAD ]T: Select statement for UNLOAD is 'SELECT "CREATED","ACCNT_FLG","INT_ORG_FLG","NAME","PRTNR_FLG","INVSTR_FLG","CUST_STAT_CD","INTEGRATION_ID","OU_NUM","OU_TYPE_CD" FROM "SIEBEL"."S_ORG_EXT"'

where the column ROW_ID was NOT included in the SQL. Looks to me this is the root cause.

Would you please try to:

1- Use the default PK in transformation, or

2- at least include column ROW_ID in the replication columns list

In short, let Replicate create the target table table to confirm this is the reason.

Hope this helps,

John.

[Ole_Dufour]

Executing :

SELECT "CREATED","ACCNT_FLG","INT_ORG_FLG","NAME","PRTNR_FLG","INVSTR_FLG","CUST_STAT_CD","INTEGRATION_ID","OU_NUM","OU_TYPE_CD" FROM "SIEBEL"."S_ORG_EXT"

worked successfully. 

We will look into the Full Load Passthru feature.
Thank you.

[Ole_Dufour]

Hi John,

adding the ROW_ID from the source table S_ORG_EXT to the transformations did the trick.
The warning disappeared and the replication result stayed the same. 
This is for me the accepted solution.
Thank you !

Ole Dufour