Update Strategy Transformation

Update strategy transformation is an active and connected transformation. Update strategy transformation is used to insert, update, and delete records in the target table. It can also reject the records without reaching the target table. When you design a target table, you need to decide what data should be stored in the target.

When you want to maintain a history or source in the target table, then for every change in the source record you want to insert a new record in the target table.
When you want an exact copy of source data to be maintained in the target table, then if the source data changes you have to update the corresponding records in the target.

The design of the target table decides how to handle the changes to existing rows. In the informatica, you can set the update strategy at two different levels:

1. Within a session
2. Within a Mapping

1. Update Strategy within a session:
When we configure a session, we can instruct the IS to either treat all rows in the same way or use instructions coded into the session mapping to flag rows for different database operations.
Session Configuration:
Edit Session -> Properties -> Treat Source Rows as: (Insert, Update, Delete, and Data Driven). Insert is default. Specifying Operations for Individual Target Tables:

You can set the following update strategy options:
Insert: Select this option to insert a row into a target table.
Delete: Select this option to delete a row from a table.
Update: We have the following options in this situation:

•  Update as Update. Update each row flagged for update if it exists in the target table.
•  Update as Insert. Inset each row flagged for update.
•  Update else Insert. Update the row if it exists. Otherwise, insert it.

Truncate table: Select this option to truncate the target table before loading data.
2. Flagging Rows within a Mapping
Within a mapping, we use the Update Strategy transformation to flag rows for insert, delete, update, or reject.

Operation	Constant	Numeric Value
INSERT	DD_INSERT	0
UPDATE	DD_UPDATE	1
DELETE	DD_DELETE	2
REJECT	DD_REJECT	3

Update Strategy Expressions:
Frequently, the update strategy expression uses the IIF or DECODE function from the transformation language to test each row to see if it meets a particular condition.
IIF( ( ENTRY_DATE > APPLY_DATE), DD_REJECT, DD_UPDATE )
Or
IIF( ( ENTRY_DATE > APPLY_DATE), 3, 2 )

• The above expression is written in Properties Tab of Update Strategy T/f.
• DD means DATA DRIVEN

Forwarding Rejected Rows:
We can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them.
Steps:

1. Create Update Strategy Transformation
2. Pass all ports needed to it.
3. Set the Expression in Properties Tab.
4. Connect to other transformations or target.

Performance tuning:

1. Use Update Strategy transformation as less as possible in the mapping.
2. Do not use update strategy transformation if we just want to insert into target table, instead use direct mapping, direct filtering etc.
3. For updating or deleting rows from the target table we can use Update Strategy transformation itself.