Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Print Bookmark Alert me when this article is updated


FAQ: How are PowerCenter connections for Lookup Transformation and Stored Procedure maintained?

When a mapping contains a Lookup transformation (on a database table) or a Stored Procedure, the DTM (Data Transformation Manager which is created when a PowerCenter Integration Service executes a session) process would open connections as follows:

  1. If same relational connection (created in Workflow Manager) is used for the Lookup and the Stored Procedure transformation, then only one connection would be opened and used by both the Lookup transformation and the Stored Procedure transformation.
    If the Lookup transformation appears before Stored Procedure in the mapping, the DTM will open a connection for Lookup Transformation. It will re-use the same connection for Stored Procedure. This connection will be open till session completion.
  2. If the Lookup transformation and the Stored Procedure transformation use different relational connections, then the DTM will open one connection for each transformation. This connection will remain open until session completion.
The PowerCenter Server maintains a database connection pool for connections made to databases when the session contains Stored Procedures or Lookups. You can optionally set the MaxLookupSPDBConnections parameter to limit connections when you configure the PowerCenter Integration Service.
The PowerCenter Integration Service allows unlimited number of connections to Lookup or Stored Procedure databases. If you do not have permission for a certain number of connections that a session requires, then the session fails to execute.

Pre-Session, Post-Session and Load Stored Procedures, if they have identical connection attributes (such as same connection name), the consecutive Stored Procedures reuse a connection.


The DTM process maintains a pool of connections for Lookups and Stored Procedures. If a connection is opened for a Lookup or a Stored Procedure it is not closed until the session completes. Hence, after Lookup or Stored Procedure finishes its task, the connection is returned to the pool. It can be reused for other Lookup or Stored Procedure when same connection value is specified in that Lookup/Stored Procedure as previous one.

More Information
​The opening and closing connection works differently for reader (source) and writer (target) thread. For a session, a reader object holds the connection as long as it needs to read the data from the source tables. A writer object holds a connection for as long as it needs to write data to the target tables.​

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



Last Modified Date:6/5/2017 1:58 AMID:18388
People who viewed this also viewed


Did this KB document help you?

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