Table of Contents

PowerCenter - Update Strategy (Insert, Update, Delete)

About

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

In PowerCenter, you set it at two different levels:

Powercenter Update Strategy

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

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

Powercenter Task Attribute

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

Powercenter Target Properties Load Strategy

Debbuger

Powercenter Debugger Data Display Row Type

Documentation / Reference