Primary Keys issue in Data extraction from AS400 DB2

[suvbin]

AS400 DB2 Tables have Key fields defined. However, when data is brought into the target SQL instance, the key definitions are being dropped in the target during the full load.    

Any ideas on how to overcome this challenge.

[lyka]

Hi Sundeep, 

May i know what is the target endpoint?

If you look in the designer > table settings > transform tab, do you see a key assigned to the column?

Thanks,

Lyka

[suvbin]

target endpoint is  Azure SQL Managed instance. And also we are unable to see the key assigned to it, for few tables.  For few tables its showing. But for few of them its not showing

[lyka]

Hi Sundeep,

If you are not able to see the keys in the UI then Replicate was not able to recognize the key. If you add the key manually on the UI and reload the task, is the key created on the target?

  I suggest to open a support case and provide the source and target DDL of one of the table that you have an issue with and provide a task log with source_unload and target_load set to trace to further troubleshoot this.

Thanks,

Lyka

[suvbin]

Thank you for the clarification. Will check and let you know. 

[Bill_Steinagle · Accepted solution]

Hi,

Thank you for the post to the QDI forums. The PK or Unique Index if defined to the DB2i DB Catalog we should be able to read it and define to the Target and can check via the Transform on the Table. The other issue is if there is no PK or Unique Index you may want to consider using the RRN as the PK. This is on the DB2i Source Endpoint as shown below.

Open the DB2i Source Endpoint and click on Advanced and scroll down see the RRN option when  there is no PK or Unique Index defined for a Table:

Thanks!

Bill