Update Strategy TF

Update Strategy Transformation:-

1)        Update Strategy TF: It is connected and an Active TF (Input # Output)

2)        Update Strategy TF is used to insert, update, and delete records in the target table.

3)        US TF can be used only if the Target is a Relational Table.

4)        Update Strategy Expression:

We have to flag rows by assigning the constant values using the update strategy expression. The update strategy expression property is available in the properties tab of the update strategy transformation.

e.g., IIF (condition, DD_INSERT, DD_UPDATE)

Or IIF (condition, 0, 1)

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.

Key Points:

1)                    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 we have to specify a primary key in the target definition in the mapping for update strategy transformation to work.

2)                    For the row tags DD_DELETE and DD_UPDATE, the table definition in a mapping must have a key identified otherwise the session       

created from that mapping will fail.

3)                    Rows tagged with DD_REJECT will be passed on to the next transformation or target and subsequently placed in the appropriate   

"bad file" if the "Forward Rejected Rows" attribute is "checked" (default). If the attribute is "un-checked" then reject rows will be

skipped. 

Performance Tuning on AggregatorTF:

1)        Unless it is really required, don’t go for the Updated Strategy TF.

2)        In the session label also we can update the table, instead of using USTF, if the normal updates required without any conditions.

Important Note on Designing Perspective:

Note: Since USTF is having performance issues, we are avoiding using it here.

Scenario: Need to update a Relational Table which is having a High volume data.

Assumption1: Both the Source and Target tables are belongs to same DB.

Assumption2: Both the Source and Target tables are belongs to different DB.

Approach:

                       

For Assumption1 use the Join query in the Source Qualifier.

For Assumption2 use the Joiner TF to join the Source and Target and follow as above.

1.Use Database JOIN to identify the records to be updated.

2.(By Using ROUTER TF)Insert the records into TEMP table, which is identified for UPDATE.

3.Use post session SQL on the TEMP Table to update the target table.