Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(2 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

MAPPING TEMPLATES: Using Update strategy to delete rows



Content

This mapping template uses an Update Strategy Transformation to flag records for deletion from the target table.

When data in a target table is to be deleted, it is necessary to consider various delete strategies and adapt the best strategy for a business scenario. The delete strategy should be efficient, easy to implement, and flexible enough to allow for easy modification of the delete criteria in the future.

There are many methods of deleting records from a target table. This mapping template illustrates a method of using an Update Strategy transformation in a PowerMart / PowerCenter mapping to flag records that need to be deleted from the target table.

Implementation Guidelines

The mapping has an update strategy transformation just before the target to flag individual rows for deletion. In the Properties tab of the Update Strategy transformation, the 'Update Strategy Expression' property is set to DD_DELETE or the Numeric Value 2, to mark the rows for deletion. Using DD_DELETE makes it easier to debug mappings than using the numeric 2. The following are a few points to keep in mind while using update strategy to delete records:

  • Make sure you select the 'Delete' option in the target properties in a session (This option is selected by default when you create a session).
  • Rows can be deleted from a target table using various methods. For example, you can set the session property 'Treat Rows As' to'Delete' to mark all rows for deletion. But this approach makes it impossible to flag only a few rows for deletion based on a criterion.
  • You can also use a stored procedure to perform the deletion and call the stored procedure from within a PowerMart/PowerCenter mapping instead of using an Update Strategy transformation. However using an Update Strategy transformation will result in better performance over using stored procedures.
  • Use a conditional expression to mark only selected rows for deletion. For example, you can use an expression such as IIF(STORE_ID < 100, DD_DELETE, DD_REJECT) to delete only the rows that have STORE_ID less than 100.
  • If you use an Aggregator transformation after the update strategy transformation, beware of the aggregator behavior. For example, if the row marked for deletion is used in a 'Sum' operation, the aggregator actually subtracts the value appearing in this row.
  • Make sure that the user running this session has privileges to delete records from the target table in the database.
  • Make sure that there are no constraints at the database level on this table that prevents records from being deleted.
  • If you are deleting a large number of rows, then you should check to make sure that the rollback segment is big enough to hold that many rows.

If the database does page level locking and your session tries to delete a row from a page that is already locked, the session will fail.

Pros

Flexibility of using a conditional expression.

Performance gain over a stored procedure for deleting records.

Cons

The primary key constraint must exist in the target definition in the repository. If the primary key does not exist in the target table, then you have to create one in the designer so that the mapping has a where clause for the delete query (Note: it is not necessary to create the key in the underlying database).

More Information


Reference


Attachments


Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:

Last Modified Date: 8/11/2010 6:28 AM ID: 16315
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)