DR4A V1.2 Sequence not replicated

After insert on capture side on table with autonumber column, data is inserted, but sequence is not replicated on apply side.

You can follow the following steps to recreate the scenario.

CREATE LARGE TABLESPACE "TSD_DBA_TEST"

IN DATABASE PARTITION GROUP "IBMDEFAULTGROUP"

PAGESIZE 32 K

MANAGED BY AUTOMATIC STORAGE

AUTORESIZE YES

INCREASESIZE 10 PERCENT

EXTENTSIZE 32

BUFFERPOOL "IBMDEFAULTBP"

OVERHEAD INHERIT

TRANSFERRATE INHERIT

DATA TAG INHERIT

USING STOGROUP "IBMSTOGROUP";

CREATE LARGE TABLESPACE "TSI_DBA_TEST"

IN DATABASE PARTITION GROUP "IBMDEFAULTGROUP"

PAGESIZE 32 K

MANAGED BY AUTOMATIC STORAGE

AUTORESIZE YES

INCREASESIZE 10 PERCENT

EXTENTSIZE 32

BUFFERPOOL "IBMDEFAULTBP"

OVERHEAD INHERIT

TRANSFERRATE INHERIT

DATA TAG INHERIT

USING STOGROUP "IBMSTOGROUP";

CREATE TABLE "PRD_DBA"."PETRA_TEST_AUTONUM" (

"ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 NO CYCLE NO CACHE NO ORDER ),

"NUM" SMALLINT NOT NULL,

"TEXT" CHAR(10 CODEUNITS32)

)

ORGANIZE BY ROW

DATA CAPTURE NONE

IN "TSD_DBA_TEST" INDEX IN "TSI_DBA_TEST"

DISTRIBUTE BY HASH ("NUM")

COMPRESS YES STATIC;

ALTER TABLE "PRD_DBA"."PETRA_TEST_AUTONUM" ADD CONSTRAINT "PK_PETRA_TEST_AUTONUM" PRIMARY KEY

("NUM") NOT ENFORCED;

COMMENT ON TABLE "PRD_DBA"."PETRA_TEST_AUTONUM" IS

'Petra: TESTNA_TABELA';

On capture side we inserted 2 records:

INSERT INTO "PRD_DBA"."PETRA_TEST_AUTONUM" (NUM, TEXT) VALUES (1, 'a');

INSERT INTO "PRD_DBA"."PETRA_TEST_AUTONUM" (NUM, TEXT) VALUES (2, 'b');

Then we insert 3 records on apply side:

INSERT INTO "PRD_DBA"."PETRA_TEST_AUTONUM" (NUM, TEXT) VALUES (3, 'C');

INSERT INTO "PRD_DBA"."PETRA_TEST_AUTONUM" (NUM, TEXT) VALUES (4, 'D');

INSERT INTO "PRD_DBA"."PETRA_TEST_AUTONUM" (NUM, TEXT) VALUES (5, 'E');

As we see on results of insert on apply side below, replication does not consider autonumber from capture side. We ran the following query on the apply side:

select *

FROM "PRD_DBA"."PETRA_TEST_AUTONUM"

order by id

The result of the select:

ID NUM TEXT

-- --- ----------

1 3 C

1 1 a

2 4 D

2 2 b

3 5 E

A workaround suggested by the development team (Senthil C., Nishant M.) is to use odd/even numbering on capture/apply side. However, this requires manual steps from the DBA. Since there will be a large number of tables used on our DWH environment, we expect the solution to be automatic.

As per agreement with DR4A dev team and case TS003221534 this request was opened.