Update Strategy Transformation

Explain about Update Strategy Transformation

Update Strategy Transformation is an active transformation which flags the source records  for insert, update, delete and reject data driven operations
An Update Strategy Transformation functions as DML commands in SQL
An Update Strategy Transformation operates on target database account. The target table require a primary key to update the record

The Informatica Powercenter allows you to implement an Update Strategy Transformation in two ways

1.  Using Update Strategy transformation at mapping level
2.  Using target table properties at session level

The conditional Update Strategy expressions can be developed by using an Update Strategy transformation with following constants

DD_INSERT     -----> 0
DD_UPDATE   ----->  1
DD_DELETE    -----> 2
DD_REJECT    ----->  3

Conditional Expression

IFF(SAL>2000, DD_INSERT, DD_UPDATE)
          (or)
IFF(SAL>2000, 0,1)

The default update strategy expression is "DD_INSERT"

Use the following target table properties at session level to implement an update strategy

1. INSERT: It inserts the record in the target

2. UPDATE AS UPDATE: It updates the record in the target 

3. DELETE: It deletes the record from the target

4. UPDATE AS INSERT: For each update in the source it inserts a new record in the target

5. UPDATE ELSE INSERT: It updates the record if it exist in the target else inserts the record in the target