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:
Session Level:
Configuring at session level instructs the integration service to either treat all rows in the same way (Insert or update or delete) or use instructions coded in the session mapping to flag for different database operations.
Mapping Level:
Use update strategy transformation to flag rows for inert, update, delete or reject.
Flagging Rows in Mapping with Update Strategy:
You have to flag each row for inserting, updating, deleting or rejecting. The constants and their numeric equivalents for each database operation are listed below.
DD_INSERT: Numeric value is 0. Used for flagging the row as Insert.
DD_UPDATE: Numeric value is 1. Used for flagging the row as Update.
DD_DELETE: Numeric value is 2. Used for flagging the row as Delete.
DD_REJECT: Numeric value is 3. Used for flagging the row as Reject.
The integration service treats any other numeric value as an insert.
Update Strategy Expression:
You have to flag rows by assigning the constant numeric values using the update strategy expression. The update strategy expression property is available in the properties tab of the update strategy transformation.
Each row is tested against the condition specified in the update strategy expression and a constant value is assigned to it. A sample expression is show below:
IIF(department_id=10, DD_UPDATE, DD_INSERT)
Mostly IIF and DECODE functions are used to test for a condition in update strategy transformation.
Update Strategy and Lookup Transformations:
Update strategy transformation is used mostly with lookup transformation. The row from the source qualifier is compared with row from lookup transformation to determine whether it is already exists or a new record. Based on this comparison, the row is flagged to insert or update using the update strategy transformation.
Update Strategy and Aggregator Transformations:
If you place an update strategy before an aggregator transformation, the way the aggregator transformation performs aggregate calculations depends on the flagging of the row. For example, if you flag a row for delete and then later use the row to calculate the sum, then the integration service subtracts the value appearing in this row. If it’s flagged for insert, then the aggregator adds its value to the sum.
Important Note:
Update strategy works only when we have a primary key on the target table. If there is no primary key available on the target table, then you have to specify a primary key in the target definition in the mapping for update strategy transformation to work.
