Count is not matching while processing delta records from Oracle

[puja]

Hi Team,

We are facing count mismatch issues while processing delta records from Oracle table using change processing via Qlik Replicate.

any idea on this?

Thanks,

[Pedro_Lopez]

Hi Puja,

Thanks for reaching out! 

Could you let us know more about the issue? Where do you see the mismatch, in the numbers Replicate UI reports or in the count() between Source and Target tables?

Do you have any filters applied?

Send us a screenshot illustrating if possible.

Regards,
Pedro

[Michael_Litz]

Hi Puja,

One reason you may be seeing a difference in the counts between a source and target table can have to do with a large source transaction with target latency.

I have seen where a task has 20 minutes of latency and the task is processing tens of thousands changes for a table. If a record count were to be done at this time the counts would be different because not all changes (Insert's) from the source have made it to the target. 

Once the task did catch up to near zero latency the counts were in sync. 

Please let me know if this helps.

Thank you,
Michael Litz
Technical Support Engineer

[Dana_Baldwin]

Hi @puja 

Another potential reason for different record counts between source & target is when the full load occurs during change processing. Some pending changes may not be included as we only replicate committed changes to the target.

From our User Guide here:

Full Load Tuning #Full Load Tuning ‒ Qlik Replicate

• Transaction consistency timeout (seconds): Enter the number of seconds that Qlik Replicate waits for transactions to close, if they are open when the task starts, before beginning the Full Load operation. The default value is 600 (10 minutes). Qlik Replicate will begin the full load after the timeout value is reached even if there are open transactions.

  Note: To replicate transactions that were open when Full Load started but were only committed after the timeout value was reached, you need to reload the target tables.

  The task will wait for the open transactions to commit or rollback for the amount of time specified, then it will start to process the full load without these pending changes. It is a best practice to run a full load during a quiet time on the source.

I hope this helps!

Dana

[puja]

Count mismatch is between source(oracle)table and replicated table via Qlik Replicate.

It seems ,mainly this mismatch is while processing delta records using change processing via Qlik replicate.

This count mismatch is only for few tables.

[puja]

Thanks Michael

We have checked the count and found the mismatch even once the latency is zero, the following day

also, we checked the count the next day of when delta records processed.

[puja]

Thanks Dana

I could see some roll back transaction number in Change processing dashboard.

How this roll back is happening at the Qlik side if Qlik processes the committed transactions only?

Is it possible that we are getting the count mismatch due to rollback transactions?

Regards,

[john_wang]

Hello @puja ,

In general the rows number mismatch must be caused by INSERT or DELETE operations during the CDC stage, however it's irrelevant to roll back transactions as Replicate will not replicate/copy the roll back transactions, Only committed rows will be replicated.

Let's narrow down the issue by steps:

1. Confirm If the rows match after Full Load done - put the 'problematic' table in a test task with setting:

      Before cached changes have been applied

After the task stopped, check the rows number.

2. Confirm if the mismatch caused by CDC, you may enable  both of "Apply Changes Processing" and "Store Changes Processing" , then RESUME the task, after all the changed rows replicated to target side (latency is zero, or in a static status), then compare the 2 sides rows.

If you found some rows missed in the target side table, then you may search the row from the store changes table to get more detailed information include the operation (INSERT/DELETE), timestamp, and columns values to figure out why it's not applied to target table successfully.

BTW, enable source_capture/target_apply to verbose will help to understand the RCA by reading the task log file.

If we can know the target side DB/Files type then it's helpful also.

Hope this helps.

Best Regards,

John.