CODEWITHSUNDEEP
informaticainformatica-powercenter
Thilak R
Thilak R

Reputation: 15

Flag Insert Update and delete source record in target using informatica

The requirement is to flag the incoming record from source to target as I,U,D, I-insert, D-Delete, u-Update. Source and target are various RDBMS system.

Source - Oracle Target - Greenplum

Using lookup on scd type1 we able to achieve I and U flag. Since the lookup is based on equi join of the primary key it was a challenge to flag the deleted record. Not able to use left or full outer join

Your inputs are highly appreciable

Upvotes: 0

Views: 2587

Answers (4)

Daniel Machet
Daniel Machet

Reputation: 630

Powerexchange is another informatica product which can read from the oracle redo logs to determine changes in the source database records in realtime. Such records can be stamped as I U or D in the DTL__CAPXACTION field. Powerexchange then connects to powercenter mappings through application multigroup source qualifiers.

If the solution they're after is more inline the assumptions the others have made I'd go with Maciej - nice, neat and easy

Upvotes: 0

Maciejg
Maciejg

Reputation: 3353

In addition to already mentioned solutions, to improve performance, you can flag everything as deleted before load and then stamp your inserts / updates accordingly. This way you can avoid comparison.

Upvotes: 1

Aswin
Aswin

Reputation: 111

As Thilak mentioned, source and target are two different RDBMS we wouldn't be able to reverse minus.

You have to handle in two step process, stage everything you receive from source in a staging table within oracle and then while loading delta compare against the staging table to decide I/U/D based on full outer join.

Assuming you always receive full load from source. If source is not sending full data you won't be able to track deletes.

Hope it helps!

Upvotes: 0

Ankur Shukla
Ankur Shukla

Reputation: 11

I am not sure if I understood the requirement correctly but assuming you are deleting records based on the data not coming from the source.

There could be some ways. You should have a key in source and target to identify the same record.

if above assumptions is correct follow this process :

  1. First use input data to update/insert the record.
  2. Use target load plan and run another mapping taking target DB as as source and do a minus operation with the incoming data.
  3. Delete the additional identified records.

Please correct if my understanding is wrong with regard to above question.

Upvotes: 0

Related Questions