Source based CDC based on date fields i am planning to extract data

[Former Member]

I am planning to extract data based on create date and update date.

if it is based on created dat, then i append/insert records to target table, if it is based on updated_date, then will do an update to teh exiting row in target table.

I am confused at one point, how can i update, can i use map_operation transform object to insert or update rows in the target table.

If i use map_op, do i need to use table _comparison transform object?

Since i already have my extracted data for new rows and updated rows, why do i need a table comparison that is where i am confused.

Please kindly provide  me a logic how to handle scenario.

Thanks a lot for the helpful info.

Roger

[Former Member · Accepted solution]

Hi,

Check this http://wiki.sdn.sap.com/wiki/display/EIM/Extractors+-+Source-Based+CDC

Arun

[Former Member · Accepted solution]

Arun, Thanks a lot for teh link, My data is not SAP ecc based but both source and target schemas are based on oracle.

If you look at teh below picture, this is the same way i will try. I will extract just teh updated rows from source based on Updated_date. and connect to Map_Op (Update to Update option).

But how will the Map_Op figure out what is the Primary key ids. when i am not using Table _comp. within table comp transform i have Primary keys option where i pick columns.

Does Map_Operation transform object will it figure out automatically or is there a way i can put those details. I have almost 150 columns within the table.

Is there a way i can mention what are teh primary key columns, based on that the update automatically takes place on the target table.

Thank you very much for the helpful info.

[Former Member · Accepted solution]

Hi,

Since you have to update the target table from normal, use only map operation where you mention normal to update and turn on update key column in the target table.

Arun

[Former Member · Accepted solution]

Arun,

Thanks for teh response, What happens when i turn on teh property update key column under options for target table.

Does that mean it only does updates?

How about if i defined primary key columns within my query transform object prior to the Map_op object will that also be treated as the primary keys for updates to teh target table via Map_Op Object?

Thanks a lot for all your helpful details.

regards,

Roger

[Former Member · Accepted solution]

Guy,

1) What happens when i turn on teh property update key column under options for target table.

You don't need to use 'Update key columns' unless primary key at the source is changed. This will basically change the PK value to a new one thats in the source.

2) Does that mean it only does updates?

Refer above answer

3) How about if i defined primary key columns within my query transform object prior to the Map_op object will that also be treated as the primary keys for updates to teh target table via Map_Op Object?

As mentioned in the link i provided earlier, yes you have to mention a primary key before the map_CDC transform.

Hope this helps.

Arun