Lots of ERP tables with no primary keys.

[numbrkar]

I’m currently tasked with replicating over all of our erp tables to a staging area using Qlik replicate for cdc. Unfortunately replicate has a huge latency when you use tables with no primary keys, what is the common solution here? Do I use another way of replication or do I assign and create a PK for every table(132tables)?

[DesmondWOO]

Hi @numbrkar ,

Thank you for reaching out to the Qlik Community.

Without access to the task log, I'm unsure whether the latency is occurring on the source side or the target side. If your target table does not have a primary key, the performance will indeed be especially impacted during updates and deletes. Additionally, batch optimized apply cannot function without primary key.

So, if possible, please add a primary key for each table. If your source database is DB2 iSeries, you can use the Record Relative Number (RRN) as a workaround in the absence of a primary key. You may also check with our professional service team for further advice.

Regards,
Desmond

[vinayak_m]

Hi @numbrkar ,

As per my knowledge, there are a couple of ways to approach this problem.

1. Connect with the Database team and identify if the Primary key can be assigned to these tables, which would allow you to see at least some level of performance improvement in your tasks.
   
   
2. Since all your tables have no primary keys, there is no reason to collectively run them in a single task, you can create different batches and run them in parallel/ run in a single task with all 132 tables running in parallel at once, this is something which you would have to test out and see what approach is working for you better.
   
   
3. You can create a composite primary key yourself, it's a tedious approach but you can do it on your own without involving the DB team and then observe a improvement in the latency.
   
   Thanks,
   Vinayak

[narendersarva]

Hi @numbrkar 

When dealing with Change Data Capture (CDC) using Qlik Replicate, the presence of primary keys (PKs) is crucial for efficiently identifying and replicating changes. The latency issues you're experiencing are likely due to the difficulty in tracking changes without PKs.

Please coordinate with your DBA on adding PK's to these tables and check the impact.

Thanks
Naren