IBM DB2 i series , source tables does not having primary key

[AKV0524]

Hi Team,

we are trying to replicate some tables from IBM DB2 i series source in which we observed that tables are not having primary keys so we went though guide and we found out that in advance option there is one option "Add RRN column to tables without a Primary key or Unique Index".

Do we need to select this option while running CDC job if we don't have any primary key available in the source to capture insert, update and delete.

Thanks

Amit

[john_wang]

Hello @AKV0524 ,

Thanks for reaching out!

Relative Record Number (RRN) is a hidden column in DB2i  tables (PF), and it's recorded in every entry of Journal, this column is extremely useful sometimes. it's just like the hidden column "ROWID" in Oracle world.

You can enable or disable the "Add RRN column" option, it's irrelevant to the source side changes capture, however it may improve the target side applying performance as it can act as a PK or Unique Index to position the target rows(s) as quick as possible, especially if the target side table does not have a regular PK or Unique Index column(s), and the table has huge number of rows. In this scenario it's recommended to enable the option.

However please take note the limitations while using RRN as PK:

1- RRN will not be unique for each row of a multi-member or distributed table 

2- RRN will be changed for a row in a table after some special operations eg re-organizing the PF

A reload is needed under this scenario.

Hope this helps.

Regards,

John.

[AKV0524]

Hi Wang,

Thanks for the input.

one doubt still i have

i have created a logstream and Replicate CDC job in which i can see insert,upate and delete is coming however when i looked the delete record in _CT table i can see that delete records are having all column value is null, does it work like that only, will delete records which are capture in _CT table those delete records are going to merge in main table and delete records should be deleted from the main table.

Thanks

Amit

[john_wang]

Hello Amit @AKV0524 ,

Good question!

I think this is relevant to the journal setting. When the Record images parameter set to *AFTER, deleted records will contain NULL values in their data fields. To save a copy of the deleted record to the deleted record journal, you must set the Record images parameter set to *BOTH.

Let me know if it works for you.

Regards,

John.

[AKV0524]

sure john will update you.

This record parameter setting needs to be done by the DBA team in their as400 system.

There is no way that we can do this seting change in Replicate CDC task?

Rhanks

Amit

[john_wang]

Hello @AKV0524 ,

This can only be done in DB2i Journal settings.

Regards,

John.

[AKV0524]

Thanks john for the your input we will connect our DBA team on this.

what if  dba team does n't want to change this parameter setting so in that case delete RECORDS which Attunity capture in _CT table which consists of null value so Attunity delete those records from main table ot it will not delete records from main table.