GoldenGate Extract 11.2 test cases

2 Comments / Oracle GoldenGate / By / / COMPRESSDELETES, Extract, GETUPDATEBEFORES, GoldenGate, Oracle, trail

This article presents the complete full list of cases that has been used in Extract analysis of Oracle GoldenGate 11.2

In the previous article I have presented an summary of all Extract options that influence what is written to the trail files. Here is a summary of what I have tested and what results I have got:

1. Source database

On the source database the schema is:

CREATE TABLE adam.ref1(
 pk1 INT NOT NULL,
 pk2 INT NOT NULL,
 pk3 INT NOT NULL
);
ALTER TABLE adam.ref1 ADD CONSTRAINT ref1pk PRIMARY KEY(pk1, pk2, pk3);

--PK, UI, (pk1) is PK
CREATE TABLE adam.tab0(
 pk1 INT NOT NULL,
 fk1 INT NOT NULL,
 fk2 INT NOT NULL,
 fk3 INT NOT NULL,
 fk4 INT NOT NULL,
 fk5 INT NOT NULL,
 fk6 INT NOT NULL,
 ui1 INT NOT NULL,
 ui2 INT NOT NULL,
 ui3 INT NOT NULL,
 ui4 INT NOT NULL,
 ui5 INT NOT NULL,
 ui6 INT NOT NULL,
 co1 INT NOT NULL,
 co2 INT NOT NULL,
 co3 INT NOT NULL,
 co4 INT NOT NULL,
 co5 INT NOT NULL
);
ALTER TABLE adam.tab0 ADD CONSTRAINT tab0pk PRIMARY KEY(pk1);
ALTER TABLE adam.tab0 ADD CONSTRAINT tab0fk1 FOREIGN KEY(fk1, fk2, fk3) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab0 ADD CONSTRAINT tab0fk2 FOREIGN KEY(fk4, fk5, fk6) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab0 ADD CONSTRAINT tab0u1 UNIQUE(ui1, ui2, ui3);
ALTER TABLE adam.tab0 ADD CONSTRAINT tab0u2 UNIQUE(ui4, ui5, ui6);

--PK, UI, (ui4, ui5, ui6) is PK
CREATE TABLE adam.tab1(
 pk1 INT NOT NULL,
 fk1 INT NOT NULL,
 fk2 INT NOT NULL,
 fk3 INT NOT NULL,
 fk4 INT NOT NULL,
 fk5 INT NOT NULL,
 fk6 INT NOT NULL,
 ui1 INT NOT NULL,
 ui2 INT NOT NULL,
 ui3 INT NOT NULL,
 ui4 INT NOT NULL,
 ui5 INT NOT NULL,
 ui6 INT NOT NULL,
 co1 INT NOT NULL,
 co2 INT NOT NULL,
 co3 INT NOT NULL,
 co4 INT NOT NULL,
 co5 INT NOT NULL
);
ALTER TABLE adam.tab1 ADD CONSTRAINT tab1pk PRIMARY KEY(pk1);
ALTER TABLE adam.tab1 ADD CONSTRAINT tab1fk1 FOREIGN KEY(fk1, fk2, fk3) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab1 ADD CONSTRAINT tab1fk2 FOREIGN KEY(fk4, fk5, fk6) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab1 ADD CONSTRAINT tab1u1 UNIQUE(ui1, ui2, ui3);
ALTER TABLE adam.tab1 ADD CONSTRAINT tab1u2 UNIQUE(ui4, ui5, ui6);

--PK, UI, (col5) is PK
CREATE TABLE adam.tab2(
 pk1 INT NOT NULL,
 fk1 INT NOT NULL,
 fk2 INT NOT NULL,
 fk3 INT NOT NULL,
 fk4 INT NOT NULL,
 fk5 INT NOT NULL,
 fk6 INT NOT NULL,
 ui1 INT NOT NULL,
 ui2 INT NOT NULL,
 ui3 INT NOT NULL,
 ui4 INT NOT NULL,
 ui5 INT NOT NULL,
 ui6 INT NOT NULL,
 co1 INT NOT NULL,
 co2 INT NOT NULL,
 co3 INT NOT NULL,
 co4 INT NOT NULL,
 co5 INT NOT NULL
);
ALTER TABLE adam.tab2 ADD CONSTRAINT tab2pk PRIMARY KEY(pk1);
ALTER TABLE adam.tab2 ADD CONSTRAINT tab2fk1 FOREIGN KEY(fk1, fk2, fk3) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab2 ADD CONSTRAINT tab2fk2 FOREIGN KEY(fk4, fk5, fk6) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab2 ADD CONSTRAINT tab2u1 UNIQUE(ui1, ui2, ui3);
ALTER TABLE adam.tab2 ADD CONSTRAINT tab2u2 UNIQUE(ui4, ui5, ui6);

--UI, (ui4, ui5, ui6) is PK
CREATE TABLE adam.tab3(
 pk1 INT NOT NULL,
 fk1 INT NOT NULL,
 fk2 INT NOT NULL,
 fk3 INT NOT NULL,
 fk4 INT NOT NULL,
 fk5 INT NOT NULL,
 fk6 INT NOT NULL,
 ui1 INT NOT NULL,
 ui2 INT NOT NULL,
 ui3 INT NOT NULL,
 ui4 INT NOT NULL,
 ui5 INT NOT NULL,
 ui6 INT NOT NULL,
 co1 INT NOT NULL,
 co2 INT NOT NULL,
 co3 INT NOT NULL,
 co4 INT NOT NULL,
 co5 INT NOT NULL
);
ALTER TABLE adam.tab3 ADD CONSTRAINT tab3fk1 FOREIGN KEY(fk1, fk2, fk3) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab3 ADD CONSTRAINT tab3fk2 FOREIGN KEY(fk4, fk5, fk6) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab3 ADD CONSTRAINT tab3u1 UNIQUE(ui1, ui2, ui3);
ALTER TABLE adam.tab3 ADD CONSTRAINT tab3u2 UNIQUE(ui4, ui5, ui6);

--UI, (col5) is PK
CREATE TABLE adam.tab4(
 pk1 INT NOT NULL,
 fk1 INT NOT NULL,
 fk2 INT NOT NULL,
 fk3 INT NOT NULL,
 fk4 INT NOT NULL,
 fk5 INT NOT NULL,
 fk6 INT NOT NULL,
 ui1 INT NOT NULL,
 ui2 INT NOT NULL,
 ui3 INT NOT NULL,
 ui4 INT NOT NULL,
 ui5 INT NOT NULL,
 ui6 INT NOT NULL,
 co1 INT NOT NULL,
 co2 INT NOT NULL,
 co3 INT NOT NULL,
 co4 INT NOT NULL,
 co5 INT NOT NULL
);
ALTER TABLE adam.tab4 ADD CONSTRAINT tab4fk1 FOREIGN KEY(fk1, fk2, fk3) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab4 ADD CONSTRAINT tab4fk2 FOREIGN KEY(fk4, fk5, fk6) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab4 ADD CONSTRAINT tab4u1 UNIQUE(ui1, ui2, ui3);
ALTER TABLE adam.tab4 ADD CONSTRAINT tab4u2 UNIQUE(ui4, ui5, ui6);

--nothing, (col5) is PK
CREATE TABLE adam.tab5(
 pk1 INT NOT NULL,
 fk1 INT NOT NULL,
 fk2 INT NOT NULL,
 fk3 INT NOT NULL,
 fk4 INT NOT NULL,
 fk5 INT NOT NULL,
 fk6 INT NOT NULL,
 ui1 INT NOT NULL,
 ui2 INT NOT NULL,
 ui3 INT NOT NULL,
 ui4 INT NOT NULL,
 ui5 INT NOT NULL,
 ui6 INT NOT NULL,
 co1 INT NOT NULL,
 co2 INT NOT NULL,
 co3 INT NOT NULL,
 co4 INT NOT NULL,
 co5 INT NOT NULL
);
ALTER TABLE adam.tab5 ADD CONSTRAINT tab5fk1 FOREIGN KEY(fk1, fk2, fk3) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab5 ADD CONSTRAINT tab5fk2 FOREIGN KEY(fk4, fk5, fk6) references adam.ref1(pk1, pk2, pk3);

--nothing, (co2) is PK, modify PK
CREATE TABLE adam.tab6(
 pk1 INT NOT NULL,
 fk1 INT NOT NULL,
 fk2 INT NOT NULL,
 fk3 INT NOT NULL,
 fk4 INT NOT NULL,
 fk5 INT NOT NULL,
 fk6 INT NOT NULL,
 ui1 INT NOT NULL,
 ui2 INT NOT NULL,
 ui3 INT NOT NULL,
 ui4 INT NOT NULL,
 ui5 INT NOT NULL,
 ui6 INT NOT NULL,
 co1 INT NOT NULL,
 co2 INT NOT NULL,
 co3 INT NOT NULL,
 co4 INT NOT NULL,
 co5 INT NOT NULL
);
ALTER TABLE adam.tab6 ADD CONSTRAINT tab6fk1 FOREIGN KEY(fk1, fk2, fk3) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab6 ADD CONSTRAINT tab6fk2 FOREIGN KEY(fk4, fk5, fk6) references adam.ref1(pk1, pk2, pk3);

--nothing, (allcols) is PK
CREATE TABLE adam.tab7(
 pk1 INT NOT NULL,
 fk1 INT NOT NULL,
 fk2 INT NOT NULL,
 fk3 INT NOT NULL,
 fk4 INT NOT NULL,
 fk5 INT NOT NULL,
 fk6 INT NOT NULL,
 ui1 INT NOT NULL,
 ui2 INT NOT NULL,
 ui3 INT NOT NULL,
 ui4 INT NOT NULL,
 ui5 INT NOT NULL,
 ui6 INT NOT NULL,
 co1 INT NOT NULL,
 co2 INT NOT NULL,
 co3 INT NOT NULL,
 co4 INT NOT NULL,
 co5 INT NOT NULL
);
ALTER TABLE adam.tab7 ADD CONSTRAINT tab7fk1 FOREIGN KEY(fk1, fk2, fk3) references adam.ref1(pk1, pk2, pk3);
ALTER TABLE adam.tab7 ADD CONSTRAINT tab7fk2 FOREIGN KEY(fk4, fk5, fk6) references adam.ref1(pk1, pk2, pk3);

2. Target tables

CREATE TABLE anna.tab1(
 pk1 INT NULL,
 tablename varchar(32) NULL,
 optype varchar(32) NULL,
 beforafterind varchar(32) NULL,
 fk1 INT NULL,
 fk2 INT NULL,
 fk3 INT NULL,
 fk4 INT NULL,
 fk5 INT NULL,
 fk6 INT NULL,
 ui1 INT NULL,
 ui2 INT NULL,
 ui3 INT NULL,
 ui4 INT NULL,
 ui5 INT NULL,
 ui6 INT NULL,
 co1 INT NULL,
 co2 INT NULL,
 co3 INT NULL,
 co4 INT NULL,
 co5 INT NULL,
 pk1b INT NULL,
 fk1b INT NULL,
 fk2b INT NULL,
 fk3b INT NULL,
 fk4b INT NULL,
 fk5b INT NULL,
 fk6b INT NULL,
 ui1b INT NULL,
 ui2b INT NULL,
 ui3b INT NULL,
 ui4b INT NULL,
 ui5b INT NULL,
 ui6b INT NULL,
 co1b INT NULL,
 co2b INT NULL,
 co3b INT NULL,
 co4b INT NULL,
 co5b INT NULL
);

3. Test data

INSERT INTO adam.ref1 VALUES(10, 11, 12);
INSERT INTO adam.ref1 VALUES(10, 11, 13);
COMMIT;

INSERT INTO adam.tab0(pk1, fk1, fk2, fk3, fk4, fk5, fk6, ui1, ui2, ui3, ui4, ui5, ui6, co1, co2, co3, co4, co5)
 VALUES( 0, 10, 11, 12, 10, 11, 12, 20, 21 , 22, 20, 21, 22, 30, 31, 32, 33, 34);
COMMIT;
UPDATE adam.tab0 SET fk2 = 11, fk3 = 13, ui2 = 21, ui3 = 23, co2 = 31, co3 = 33;
COMMIT;
UPDATE adam.tab0 set pk1 = 1;
COMMIT;
DELETE FROM adam.tab0;
COMMIT;

INSERT INTO adam.tab1(pk1, fk1, fk2, fk3, fk4, fk5, fk6, ui1, ui2, ui3, ui4, ui5, ui6, co1, co2, co3, co4, co5)
 VALUES( 0, 10, 11, 12, 10, 11, 12, 20, 21 , 22, 20, 21, 22, 30, 31, 32, 33, 34);
COMMIT;
UPDATE adam.tab1 SET fk2 = 11, fk3 = 13, ui2 = 21, ui3 = 23, co2 = 31, co3 = 33;
COMMIT;
UPDATE adam.tab1 set ui4 = 19;
COMMIT;
DELETE FROM adam.tab1;
COMMIT;

INSERT INTO adam.tab2(pk1, fk1, fk2, fk3, fk4, fk5, fk6, ui1, ui2, ui3, ui4, ui5, ui6, co1, co2, co3, co4, co5)
 VALUES( 0, 10, 11, 12, 10, 11, 12, 20, 21 , 22, 20, 21, 22, 30, 31, 32, 33, 34);
COMMIT;
UPDATE adam.tab2 SET fk2 = 11, fk3 = 13, ui2 = 21, ui3 = 23, co2 = 31, co3 = 33;
COMMIT;
UPDATE adam.tab2 set co5 = 35;
COMMIT;
DELETE FROM adam.tab2;
COMMIT;

INSERT INTO adam.tab3(pk1, fk1, fk2, fk3, fk4, fk5, fk6, ui1, ui2, ui3, ui4, ui5, ui6, co1, co2, co3, co4, co5)
 VALUES( 0, 10, 11, 12, 10, 11, 12, 20, 21 , 22, 20, 21, 22, 30, 31, 32, 33, 34);
COMMIT;
UPDATE adam.tab3 SET fk2 = 11, fk3 = 13, ui2 = 21, ui3 = 23, co2 = 31, co3 = 33;
COMMIT;
UPDATE adam.tab3 set ui4 = 19;
COMMIT;
DELETE FROM adam.tab3;
COMMIT;

INSERT INTO adam.tab4(pk1, fk1, fk2, fk3, fk4, fk5, fk6, ui1, ui2, ui3, ui4, ui5, ui6, co1, co2, co3, co4, co5)
 VALUES( 0, 10, 11, 12, 10, 11, 12, 20, 21 , 22, 20, 21, 22, 30, 31, 32, 33, 34);
COMMIT;
UPDATE adam.tab4 SET fk2 = 11, fk3 = 13, ui2 = 21, ui3 = 23, co2 = 31, co3 = 33;
COMMIT;
UPDATE adam.tab4 set co5 = 35;
COMMIT;
DELETE FROM adam.tab4;
COMMIT;

INSERT INTO adam.tab5(pk1, fk1, fk2, fk3, fk4, fk5, fk6, ui1, ui2, ui3, ui4, ui5, ui6, co1, co2, co3, co4, co5)
 VALUES( 0, 10, 11, 12, 10, 11, 12, 20, 21 , 22, 20, 21, 22, 30, 31, 32, 33, 34);
COMMIT;
UPDATE adam.tab5 SET fk2 = 11, fk3 = 13, ui2 = 21, ui3 = 23, co2 = 31, co3 = 33;
COMMIT;
UPDATE adam.tab5 set co5 = 35;
COMMIT;
DELETE FROM adam.tab5;
COMMIT;

INSERT INTO adam.tab6(pk1, fk1, fk2, fk3, fk4, fk5, fk6, ui1, ui2, ui3, ui4, ui5, ui6, co1, co2, co3, co4, co5)
 VALUES( 0, 10, 11, 12, 10, 11, 12, 20, 21 , 22, 20, 21, 22, 30, 31, 32, 33, 34);
COMMIT;
UPDATE adam.tab6 SET fk2 = 11, fk3 = 13, ui2 = 21, ui3 = 23, co2 = 30, co3 = 33;
COMMIT;
UPDATE adam.tab6 set co2 = 29;
COMMIT;
DELETE FROM adam.tab6;
COMMIT;

INSERT INTO adam.tab7(pk1, fk1, fk2, fk3, fk4, fk5, fk6, ui1, ui2, ui3, ui4, ui5, ui6, co1, co2, co3, co4, co5)
 VALUES( 0, 10, 11, 12, 10, 11, 12, 20, 21 , 22, 20, 21, 22, 30, 31, 32, 33, 34);
COMMIT;
UPDATE adam.tab7 SET fk2 = 11, fk3 = 13, ui2 = 21, ui3 = 23, co2 = 31, co3 = 33;
COMMIT;
UPDATE adam.tab7 set co5 = 35;
COMMIT;
DELETE FROM adam.tab7;
COMMIT;

3. ADD TRANDATA commands

--PK, UI, (pk1) is PK
ADD TRANDATA adam.tab0, COLS(co4)
--PK, UI, (ui4, ui5, ui6) is PK
ADD TRANDATA adam.tab1, COLS(co4, ui4, ui5, ui6), NOKEY
--PK, UI, (co5) is PK
ADD TRANDATA adam.tab2, COLS(co4, co5), NOKEY
--UI, (ui4, ui5, ui6) is PK
ADD TRANDATA adam.tab3, COLS(co4, ui4, ui5, ui6), NOKEY
--UI, (co5) is PK
ADD TRANDATA adam.tab4, COLS(co4, co5), NOKEY

--nothing, (co5) is PK
ADD TRANDATA adam.tab5, COLS(co4, co5), NOKEY
--nothing, (co2) is PK, modify PK
ADD TRANDATA adam.tab6, COLS(co4, co2), NOKEY
--nothing, (allcols) is PK
ADD TRANDATA adam.tab7, COLS(pk1, fk1, fk2, fk3, fk4, fk5, fk6, ui1, ui2, ui3, ui4, ui5, ui6, co1, co2, co3, co4, co5), NOKEY

4. Replicat PARAM file

INSERTALLRECORDS
MAP adam.tab0, TARGET anna.tab1, KEYCOLS(pk1), &
 COLMAP(pk1 = pk1, tablename = @GETENV("GGHEADER","TABLENAME"), optype = @GETENV("GGHEADER","OPTYPE"), beforafterind = @GETENV("GGHEADER","BEFOREAFTERINDICATOR"), &
 fk1 = fk1, fk2 = fk2, fk3 = fk3, fk4 = fk4, fk5 = fk5, fk6 = fk6, &
 ui1 = ui1, ui2 = ui2, ui3 = ui3, ui4 = ui4, ui5 = ui5, ui6 = ui6, &
 co1 = co1, co2 = co2, co3 = co3, co4 = co4, co5 = co5, &
 pk1b = BEFORE.pk1, 
 fk1b = BEFORE.fk1, fk2b = BEFORE.fk2, fk3b = BEFORE.fk3, fk4b = BEFORE.fk4, fk5b = BEFORE.fk5, fk6b = BEFORE.fk6, &
 ui1b = BEFORE.ui1, ui2b = BEFORE.ui2, ui3b = BEFORE.ui3, ui4b = BEFORE.ui4, ui5b = BEFORE.ui5, ui6b = BEFORE.ui6, &
 co1b = BEFORE.co1, co2b = BEFORE.co2, co3b = BEFORE.co3, co4b = BEFORE.co4, co5b = BEFORE.co5 &
 );
MAP adam.tab1, TARGET anna.tab1, KEYCOLS(pk1), &
 COLMAP(pk1 = pk1, tablename = @GETENV("GGHEADER","TABLENAME"), optype = @GETENV("GGHEADER","OPTYPE"), beforafterind = @GETENV("GGHEADER","BEFOREAFTERINDICATOR"), &
 fk1 = fk1, fk2 = fk2, fk3 = fk3, fk4 = fk4, fk5 = fk5, fk6 = fk6, &
 ui1 = ui1, ui2 = ui2, ui3 = ui3, ui4 = ui4, ui5 = ui5, ui6 = ui6, &
 co1 = co1, co2 = co2, co3 = co3, co4 = co4, co5 = co5, &
 pk1b = BEFORE.pk1, 
 fk1b = BEFORE.fk1, fk2b = BEFORE.fk2, fk3b = BEFORE.fk3, fk4b = BEFORE.fk4, fk5b = BEFORE.fk5, fk6b = BEFORE.fk6, &
 ui1b = BEFORE.ui1, ui2b = BEFORE.ui2, ui3b = BEFORE.ui3, ui4b = BEFORE.ui4, ui5b = BEFORE.ui5, ui6b = BEFORE.ui6, &
 co1b = BEFORE.co1, co2b = BEFORE.co2, co3b = BEFORE.co3, co4b = BEFORE.co4, co5b = BEFORE.co5 &
 );
MAP adam.tab2, TARGET anna.tab1, KEYCOLS(pk1), &
 COLMAP(pk1 = pk1, tablename = @GETENV("GGHEADER","TABLENAME"), optype = @GETENV("GGHEADER","OPTYPE"), beforafterind = @GETENV("GGHEADER","BEFOREAFTERINDICATOR"), &
 fk1 = fk1, fk2 = fk2, fk3 = fk3, fk4 = fk4, fk5 = fk5, fk6 = fk6, &
 ui1 = ui1, ui2 = ui2, ui3 = ui3, ui4 = ui4, ui5 = ui5, ui6 = ui6, &
 co1 = co1, co2 = co2, co3 = co3, co4 = co4, co5 = co5, &
 pk1b = BEFORE.pk1, 
 fk1b = BEFORE.fk1, fk2b = BEFORE.fk2, fk3b = BEFORE.fk3, fk4b = BEFORE.fk4, fk5b = BEFORE.fk5, fk6b = BEFORE.fk6, &
 ui1b = BEFORE.ui1, ui2b = BEFORE.ui2, ui3b = BEFORE.ui3, ui4b = BEFORE.ui4, ui5b = BEFORE.ui5, ui6b = BEFORE.ui6, &
 co1b = BEFORE.co1, co2b = BEFORE.co2, co3b = BEFORE.co3, co4b = BEFORE.co4, co5b = BEFORE.co5 &
 );
MAP adam.tab3, TARGET anna.tab1, KEYCOLS(pk1), &
 COLMAP(pk1 = pk1, tablename = @GETENV("GGHEADER","TABLENAME"), optype = @GETENV("GGHEADER","OPTYPE"), beforafterind = @GETENV("GGHEADER","BEFOREAFTERINDICATOR"), &
 fk1 = fk1, fk2 = fk2, fk3 = fk3, fk4 = fk4, fk5 = fk5, fk6 = fk6, &
 ui1 = ui1, ui2 = ui2, ui3 = ui3, ui4 = ui4, ui5 = ui5, ui6 = ui6, &
 co1 = co1, co2 = co2, co3 = co3, co4 = co4, co5 = co5, &
 pk1b = BEFORE.pk1, 
 fk1b = BEFORE.fk1, fk2b = BEFORE.fk2, fk3b = BEFORE.fk3, fk4b = BEFORE.fk4, fk5b = BEFORE.fk5, fk6b = BEFORE.fk6, &
 ui1b = BEFORE.ui1, ui2b = BEFORE.ui2, ui3b = BEFORE.ui3, ui4b = BEFORE.ui4, ui5b = BEFORE.ui5, ui6b = BEFORE.ui6, &
 co1b = BEFORE.co1, co2b = BEFORE.co2, co3b = BEFORE.co3, co4b = BEFORE.co4, co5b = BEFORE.co5 &
 );
MAP adam.tab4, TARGET anna.tab1, KEYCOLS(pk1), &
 COLMAP(pk1 = pk1, tablename = @GETENV("GGHEADER","TABLENAME"), optype = @GETENV("GGHEADER","OPTYPE"), beforafterind = @GETENV("GGHEADER","BEFOREAFTERINDICATOR"), &
 fk1 = fk1, fk2 = fk2, fk3 = fk3, fk4 = fk4, fk5 = fk5, fk6 = fk6, &
 ui1 = ui1, ui2 = ui2, ui3 = ui3, ui4 = ui4, ui5 = ui5, ui6 = ui6, &
 co1 = co1, co2 = co2, co3 = co3, co4 = co4, co5 = co5, &
 pk1b = BEFORE.pk1, 
 fk1b = BEFORE.fk1, fk2b = BEFORE.fk2, fk3b = BEFORE.fk3, fk4b = BEFORE.fk4, fk5b = BEFORE.fk5, fk6b = BEFORE.fk6, &
 ui1b = BEFORE.ui1, ui2b = BEFORE.ui2, ui3b = BEFORE.ui3, ui4b = BEFORE.ui4, ui5b = BEFORE.ui5, ui6b = BEFORE.ui6, &
 co1b = BEFORE.co1, co2b = BEFORE.co2, co3b = BEFORE.co3, co4b = BEFORE.co4, co5b = BEFORE.co5 &
 );
MAP adam.tab5, TARGET anna.tab1, KEYCOLS(pk1), &
 COLMAP(pk1 = pk1, tablename = @GETENV("GGHEADER","TABLENAME"), optype = @GETENV("GGHEADER","OPTYPE"), beforafterind = @GETENV("GGHEADER","BEFOREAFTERINDICATOR"), &
 fk1 = fk1, fk2 = fk2, fk3 = fk3, fk4 = fk4, fk5 = fk5, fk6 = fk6, &
 ui1 = ui1, ui2 = ui2, ui3 = ui3, ui4 = ui4, ui5 = ui5, ui6 = ui6, &
 co1 = co1, co2 = co2, co3 = co3, co4 = co4, co5 = co5, &
 pk1b = BEFORE.pk1, 
 fk1b = BEFORE.fk1, fk2b = BEFORE.fk2, fk3b = BEFORE.fk3, fk4b = BEFORE.fk4, fk5b = BEFORE.fk5, fk6b = BEFORE.fk6, &
 ui1b = BEFORE.ui1, ui2b = BEFORE.ui2, ui3b = BEFORE.ui3, ui4b = BEFORE.ui4, ui5b = BEFORE.ui5, ui6b = BEFORE.ui6, &
 co1b = BEFORE.co1, co2b = BEFORE.co2, co3b = BEFORE.co3, co4b = BEFORE.co4, co5b = BEFORE.co5 &
 );
MAP adam.tab6, TARGET anna.tab1, KEYCOLS(pk1), &
 COLMAP(pk1 = pk1, tablename = @GETENV("GGHEADER","TABLENAME"), optype = @GETENV("GGHEADER","OPTYPE"), beforafterind = @GETENV("GGHEADER","BEFOREAFTERINDICATOR"), &
 fk1 = fk1, fk2 = fk2, fk3 = fk3, fk4 = fk4, fk5 = fk5, fk6 = fk6, &
 ui1 = ui1, ui2 = ui2, ui3 = ui3, ui4 = ui4, ui5 = ui5, ui6 = ui6, &
 co1 = co1, co2 = co2, co3 = co3, co4 = co4, co5 = co5, &
 pk1b = BEFORE.pk1, 
 fk1b = BEFORE.fk1, fk2b = BEFORE.fk2, fk3b = BEFORE.fk3, fk4b = BEFORE.fk4, fk5b = BEFORE.fk5, fk6b = BEFORE.fk6, &
 ui1b = BEFORE.ui1, ui2b = BEFORE.ui2, ui3b = BEFORE.ui3, ui4b = BEFORE.ui4, ui5b = BEFORE.ui5, ui6b = BEFORE.ui6, &
 co1b = BEFORE.co1, co2b = BEFORE.co2, co3b = BEFORE.co3, co4b = BEFORE.co4, co5b = BEFORE.co5 &
 );
MAP adam.tab7, TARGET anna.tab1, KEYCOLS(pk1), &
 COLMAP(pk1 = pk1, tablename = @GETENV("GGHEADER","TABLENAME"), optype = @GETENV("GGHEADER","OPTYPE"), beforafterind = @GETENV("GGHEADER","BEFOREAFTERINDICATOR"), &
 fk1 = fk1, fk2 = fk2, fk3 = fk3, fk4 = fk4, fk5 = fk5, fk6 = fk6, &
 ui1 = ui1, ui2 = ui2, ui3 = ui3, ui4 = ui4, ui5 = ui5, ui6 = ui6, &
 co1 = co1, co2 = co2, co3 = co3, co4 = co4, co5 = co5, &
 pk1b = BEFORE.pk1, 
 fk1b = BEFORE.fk1, fk2b = BEFORE.fk2, fk3b = BEFORE.fk3, fk4b = BEFORE.fk4, fk5b = BEFORE.fk5, fk6b = BEFORE.fk6, &
 ui1b = BEFORE.ui1, ui2b = BEFORE.ui2, ui3b = BEFORE.ui3, ui4b = BEFORE.ui4, ui5b = BEFORE.ui5, ui6b = BEFORE.ui6, &
 co1b = BEFORE.co1, co2b = BEFORE.co2, co3b = BEFORE.co3, co4b = BEFORE.co4, co5b = BEFORE.co5 &
 );

5. Results test script

To check the results I am running on the target database the following script:

select 
 case when OPTYPE like 'INSERT%' then 'I' when OPTYPE like 'DELETE%' then 'D' else 'U' end as O, 
 tablename as TB, 
 case when beforafterind like 'BEFO%' then 'B' when beforafterind like 'AFT%' then 'A' else '?' end as I, 
 pk1,
 fk1, fk2, fk3, fk4, fk5, fk6,
 ui1, ui2, ui3, ui4, ui5, ui6,
 co1, co2, co3, co4, co5,
 pk1b as p1b,
 fk1b as f1b, fk2b as f2b, fk3b as f3b, fk4b as f4b, fk5b as f5b, fk6b as f6b,
 ui1b as u1b, ui2b as u2b, ui3b as u3b, ui4b as u4b, ui5b as u5b, ui6b as u6b,
 co1b as c1b, co2b as c2b, co3b as c3b, co4b as c4b, co5b as c5b
from anna.tab1;

6. Test results

To find parameters and their order in a more easy fashion I have used the following codes for them in my tests:

  • 4 – NOCOMPRESSUPDATES
  • 5 – COMPRESSUPDATE
  • 6 – NOCOMPRESSDELETES
  • 7 – COMPRESSDELETES
  • 8 – IGNOREUPDATEBEFORES
  • 9 – GETUPDATEBEFORES

These are the results for total 79 test cases:

Test no. Order Parameters Extract Results Result category
4 4 NOCOMPRESSUPDATES PRM SQL OUT UPDATE: after
DELETE: only PK
46 46 NOCOMPRESSUPDATES
NOCOMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: full
46 64 NOCOMPRESSDELETES
NOCOMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: full
468 468 NOCOMPRESSUPDATES
NOCOMPRESSDELETES
IGNOREUPDATEBEFORES		 PRM SQL OUT UPDATE: after
DELETE: full
468 486 NOCOMPRESSUPDATES
IGNOREUPDATEBEFORES
NOCOMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: full
468 648 NOCOMPRESSDELETES
NOCOMPRESSUPDATES
IGNOREUPDATEBEFORES		 PRM SQL OUT UPDATE: after
DELETE: full
468 684 NOCOMPRESSDELETES
IGNOREUPDATEBEFORES
NOCOMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: full
468 846 IGNOREUPDATEBEFORES
NOCOMPRESSUPDATES
NOCOMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: full
468 864 IGNOREUPDATEBEFORES
NOCOMPRESSDELETES
NOCOMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: full
469 469 NOCOMPRESSUPDATES
NOCOMPRESSDELETES
GETUPDATEBEFORES		 PRM SQL OUT UPDATE: before + after
DELETE: full
469 496 NOCOMPRESSUPDATES
GETUPDATEBEFORES
NOCOMPRESSDELETES		 PRM SQL OUT UPDATE: before + after
DELETE: full
469 649 NOCOMPRESSDELETES
NOCOMPRESSUPDATES
GETUPDATEBEFORES		 PRM SQL OUT UPDATE: before + after
DELETE: full
469 694 NOCOMPRESSDELETES
GETUPDATEBEFORES
NOCOMPRESSUPDATES		 PRM SQL OUT UPDATE: before + after
DELETE: full
469 946 GETUPDATEBEFORES
NOCOMPRESSUPDATES
NOCOMPRESSDELETES		 PRM SQL OUT UPDATE: before + after
DELETE: full
469 964 GETUPDATEBEFORES
NOCOMPRESSDELETES
NOCOMPRESSUPDATES		 PRM SQL OUT UPDATE: before + after
DELETE: full
47 47 NOCOMPRESSUPDATES
COMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: only PK
47 74 COMPRESSDELETES
NOCOMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: only PK
478 478 NOCOMPRESSUPDATES
COMPRESSDELETES
IGNOREUPDATEBEFORES		 PRM SQL OUT UPDATE: after
DELETE: only PK
478 487 NOCOMPRESSUPDATES
IGNOREUPDATEBEFORES
COMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: only PK
478 748 COMPRESSDELETES
NOCOMPRESSUPDATES
IGNOREUPDATEBEFORES		 PRM SQL OUT UPDATE: after
DELETE: only PK
478 784 COMPRESSDELETES
IGNOREUPDATEBEFORES
NOCOMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: only PK
478 847 IGNOREUPDATEBEFORES
NOCOMPRESSUPDATES
COMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: only PK
478 874 IGNOREUPDATEBEFORES
COMPRESSDELETES
NOCOMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: only PK
479 479 NOCOMPRESSUPDATES
COMPRESSDELETES
GETUPDATEBEFORES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
479 497 NOCOMPRESSUPDATES
GETUPDATEBEFORES
COMPRESSDELETES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
479 749 COMPRESSDELETES
NOCOMPRESSUPDATES
GETUPDATEBEFORES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
479 794 COMPRESSDELETES
GETUPDATEBEFORES
NOCOMPRESSUPDATES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
479 947 GETUPDATEBEFORES
NOCOMPRESSUPDATES
COMPRESSDELETES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
479 974 GETUPDATEBEFORES
COMPRESSDELETES
NOCOMPRESSUPDATES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
48 48 NOCOMPRESSUPDATES
IGNOREUPDATEBEFORES		 PRM SQL OUT UPDATE: after
DELETE: only PK
48 84 IGNOREUPDATEBEFORES
NOCOMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: only PK
49 49 NOCOMPRESSUPDATES
GETUPDATEBEFORES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
49 94 GETUPDATEBEFORES
NOCOMPRESSUPDATES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
5 5 COMPRESSUPDATES PRM SQL OUT UPDATE: after
DELETE: only PK
56 56 COMPRESSUPDATES
NOCOMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: full
56 65 NOCOMPRESSDELETES
COMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: full
568 568 COMPRESSUPDATES
NOCOMPRESSDELETES
IGNOREUPDATEBEFORES		 PRM SQL OUT UPDATE: after
DELETE: full
568 586 COMPRESSUPDATES
IGNOREUPDATEBEFORES
NOCOMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: full
568 658 NOCOMPRESSDELETES
COMPRESSUPDATES
IGNOREUPDATEBEFORES		 PRM SQL OUT UPDATE: after
DELETE: full
568 685 NOCOMPRESSDELETES
IGNOREUPDATEBEFORES
COMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: full
568 856 IGNOREUPDATEBEFORES
COMPRESSUPDATES
NOCOMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: full
568 865 IGNOREUPDATEBEFORES
NOCOMPRESSDELETES
COMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: full
569 569 COMPRESSUPDATES
NOCOMPRESSDELETES
GETUPDATEBEFORES		 PRM SQL OUT UPDATE: before + after
DELETE: full
569 596 COMPRESSUPDATES
GETUPDATEBEFORES
NOCOMPRESSDELETES		 PRM SQL OUT UPDATE: before + after
DELETE: full
569 659 NOCOMPRESSDELETES
COMPRESSUPDATES
GETUPDATEBEFORES		 PRM SQL OUT UPDATE: before + after
DELETE: full
569 695 NOCOMPRESSDELETES
GETUPDATEBEFORES
COMPRESSUPDATES		 PRM SQL OUT UPDATE: before + after
DELETE: full
569 956 GETUPDATEBEFORES
COMPRESSUPDATES
NOCOMPRESSDELETES		 PRM SQL OUT UPDATE: before + after
DELETE: full
569 965 GETUPDATEBEFORES
NOCOMPRESSDELETES
COMPRESSUPDATES		 PRM SQL OUT UPDATE: before + after
DELETE: full
57 57 COMPRESSUPDATES
COMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: only PK
57 75 COMPRESSDELETES
COMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: only PK
578 578 COMPRESSUPDATES
COMPRESSDELETES
IGNOREUPDATEBEFORES		 PRM SQL OUT UPDATE: after
DELETE: only PK
578 587 COMPRESSUPDATES
IGNOREUPDATEBEFORES
COMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: only PK
578 758 COMPRESSDELETES
COMPRESSUPDATES
IGNOREUPDATEBEFORES		 PRM SQL OUT UPDATE: after
DELETE: only PK
578 785 COMPRESSDELETES
IGNOREUPDATEBEFORES
COMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: only PK
578 857 IGNOREUPDATEBEFORES
COMPRESSUPDATES
COMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: only PK
578 875 IGNOREUPDATEBEFORES
COMPRESSDELETES
COMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: only PK
579 579 COMPRESSUPDATES
COMPRESSDELETES
GETUPDATEBEFORES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
579 597 COMPRESSUPDATES
GETUPDATEBEFORES
COMPRESSDELETES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
579 759 COMPRESSDELETES
COMPRESSUPDATES
GETUPDATEBEFORES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
579 795 COMPRESSDELETES
GETUPDATEBEFORES
COMPRESSUPDATES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
579 957 GETUPDATEBEFORES
COMPRESSUPDATES
COMPRESSDELETES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
579 975 GETUPDATEBEFORES
COMPRESSDELETES
COMPRESSUPDATES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
58 58 COMPRESSUPDATES
IGNOREUPDATEBEFORES		 PRM SQL OUT UPDATE: after
DELETE: only PK
58 85 IGNOREUPDATEBEFORES
COMPRESSUPDATES		 PRM SQL OUT UPDATE: after
DELETE: only PK
59 59 COMPRESSUPDATES
GETUPDATEBEFORES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
59 95 GETUPDATEBEFORES
COMPRESSUPDATES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
6 6 NOCOMPRESSDELETES PRM SQL OUT UPDATE: after
DELETE: full
68 68 NOCOMPRESSDELETES
IGNOREUPDATEBEFORES		 PRM SQL OUT UPDATE: after
DELETE: full
68 86 IGNOREUPDATEBEFORES
NOCOMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: full
69 69 NOCOMPRESSDELETES
GETUPDATEBEFORES		 PRM SQL OUT UPDATE: before + after
DELETE: full
69 96 GETUPDATEBEFORES
NOCOMPRESSDELETES		 PRM SQL OUT UPDATE: before + after
DELETE: full
7 7 COMPRESSDELETES PRM SQL OUT UPDATE: after
DELETE: only PK
78 78 COMPRESSDELETES
IGNOREUPDATEBEFORES		 PRM SQL OUT UPDATE: after
DELETE: only PK
78 87 IGNOREUPDATEBEFORES
COMPRESSDELETES		 PRM SQL OUT UPDATE: after
DELETE: only PK
79 79 COMPRESSDELETES
GETUPDATEBEFORES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
79 97 GETUPDATEBEFORES
COMPRESSDELETES		 PRM SQL OUT UPDATE: before + after
DELETE: only PK
8 8 IGNOREUPDATEBEFORES PRM SQL OUT UPDATE: after
DELETE: only PK
9 9 GETUPDATEBEFORES PRM SQL OUT UPDATE: before + after
DELETE: only PK
(none) PRM SQL OUT UPDATE: after
DELETE: only PK

2 thoughts on “GoldenGate Extract 11.2 test cases”

  1. Pingback: What is Oracle GoldenGate Extract writing to the trail file (OGG 12.1) – Bersler

  2. Pingback: What is Oracle GoldenGate Extract writing to the trail file (OGG 12.2 & 12.3) – Bersler

Leave a Comment

Your email address will not be published. Required fields are marked *