1 Reply Latest reply on Apr 4, 2015 12:45 PM by Babak Fateh

    update strategy vs insert/update in sessions

    New Member

      Hi,

       

      Can anyone please tell me what is the significance of update strategy when we can do insert/update using session properties itself, need to know why Informatica has introduced a transformation called "update strategy".

       

      Thanks.

        • 1. Re: update strategy vs insert/update in sessions
          Babak Fateh Guru

          Short answer:

          Settings in the session ('Treat source rows as') is for all targets.

          Use UPS transformation in the mapping if you want to have different strategy (insert, update, ...) for the same target and also when you want to reject rows as there is no reject all at session level ('Treat source rows as'), it doesn't make sense if it were though.

           

          Here is a checklist. For more details take a look at Help.

           

          Choosing an update strategy requires setting the right options within a session and possibly adding Update Strategy transformations to a mapping. This section summarizes what you need to implement different versions of an update strategy.

           


          Only   perform inserts into a target table.

          When you configure the session, select Insert for   the Treat Source Rows As session property. Also, make sure that you select   the Insert option for all target instances in the session.


          Delete all   rows in a target table.

          When you configure the session, select Delete for   the Treat Source Rows As session property. Also, make sure that you select   the Delete option for all target instances in the session.


          Only   perform updates on the contents of a target table.

          When you configure the session, select Update for   the Treat Source Rows As session property. When you configure the update   options for each target table instance, make sure you select the Update   option for each target instance.


          Perform   different database operations with different rows destined for the same   target table.

          Add an Update Strategy transformation to the   mapping. When you write the transformation update strategy expression, use   either the DECODE or IIF function to flag rows for different operations   (insert, delete, update, or reject). When you configure a session that uses   this mapping, select Data Driven for the Treat Source Rows As session   property. Make sure that you select the Insert, Delete, or one of the Update   options for each target table instance.


          Reject   data.

          Add an Update Strategy transformation to the   mapping. When you write the transformation update strategy expression, use   DECODE or IIF to specify the criteria for rejecting the row. When you   configure a session that uses this mapping, select Data Driven for the Treat   Source Rows As session property.

           

          Take a look at Help for more details but be careful not get distracted by the details. The best way to learn is to try it and play with the options one at the time and examine the impact on the result. It's not that much complicated.

           

           

          babak