Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I'm currently working with Oracle 19c as a source, and I've some tables without primary keys. Need to configure a task for a full load with Change Data Capture (CDC). How should I proceed in managing the absence of primary keys in this scenario? Your guidance on handling this situation would be greatly appreciated.
Regards,
Pranita
Hello @Pranita123 ,
Thanks for reaching out to Qlik Community!
Oracle source table CDC does not demand PK unless you have LOB columns (BLOB, CLOB etc). If the table has NO PK then Replicate will add all columns to supplemental logging (by default), unless the option Automatically add supplemental logging is turn off .
In short, the table(s) should be replicated without any issue.
Hops this helps.
John.
Hello team,
If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.
Regards,
Sushil Kumar
Hi @john_wang Thank you for your response,
Automatically adding supplemental logging is enabled, but when there is an update at the source, the target_ct table is showing as an insert.
Could you please guide me on the same?
Regards,
Pranita
Hello @Pranita123 ,
Please check the source table DDL especially if any triggers defined on it. If you need help still, please open a support ticket with below information:
1- The source table creation DDL include PK/Unique Index and triggers (if have any)
2- Task Diag Packages with SOURCE_CAPTURE/TARGET_APPLY set to Verbose
3- The source table sample UPDATE SQL
thanks,
John.
Hello @john_wang ,
I made a mistake earlier; the issue isn't that updates are being treated as inserts, but rather that although we see three update counts on our console, no updates are reflected at the target.
We spoke with the DBA, who informed us that only database-level supplemental logging is enabled.
Given that the task involves tables without primary keys, do you believe enabling table-level supplemental logging could resolve this problem?
Thanks,
Pranita Patole
Hello Pranita Patole, @Pranita123
Please make sure that the required supplemental logging is added for all columns on each table. For your scenario, If there is no Primary Key and no Unique Index, supplemental logging must be added on all columns.
Hope this helps.
John.
Thank you for your response, @john_wang
However, they are not agreeing to supplemental logging due to storage concerns. Do we have any alternative ways?
Thanks,
Pranita
Hello @Pranita123 ,
Qlik Replicate operates as a log-based CDC solution, extracting data changes from transaction logs. However, ensuring seamless data integration can be challenging if any crucial information is missing from the transaction logs.
Maybe a Full Load ONLY task could potentially address the issue. This approach helps in mitigating the risk of incomplete data integration resulting from any gaps in the transaction log
Regards,
John.
Hello @Pranita123
It is always advised to have PK for the tables which are going to take part in Replication by a tool which extract SQL from Transactions logs.
As any replication tools require additional information to locate rows and columns on the target Side.
in case of oracle Supplemental logging do this. however, having PK is charry on cake. it mitigates the risk of data mismatch and make DML faster on target endpoint.
Regards,
Sushil Kumar