Limitation of Parallel Replicat for non-Oracle databases

Different ordering of foreign key column indexes associated with the Parent’s primary key, causes parellel Replicat to fail. This implies that parallel Replicat abends in cases where the parent has a composite primary key such that when the foreign key referencing it in the child table has a different ordering in their ordinal position than the primary key.

For example:

CREATE TABLE atstgt.tgt (
  pk1 VARCHAR(10) NOT NULL,
  pk2 VARCHAR(20) NOT NULL,
  num INT,
) PRIMARY KEY(pk1, pk2);

CREATE TABLE atstgt.child (
  id INT NOT NULL,
  pk2 VARCHAR(10) NOT NULL,
  pk1 VARCHAR(20) NOT NULL,
  details VARCHAR(100),
  CONSTRAINT fk_child FOREIGN KEY (pk1, pk2) REFERENCES atstgt.tgt (pk1, pk2) ON DELETE CASCADE ON UPDATE CASCADE) PRIMARY KEY(id);

In this example, the primary key of parent (atstgt.tgt) has pk1 at position 1 and pk2 at position 2. However, in the child table the ordinal position is reversed and pk1 ordinal position 3 comes after pk2's ordinal position 2. This leads to intermittent failures in parallel Replicat with ROW NOT FOUND error.

To avoid such issues, ensure that the indexes of the foreign key columns have the same ordering in the child table when compared with the ordering of composite primary key columns in the parent table, as shown in the following example:

CREATE TABLE atstgt.tgt (
  pk1 VARCHAR(10) NOT NULL,
  pk2 VARCHAR(20) NOT NULL,
  num INT,
) PRIMARY KEY(pk1, pk2);

CREATE TABLE atstgt.child (
  id INT NOT NULL,
  pk1 VARCHAR(10) NOT NULL,
  pk2 VARCHAR(20) NOT NULL,
  details VARCHAR(100),
  CONSTRAINT fk_child FOREIGN KEY (pk1, pk2) REFERENCES atstgt.tgt (pk1, pk2) ON DELETE CASCADE ON UPDATE CASCADE) PRIMARY KEY(id);