Thursday, February 25, 2016

Update Strategy Transformation In Informatica

Update strategy Transformation

Ø  This transformation will be used to flag the incoming records destined for the same target table for different database operations(i.e. Insert , update, delete , Reject)
Ø  There are two levels at which this property can be set 1. At mapping level 2. At session level
1. At Mapping Level
·         At mapping level we have to use update strategy transformation and flag the records for insert or update based on lookup return values . For each target table we have to specify target update strategy options. When we have an update strategy in mapping level then the property 'Treat Source Rows as' at session level will be DataDriven by default.
·         Record rejection is only possible with usage of update strategy transformation in mapping.
·         Use Decode or IIF function to define the condition in update strategy to flag the records for the operations insert, delete, update and reject.
2. At Session Level
·         The second option is that we can set the property at session level Treat Source Rows as property as Insert, update, delete. We cannot implement reject records using this property
Ø  Whether we use Mapping level or Session level settings to flag the records for different database operations we have to use the below target options for each target in the mapping.
o   Insert
o   Update
§  Update As Update
§  Update Else Insert
§  Update As Insert
o   Delete
Ø  When you are implementing update else insert and update as insert target options you have to check both Insert and the update option as well to perform the operation for individual targets
Operation
Constant
Numeric Value
Insert
DD_INSERT
0
Update
DD_UPDATE
1
Delete
DD_DELETE
2
Reject
DD_REJECT
3
Ø  In both the above implementation options, for each target table a primary key is required for implenting the operations update, delete and reject.
Ø  We can use update strategy before and after aggregator transformation.
Rejected Records at Update Strategy:
·         You can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them.
·         By default, the Integration Service forwards rejected rows to the next transformation.
·         The Integration Service flags the rows for reject and writes them to the session reject file.
·         If you do not select Forward Rejected Rows, the Integration Service drops rejected rows and writes them to the session log file.

Ø  You can use IIF or DECODE transformation functions to form update strategy and flag each incoming record from source to one database operation.

Examples update strategy expressions are given below:

·         IIF( ( ENTRY_DATE > APPLY_DATE), DD_REJECT, DD_UPDATE )
·         IIF(ISNULL(DEPTNO),0,1)

·         DECODE(FLAG_OPERATION,0,DD_INSERT,1,DD_UPDATE,2,DD_DELETE,3,DD_REJECT)

No comments:

Post a Comment