Update Strategy

In PowerCenter, you set the update strategy at two different levels:

¨ Within a session. When you configure a session, you can instruct the Integration Service to either treat all rows in the same way (for example, treat all rows as inserts), or use instructions coded into the session mapping to flag rows for different database operations.

When you configure a session, you have several options for handling database operations, including updates.

The following table displays the options for the Treat Source Rows As setting:

Setting	Description
Insert	Treat all rows as inserts. If inserting the row violates a primary or foreign key constraint in the database, the Integration Service rejects the row.
Delete	Treat all rows as deletes. For each row, if the Integration Service finds a corresponding row in the target table (based on the primary key value), the Integration Service deletes it. Note that the primary key constraint must exist in the target definition in the repository.
Update	Treat all rows as updates. For each row, the Integration Service looks for a matching primary key value in the target table. If it exists, the Integration Service updates the row. The primary key constraint must exist in the target definition.
Data Driven	Integration Service follows instructions coded into Update Strategy and Custom transformations within the session mapping to determine how to flag rows for insert, delete, update, or reject. If the mapping for the session contains an Update Strategy transformation, this field is marked Data Driven by default. If you do not choose Data Driven when a mapping contains an Update Strategy or Custom transformation,the Workflow Manager displays a warning. When you run the session, the Integration Service does not follow instructions in the Update Strategy or Custom transformation in the mapping to determine how to flag rows.

Once you determine how to treat all rows in the session, you also need to set update strategy options for individual targets. Using Session-Level Target Properties with Source Properties .

Insert

update as update ---->These Three Defaultly Selected Values, as In The Above image.

Delete

if you know in advance that all your rows will be INSERT rows, then select 'Treat Source Rows as' = insert, and only insert rows will be created, right? (Same for all updates, all deletes).

In the case of 'inserts only', it does not matter what you select for updates or deletes (uncheck them both, or not) because your mapping is creating nothing but inserts and those switches are ignored. In the case of all deletes, the scenario is the same.

If you are creating updates only, you have the choice on how the Server should process the updates . If you select 'updates as updates', the Server leaves your updates as normal UPDATE DMLs. Like all inserts/all deletes, only that one switch(Update as Update) is important - should they or should they not get released? Similarly, if you have none of the 3 update options checked, the transactions die.

if you select 'update as insert' (rarely used) switch them all to inserts. NOW the power of the insert switch matters ALSO. If you have the Server convert them all to inserts - but uncheck insert - they all die. And nothing gets loaded. Again, not that you would do that and in most cases you could just set them as inserts to begin with.

If you select Update Else Insert -- the same 2 switches matter again. The Server creates an UPDATE DML transaction. If the database can locate it, it does the update. If not, the transaction goes back into the session engine.

The Server switches it to an INSERT DML statement, right? Again, if you unchecked the insert option, the new INSERT statement dies. It does not get released to the writer. This of course is the laborious overhead Andrew mentioned. Nice for testing but in production, lookups in advance of all this are more efficient.

¨ Within a mapping.

Update Strategy Transformation

It simply allows you to write logic for the Server to do a row-by-row analysis to distinguish which rows should be set as INSERTS, UPDATES, DELETES (or ignored), based on the data on the row. Use the 'Treat Source Rows as' option = Data Driven, when using it.

DD_INSERT OR 0

DD_UPDATE OR 1

DD_DELETE OR 2

DD_REJECT OR 3

if you do not choose Data Driven when a mapping contains an Update Strategy or Custom transformation,the Workflow Manager displays a warning. When you run the session, the Integration Service does not follow instructions in the Update Strategy or Custom transformation in the mapping to determine how to flag rows.