MAPPING HELP

[ayirus]

This is what I am trying to do - I don't know which transform to use - I tried LOOKUP as well UPDATE - but could not get to work.

Have two separate source text files - 1) USA and 2) Other
Only one target - ITEMMASTER (DB2 table)

1) Have a straightforward mapping that reads USA and writes it into ITEMMASTER USA -> ITEMMASTER

2) 2nd mapping, I want to read from Other and write the record into ITEMMASTER only if does not already exist in ITEMMASTER - so check ITEMMASTER, if exists, then ignore...if not insert

Both USA and Other have the same data fields, formats, etc..

ITEMMASTER also has the same structure as USA/Other

ITEMMASTER has an explicit plicit primary key defined (composite columns)

I tried UPDATE STRATEGY TRANSFORMATION - The execution identifies the "existing" records and mark them for rejection, but did not succeed with the insertion of new records - complained about the primary key

Thanks in advance.

 

[blom0344]

There are several solutions and INFA's documentation covers them quite extensively.

1. Approach 1

set 'treat source rows as' to 'update'.
in mapping do not use an update strategy
use 'update else insert' in the session for writing to target.

This is a pretty slow/inefficient way, but if you have few source rows, then this is no problem.

2. Approach 2

Cache your target in a lookup
Flag your source rows from 'other' as either dd_update or dd_insert.
Use update strategy in mapping
Fastest would be to split inserts/updates into 2 mappings.

Ties Blom