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.
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:
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.
Flexibility of using a conditional expression.
Performance gain over a stored procedure for deleting records.
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).
What can we do to improve this information (2000 or fewer characters)