How to add RRN to specific table.

[iti-attunity-sup]

Hello support team.

We would like to add RRN to specifc table which has unique index.
We understand "Add RRN column to all target tables" option,
but we wolud like to add RRN to specifc table not all tables.(not to separate task)

So, would you tell me how to add RRN to specific table.

[INFO]
Source : DB2 for iSeries

Best Regards.

[john_wang]

Hello @iti-attunity-sup ,

You can follow the steps below to add an RRN column for a specific table:

1. In the endpoint settings, select “Do not add RRN column.”
   

    

2. In Transformations, add an additional column and name it RRN 

   

3. Define the RRN expression accordingly. For example:

   source_lookup('NO_CACHING','APSUPDB','KIT','RRN("APSUPDB"."KIT")','ID=?',$ID)

   

   where "ID" is the Unique Index of the source table "KIT" in database "APSUPDB".

Hope this helps.

John.

[iti-attunity-sup]

Hello John.

Thank you for your reply.

Additionally, would you tell me following ?

1. By this setting, does Qlik Replicate use RRN in CDC ? (replicate ui table by using RRN)

    Because source table unique index is using where phrase, unique index is not unique entire table.

2. Although I mentioned specific table, I would also like to add RRN to no pk/ui tables. (not only specific table)
Is this setting effective with set "Add RRN column to tables without a Primary key or Unique Index" ?

Best Regards.

[john_wang]

Hello @iti-attunity-sup ,

1. Yes, of course.
2. Not exactly. If the table has neither a primary key nor a unique index, the source_lookup() function cannot be used, as it will not behave as expected.

Regards,

John.

[iti-attunity-sup]

Hello John.

Thank you for your reply.

>2. Not exactly. If the table has neither a primary key nor a unique index, the source_lookup() function cannot be used, as it will not behave as expected.

Could you tell me following

3. Source table unique index is using where phrase, so unique index column is not unique entire table.
In this situation, is it possible to use this settings ?
(If no, I would like to know there is other way)

4. Is it possbile to set following in same task.
- Set "Add RRN column to tables without a Primary key or Unique Index" (to add RRN to no pk/ui tables)
- Set Additional RRN in Transformation (to add RRN to ui table)

Best Regards.

[john_wang]

Hello @iti-attunity-sup ,

It seems I may not have fully understood question (3). Could you please elaborate?

---

4. Is it possible to set following in same task.

---

Yes, you are correct. However, please note that the performance of the source_lookup() function is not as efficient as Replicate’s internal RRN-based operation.

Regards,

John.

[iti-attunity-sup]

Hello John.

Thank you for your reply.

>It seems I may not have fully understood question (3). Could you please elaborate?

Source table has unique index like following.

===========================
CREATE UNIQUE INDEX <INDEX_A> ON <TABLE_A> (<COLUMN1>,<COLUMN2>,<COLUMN3>,<COLUMN4>) WHERE <COLUMN1> LIKE 'XXXX' AND <COLUMN1> LIKE 'XXXX'
===========================

This table is replicate by using unique index, but unique index column is not unique in entire table. (because unique index has where phrase)
So we would like to replicate this table by using RRN (not unique index column).

Best Regards.

[iti-attunity-sup]

Hello John

How is additional question ?

Best regards

[john_wang]

Hello @iti-attunity-sup ,

---

This table is replicate by using unique index, but unique index column is not unique in entire table. (because unique index has where phrase)

---

Yes, in this case using RRN as the target table primary key is a better option, unless the target database also supports partial indexes.

However, please keep the following in mind:

1. If the search condition falls within the WHERE clause of the partial index, performance will be good.

2. If the search condition falls outside the WHERE clause, it may result in a full table scan, which can significantly impact performance.

3. If the search condition is outside the scope of the unique index, only the first matching row may be returned.

Hope this helps.

John.