Not getting desired result when using Change Capture stage

Sandeepch19 · Post by **Sandeepch19** » Mon Oct 27, 2014 7:45 am

Hi,

We are using Change Capture stage to indentify whether a record is an insert or an update based on a key column(one key column) and multiple values(15 columns). I am not getting desired results as everytime records are going for updates. Not all records are going for updates but some records. I did verify the values field by field for some records and didn't find any change or mismatch in the values. I am not sure whats happening. my job design is as below.

Code: Select all

                                                                  TRFM(BFORE)
                                  ODBC Connector        /        \                               
DB2---Trfm-----------|              |                   /           \                           /
                        Funnel-----LKP----LKP----/              \                         /
DB2---srt---Trfn-----|                       |                       CC stage---TRFM-
                                                     DB2                         |                    \
                                                                                    |                      
                                                               ODBC -----TRFM
                                                Connector(AFTER)

ray.wurlod · Post by **ray.wurlod** » Mon Oct 27, 2014 4:53 pm

Please tidy up your ASCII art using the Preview facility.

Then tell us whether the inputs to the Change Capture stage are identically sorted and partitioned.

chulett · Post by **chulett** » Mon Oct 27, 2014 5:28 pm

Sorry, I added the code tags so at least it was semi-readable but wasn't in the mood for the tedious 'tidy up' part.

vinothkumar · Post by **vinothkumar** » Tue Oct 28, 2014 12:33 am

Identify any particular record and try with combinations of different key columns in change capture instead of 15 at a time. It will help to find the column having mismatch.

priyadarshikunal · Post by **priyadarshikunal** » Mon Nov 03, 2014 3:21 am

Also if there are duplicates in the after dataset, first record encountered will be marked as update and second row will be marked as Insert. So make sure you do not have duplicates in there or they are properly handled.