Informatica PowerCenter Constraint Based Load Ordering

  Mapping Tips

Constraint based loading technique is available in Informatica PowerCenter since couple of its prior versions. This PowerCenter feature will let you load multiple tables in a single session, which is having database level primary key - foreign key constraint or parent - child relation. In this article let's see what is needed to set up a session for constraint based loading.

What is Constraint Based Loading

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

What is Needed to Setup Constraint Based Loading

There are couple of rules for setting up Constraint-based loading in a particular session and lets see those in detail.

1. Key Relationships.
2. Active Source.
3. Target Connection Groups.
4. Treat Rows as Insert.

1. Key Relationships.

When target tables have no key relationships, the Integration Service does not perform constraint-based loading. Similarly, when target tables have circular key relationships, the Integration Service reverts to a normal load. It reverts to a normal load.

When the target definition is imported into the Target Designer, PowerCenter will detect the Key Relationship defined on the database.  You can manually create the relationship with in the Target Designer  as well.

2. Active Source.

Both the parent and child table need to be mapped from a single active source such as Source Qualifier, Aggregator, Normalizer etc. When target tables receive rows from different active sources, the Integration Service reverts to normal loading for those tables.

In the below mapping, both targets are getting rows from the active transformation Normalizer 

3. Target Connection Groups.

The Integration Service enforces constraint-based loading for targets in the same target connection group. If the tables with the primary key-foreign key relationship are in different target connection groups, the Integration Service cannot enforce constraint-based loading when you run the workflow.

To verify that all targets are in the same target connection group, complete the following tasks.

• All targets are in the same target load order group and receive data from the same active source.
• Use the default partition properties and do not add partitions or partition points.
• Define the same target type for all targets in the session properties.
• Define the same database connection name for all targets in the session properties.
• Choose normal mode for the target load type for all targets in the session properties.

Below Image shows the target are in the same load order group.  You can verify the load order from the mapping designer.

Other required properties for the Constraint Based loading is highlighted in the below image. We can set these properties in the session level from workflow manager.

4. Treat Rows as Insert.

Use constraint-based loading when the session option Treat Source Rows As is set to Insert. You might get inconsistent data if you select a different Treat Source Rows As option and you configure the session for constraint-based loading.

Set this property in the session level as shown in below image.

How to Setup Constraint Based Loading

Now we now the requirements for setting up a session for constraint based target loading, Lets see how we configure the session.

To enable constraint-based loading, you need to set the "Constraint bases load ordering" property as shown in below image.

That is all we need to setup a session for constraint based target loading.

If you go through the session log file, you can see the details of the constraint based Load Ordering as in below image.

Hope you enjoyed this article and will be helpful in your live projects. Please feel free to leave a comment or question below, we are more than happy to help.

Share with your friends