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.
Articles Related
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 |
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 )
Task parameters
Properties
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 |
Debbuger
Documentation / Reference
- PowerCenter Workflow Basics Guide - page 96 (Target Properties)