Update Strategy Transformation in Informatica

  • Updated
  • Posted in Programming
  • 4 mins read


Update technique transformation is an energetic and linked transformation. Update technique transformation is used to insert, replace, and delete data in the goal desk. It may also reject the data with out reaching the goal desk. When you design a goal desk, you’ll want to determine what information ought to be saved in the goal.

When you need to keep a historical past or supply in the goal desk, then for each change in the supply report you need to insert a brand new report in the goal desk.
When you need a precise copy of supply information to be maintained in the goal desk, then if the supply information modifications it’s important to replace the corresponding data in the goal.

The design of the goal desk decides the right way to deal with the modifications to present rows. In the informatica, you may set the replace technique at two totally different ranges:

  • Session Level: Configuring at session degree instructs the combination service to both deal with all rows in the identical means (Insert or replace or delete) or use directions coded in the session mapping to flag for various database operations.
  • Mapping Level: Use replace technique transformation to flag rows for inert, replace, delete or reject.

Flagging Rows in Mapping with Update Strategy:

You need to flag every row for inserting, updating, deleting or rejecting. The constants and their numeric equivalents for every database operation are listed beneath.

  • DD_INSERT: Numeric worth is 0. Used for flagging the row as Insert.
  • DD_UPDATE: Numeric worth is 1. Used for flagging the row as Update.
  • DD_DELETE: Numeric worth is 2. Used for flagging the row as Delete.
  • DD_REJECT: Numeric worth is 3. Used for flagging the row as Reject.

The integration service treats every other numeric worth as an insert.

Update Strategy Expression:

You need to flag rows by assigning the fixed numeric values utilizing the replace technique expression. The replace technique expression property is out there in the properties tab of the replace technique transformation.

Each row is examined towards the situation specified in the replace technique expression and a relentless worth is assigned to it. A pattern expression is present beneath:

IIF(department_id=10, DD_UPDATE, DD_INSERT)

Mostly IIF and DECODE capabilities are used to check for a situation in replace technique transformation.

Update Strategy and Lookup Transformations:

Update technique transformation is used principally with lookup transformation. The row from the supply qualifier is in contrast with row from lookup transformation to find out whether or not it’s already exists or a brand new report. Based on this comparability, the row is flagged to insert or replace utilizing the replace technique transformation.

Update Strategy and Aggregator Transformations:

If you place an replace technique earlier than an aggregator transformation, the way in which the aggregator transformation performs mixture calculations depends upon the flagging of the row. For instance, if you happen to flag a row for delete after which later use the row to calculate the sum, then the combination service subtracts the worth showing in this row. If it’s flagged for insert, then the aggregator provides its worth to the sum.

Important Note:

Update technique works solely when we’ve got a major key on the goal desk. If there is no such thing as a major key accessible on the goal desk, then it’s important to specify a major key in the goal definition in the mapping for replace technique transformation to work.

Recommended Reading:

Update Strategy Session Level Settings
SQL Transformation
Informatica Scenarios
Update Strategy Transformation in Informatica
Stored Procedure Transformation in Informatica
Lookup Transformation in Informatica

If you want this text, then please share it or click on on the google +1 button.

Leave a Reply