Update Strategy TransformationUpdate Strategy Transformation in Informatica , is a connected and active transformation. Till now you have seen that, most of the transformation in Informatica were use to insert the records. Update Strategy transformation let you insert, update , delete data on target side . It can also reject the incoming data depending upon your condition.For example : when a customer is changing address , you can use this update strategy transformation to update data in your Customer Tables. In below article we will go through the properties of Update Strategy Transformation. We will also discuss the steps of adding /configuring Update Strategy transformation in Informatica Mapping. We will also check some performance tuning related guidelines for Update Strategy Transformation in Informatica.

Business Scenarios of Update Strategy Transformation:

Update Strategy Transformation is mainly used in Datawarehouse project to update the Target Database with the help of some Delta records (Changed data which need to be updated).

Is Update strategy Transformation Active or Passive ?

Update Strategy Transformation is an Active transformation as it can change the no of rows by rejecting some of them .

Is Update strategy Transformation Connected or Unconnected ?

Update strategy Transformation is a Connected Transformation.

Properties of Update Strategy Transformation in Informatica

  • Active /Passive : Update Strategy Transformation is an Active transformation as it can also reject rows.
  • Connected/Unconnected Transformation : Update Strategy Transformation is a connected Transformation.
  • Types of ports in Update Strategy Transformation:
    • Input : For reading input Data
    • Output : For providing output Data

Updating Rows in Informatica:

You can define update operation on below two levels:

Update Strategy on Session level:

We can instruct Informatica Service to treat all rows in the same way or use same instruction by configuring Target mapping at session level. You can edit the update strategy of Target on session level by following below steps

  • Create a session for the current mapping
  • Now Edit the session by clicking on Session -> edit
  • Go to Properties tab and set the properties of Target as mentioned below

Set below option as per your need.

  • Insert: Select this option to insert all rows into a target table.
  • Delete: Select this option to delete a rows from a table.
  • Update: Under Update set below sub -option
    • 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.

Update Strategy on Mapping level: You can flag a row (for insert , update, delete or Reject depending upon some condition) by adding “Update strategy Expression” in the properties tab of Update Strategy Transformation in a Mapping. Below Flags can be set in Update strategy Expression.

FlagNumeric ValueType Operation
DD_INSERT0Insert row
DD_UPDATE1Update row
DD_DELETE2Delete row
DD_REJECT3Reject row

We normally define some condition in update strategy expression to perform any of above action ,


Components of Update Strategy Transformation in Informatica

When you configure a Update Strategy transformation, define the following components:

  • Transformation tab: You can rename the transformation and add a description.
  • Properties tab: You can specify the tracing level, Forward Rejected Rows, and Most Importantly Update Strategy Expression (Type of Action to be taken)
  • Ports tab: Once port under Update Strategy is created , you will see input and output port for Update Strategy.

Configuring Update Strategy Transformation Ports:

You can create input ports by copying them from another transformation or by manually creating them in Ports tab.

Port Details:

  • Port name: Add the name of the port.
  • Datatype, precision, and scale: Configure the datatype and set the precision and scale for each port.
  • Default Value: In case port data is null , you can define its default value..

Configuring Update Strategy Transformation Properties:

Modify the Update Strategy Transformation properties by clicking on the Properties tab.

Update Strategy ExpressionsType of Operation(Insert, Update, Delete) need to be done. Normally we add IIF , DECODE function to figure out the operation to be done.
Forwarding Rejected RowsYou 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
Tracing LevelAmount of detail displayed in the session log for this transformation. The options are Terse, Normal, Verbose Data, and Verbose Initialization.

Must Read : Learn more about Router Transformation als0

How to use Update Strategy transformation in Informatica:

Use the following procedure to create a Update Strategy transformation in Informatica mapping.

Steps to create an Update Strategy transformation:

  • In the Mapping Designer, open a Mapping.
  • Click Transformation > Create. Select Update Strategy transformation.
Transformation Creation Step 1

Transformation Creation Step 1

Update Strategy Transformation Step 2

Update Strategy Transformation Step 2

  • Update Strategy Transformation Step 3

    Update Strategy Transformation Step 3

    Enter a name and click Done.

  • You can create ports in Update Strategy transformation by selecting and dragging it from input transformation to Update Strategy transformation.You can manually create input ports on the Update Strategy Ports tab by double clicking on it.
    Update Strategy Transformation Step 4

    Update Strategy Transformation Step 4

  • Click the Transformation tab and configure transformation properties.

Update Strategy Transformation Step 5

  • Enter an update strategy expression to flag rows as inserts, deletes, updates, or rejects
  • Update Strategy Transformation Step 6

    Update Strategy Transformation Step 6

Note: See that ‘Forward Rejected Rows‘ is checked. So that Forward Rejected Rows to Next Transformation/Written to Rejected/Bad File. If not it will write it to session log file.

  • Click OK.
  • Connect output ports of Update Strategy Transformation to target

Example of Update Strategy Transformation:

A well detailed example of Update Strategy Transformation can be found here.

More about Update Strategy Transformation:

Update Strategy Transformation rules and Guidelines:

  • Update strategy need primary key on the target table to work.You should also check if primary key is defined in Target Definition or not.
  • Try to use Update strategy as less as possible because of it slow performance
  • If you are just inserting records , then update strategy transformation should be skipped. You can us direct mapping.
  • To check existing records , you can use Lookup and set the Operation Flag accordingly in Update strategy Expression.

So , in this article we have gone through properties of Update Strategy Transformation in Informatica  ,component/Properties of Update Strategy Transformation , scenarios where to use it , steps to create it and performance related guidelines. Hope you enjoyed the article. Please feel free to give your feedback.

Alisha Lamba

About teck tricks

My name is Alisha Lamba , a 24 yr old Software Techie . I love to write article on various technology used in IT Industry. I am, great music Fan, and love watching movies. I also love to watch Cricket

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation