PowerCenter - Update Strategy (Insert, Update, Delete)

1 - About

The update strategy determines whether to insert, delete, update, or reject records.

In PowerCenter, you set it 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.

  • Within a mapping. Within a mapping, you use the Update Strategy Transformation to flag rows for insert, delete, update, or reject.

You can also use the Custom transformation to flag rows for insert, delete, update, or reject.

3 - Constant flags record

Constant Integer litteral Description
DD_DELETE 2 Delete the record
DD_INSERT 0 Insert the record
DD_REJECT 3 Used to filer or validate data. The record is skipped and written to the session reject file
DD_UPDATE 1 Update the record

4 - Example

The following statement marks items with an ID of 1001 for deletion and all other for insertion

IFF ( ITEM_ID = 1001, DD_DELETE, DD_INSERT )

5 - Task parameters

5.1 - Properties

5.2 - Mapping

You can set session-level target properties to specify how the Integration Service inserts, updates, and deletes rows.

Property Description Default
Update (as Update) Integration Service updates all rows flagged for update. Enabled
Update (as Insert) Integration Service inserts all rows flagged for update. Disabled
Update (else Insert) Integration Service updates rows flagged for update if they exist in the target, then inserts any remaining rows marked for insert. Disabled
Delete Integration Service deletes all rows flagged for delete. Disabled
Truncate Table Integration Service truncates the target before running the session. Disabled

6 - Debbuger

7 - Documentation / Reference

  • PowerCenter Workflow Basics Guide - page 96 (Target Properties)
dit/powercenter/update_strategy.txt ยท Last modified: 2017/09/06 19:56 by gerardnico