CDC Stage producing strange results
Posted: Mon Jul 16, 2012 3:13 pm
I am currently experiencing some strange behavior with the Change Data Capture stage in one of my jobs. Below is a diagram of the job.
The CDC stage is hash partitioned on both input links heading into the CDC stage. For both links, the hash partition is defined on the same 2 fields (MBR_DEMO_KEY and ADDR_TYPE), in the same order, and with the same sorting parameters. The CDC stage has those same 2 fields defined as the change keys.
For a given member that I was researching, there is only one record in the After set with the given MBR_DEMO_KEY and likewise there is only one record in the Before set with the given MBR_DEMO_KEY. Both records have the same ADDR_TYPE. For some reason this single record is producing 2 output records from the CDC, an edit and a delete.
I understand that it could be possible for there to be a delete and an insert generated if, for example, the ADDR_TYPE was different for both records with the same MBR_DEMO_KEY. An edit and a delete makes no sense though.
I decided to go to the QA environment and see if I could test out a few changes that might fix this problem. The first thing I did was to run the code unaltered to replicate the problem. In our QA environment, the same code/data produced the desired result (just a single edit record).
What could possibly be causing this strange behavior in our production environment?
Any help would be greatly appreciated!
Code: Select all
Oracle (Before)
| ______________>Dataset (Deletes)
| /
V /
Dataset (After)--->CDC--->Transformer----------->Dataset (Edits)
\
\
-------------------->Dataset (Inserts)
The CDC stage is hash partitioned on both input links heading into the CDC stage. For both links, the hash partition is defined on the same 2 fields (MBR_DEMO_KEY and ADDR_TYPE), in the same order, and with the same sorting parameters. The CDC stage has those same 2 fields defined as the change keys.
For a given member that I was researching, there is only one record in the After set with the given MBR_DEMO_KEY and likewise there is only one record in the Before set with the given MBR_DEMO_KEY. Both records have the same ADDR_TYPE. For some reason this single record is producing 2 output records from the CDC, an edit and a delete.
I understand that it could be possible for there to be a delete and an insert generated if, for example, the ADDR_TYPE was different for both records with the same MBR_DEMO_KEY. An edit and a delete makes no sense though.
I decided to go to the QA environment and see if I could test out a few changes that might fix this problem. The first thing I did was to run the code unaltered to replicate the problem. In our QA environment, the same code/data produced the desired result (just a single edit record).
What could possibly be causing this strange behavior in our production environment?
Any help would be greatly appreciated!