RC/Compare : DDL to DDL Compare of foreign keys report differences!

Products

• RC Compare for DB2 for z/OS

Issue/Introduction

When using RC Compare for DB2 for Z/OS(RCC), for DDL comparison using option 4, Source SQL to Target SQL, the strategy incorrectly reports a difference in the foreign key when there is none.

There are some column differences but there are no differences in foreign key relations. The strategy generates the drop and re-create statements for foreign keys.

 

Environment

Release: R20
Component: RCC

Cause

The parent table is not included in the strategy therefore the unique constraint can not be paired and the foreign keys are not paired. 

Resolution

When analyzing a DDL-DDL compare strategy where the child tables are paired, parent tables are not included and the following foreign key compare rules are used:

MOVE_UNPAIRED_SRC_FKS Y  
DROP_UNPAIRED_TRG_FKS Y 

Whenever child tables are paired, RCC by design will try to pair the foreign keys. Before pairing the foreign keys, the unique constraints that the foreign keys depend on have to be paired first. The unique constraints have to be paired first in order to pair the foreign keys.

If there are no differences between the paired unique constraints and the paired foreign keys, then it will be considered that the foreign keys have no differences even when the DDL for foreign keys are identical.

Since the parent table is not included in the strategy, the unique constraint can not be paired and the foreign keys are not paired. The foreign keys on the source become Source created (newly created).

Because of compare rule "DROP_UNPAIRED_TRG_FKS Y", the foreign key on the target is dropped.

Because of rule "MOVE_UNPAIRED_SRC_FKS Y", the unpaired source foreign key is added to the target.

In a SSID-SSID compare strategy, when the parent table is not included, RCC can get the parent information from the catalog and will be able to pair the unique constraints and the foreign keys. This is the reason you will not be seeing this problem in a SSID-SSID compare strategy.

To resolve the problem include the parent table in the source and target DDL. Just add the create table DDL for the parent tables. They don't have to be paired.

In that way the unique constraints that the foreign keys depend on are paired and then the foreign keys are paired and so no changes will be found.

 

Additional Information

DDL File Comparisons

Manage Rule Sets