How to insert data and capture duplicates

[svhari76]

Hi

I am getting data in flat file and reading the data and loading the data to DB2 databse. My requiremnt is insert new o rupdate the existing data in db2, but at the same time write the updating record data to a file.

Can some please give idea, how i can achive this?

[anbu]

Use Change data capture stage to find inserts and updates

[vinothkumar]

Calculate CRC values using CRC32 function for all non key columns and do a lookup. If the values matches then there is no change and if the value changes then update in table and write in a file. Since it is a server job we cant have ChangeData capture stage.

[svhari76]

Can you please explain little in detail please? I am fairly new to DS.

[vinothkumar]

CRC32 is a function that generates a number based on its arugument. If the same argument comes in future, then same number will be generated.
For your case, Concatenate all your non key columns from source into a single value and pass this value to CRC32 function. Similarly calculate CRC value from lookup/target table also. If the Key column matches and CRC values doestnt match between source and lookup then it is an updated one.

This is not so efficient method. If anyone has easy and efficient way than this, it is appreciated.

[gateleys]

Concatenate all your non key columns from source into a single value and pass this value to CRC32 function. Similarly calculate CRC value from lookup/target table also. If the Key column matches and CRC values doestnt match between source and lookup then it is an updated one.

I would place some kind of a delimiter after each column value during the concatenation of non-key values. This will eliminate incorrect interpretations caused by concatenation. For example, if there are only 2 non-key columns with values, then it will avoid situations caused by values such as-

Code: Select all

1101   12
110     112

[svhari76]

Thanks for the explanation. I google little bit about the function.

Now my question is is it possible to pass the key values to Database from Transformer and get the row from table and in another transformer can i compare the rows with the function or can compare each field?