PowerCenter - Update Strategy (Insert, Update, Delete)

Card Puncher Data Processing

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.

Powercenter Update Strategy

  • 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.

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

  • PowerCenter Workflow Basics Guide - page 96 (Target Properties)





Discover More
Card Puncher Data Processing
PowerCenter - Custom Transformation

You can use the Custom transformation to define the update strategy (i.e. flag rows for insert, delete, update, or reject)
Powercenter Pre Session Variable Assignment
PowerCenter - Mapping (Parameter|Variable)

When you use a mapping parameter or variable in a mapping, first you declare the mapping parameter or variable for use in each mapplet or mapping. Then, you define a value for the mapping parameter or...
Powercenter Transformation Create
PowerCenter - Transformations

A transformation is a part of a mapping that generates or modifies data. Every mapping includes a Source Qualifier transformation, representing all data read from a source and temporarily stored by the...



Share this page:
Follow us:
Task Runner