In order to discover the latest source table's inserts, updates and deletes (two-column primary key and additional unique index built on another column) I coded a change capture job flow composed of the steps outined below.
Job 5) aborts with over 50 warnings of unique constraint messages on the secondary unique index.
All primary key and secondary unique index columns have been flagged as key columns throughout the jobs. The jobs are running with a configuration file with 4 nodes.
I am trying to figure out whether I need to sequence the updates by first applying the deletes then the inserts and then the updates or whether the flow and options I chose have fundamental flaws.
Any help is appreciated.
Thanks.
Marco
Code: Select all
1)
Sequence job that is controlling four parallel jobs: it executes job 2) and 3) concurrently and then 4) and 5) sequentially.
Code: Select all
2)
After job
An Oracle Connector (Preserve partitioning=propagate;
Execution mode=parallel) with Round Robin partitioned reads links to a
Transformer (Preserve partitioning=propagate;
Combinability mode=auto;
Execution mode=propagate) which links to an
(After) Dataset stage (Partition type=auto;
Combinability mode=auto;
Execution mode=parallel)
Code: Select all
3)
Before job
An Oracle Connector (Preserve partitioning=propagate;
Execution mode=parallel) with Round Robin partitioned reads links to a
(Before) Dataset stage (Partition type=auto;
Combinability mode=auto;
Execution mode=parallel)
Code: Select all
4)
Change Capture job
After Dataset stage (Preserve partitioning=propagate;
Combinability mode=auto;
Execution mode=parallel) together with the
Before Dataset stage (Preserve partitioning=propagate;
Combinability mode=auto;
Execution mode=parallel) link to a
Change Capture stage (Change keys=primary key and unique index columns;
Change mode=Explicit keys, All values;
Drop out for Copy=True;
Drop out for Delete/Edit/Insert=False;
Execution mode=parallel;
Combinability mode=auto;
Preserve partitioning=Default(set);
INPUT TAB (both After and Before links): Partitioning type=Hash;
Perform sort=Yes, on the three key columns with usage=Sorting, Partitioning/Ascending) which links to a
(CCapture) Dataset stage (Partition type=auto;
Combinability mode=auto;
Execution mode=parallel)
Code: Select all
5)
Update target job
CCapture Dataset stage (Execution mode=parallel;
Combinability mode=auto;
Preserve partitioning=propagate) links to a
Transformer stage (Execution mode=parallel;
Combinability mode=auto;
Preserve partitioning=propagate;
INPUT TAB: Partition type=Auto) which links to three
(INS, UPD, DEL) Oracle Connectors (Execution mode=parallel;
Preserve partitioning=propagate)