In the Workflow Manager, you can specify constraint-based loading for a session. When you select this option, the PowerCenter Integration Service orders the target load on a row-by-row basis. For every row generated by an active source, the PowerCenter Integration Service loads the corresponding transformed row first to the primary key table, then to any foreign key tables.

The

Constraint based load ordering

attribute applies only to insert operations. Change data normally contains a mixture of insert, update, and delete operations. PowerCenter provides a custom property to enable complete constraint-based loading.

When you enable complete constraint-based loading, change data is loaded to targets in the same Transaction Control Unit (TCU) by using the row ID assigned to the data by the CDC Reader. As a result, data is applied to the targets in the same order in which it was applied to the sources. The following message will be issued in the session log to indicate that this support is enabled:

To enable complete constraint-based loading, specify

FullCBLOSupport=Yes

in the

Custom Properties

attribute on the

Config Object

tab. This property can also be set in the PowerCenter Integration Service, which makes it applicable to all workflows and sessions that use that the PowerCenter Integration Service.

If you use complete constraint-based loading, your mapping must not contain active transformations which change the row ID generated by the CDC Reader. The following transformations change the row Id value: