Update Strategy transformation


Specifies how each row updates target tables (insert, update, delete, or reject) based on an expression.

The Update Strategy transformation "tags" a row with the appropriate DML (data manipulation language) for the PowerCenter writer to apply to a relational target. Each row can be "tagged" with one of the following flags (the DD label stands for Data Driven). Appropriate SQL is submitted to the target database:

  • DD_INSERT for insert
  • DD_DELETE for delete
  • DD_UPDATE for update
  • DD_REJECT means the row will not have SQL written for it

    Examples / useful tips

  • IIF(row_exists_flag = 1, DD_UPDATE, DD_INSERT)
  • For the row tags DD_DELETE and DD_UPDATE, the table definition in a mapping must have a key identified otherwise the session created from that mapping will fail.
  • Rows tagged with DD_REJECT will be passed on to the next transformation or target and subsequently placed in the appropriate "bad file" if the "Forward Rejected Rows" attribute is "checked" (default). If the attribute is "un-checked" then reject rows will be skipped.
  • In some cases there may be a performance benefit to split a mapping with updates and inserts into two mappings and sessions: one with inserts and the other with updates.