Skip navigation

Log In

Forgot your password?
Don’t have an account? Sign Up

Sign Up

Access our rich network of apps, trials, and services.

Start the registration process by entering your email address below.

Already have an account? Log In

PowerCenter – Stored Procedure Example

Free download

Posted by: Informatica Enterprise Data Integration

Informatica PowerCenter mapping example to demonstrate the use of Stored Procedure transformation.

Overview


Database administrators create stored procedures to automate tasks that are too complicated for standard SQL statements. A Stored Procedure transformation is an important tool for leveraging existing stored procedures from within PowerCenter.

 

Not all databases support stored procedures, and stored procedure syntax varies depending on the database. You might use stored procedures to complete the following tasks:

  • Check the status of a target database before loading data into it.
  • Determine if enough space exists in a database.
  • Perform a specialized calculation.
  • Drop and recreate indexes.

 

You might use a stored procedure to perform a query or calculation that you would otherwise make part of a mapping. For example, if you already have a well-tested stored procedure for calculating sales tax, you can perform that calculation through the stored procedure instead of recreating the same calculation in an Expression transformation.

As a best practice, do not run unnecessary instances of stored procedures as it can impact performance.

 

Each time a stored procedure runs during a mapping, the session must wait for the stored procedure to complete in the database. You have two possible options to avoid this:

  • Reduce the row count. Use an active transformation prior to the Stored Procedure transformation to reduce the number of rows that must be passed the stored procedure. Or, create an expression that tests the values before passing them to the stored procedure to make sure that the value does not really need to be passed.
  • Create an expression. Most of the logic used in stored procedures can be easily replicated using expressions in the Designer.

 

The download will help you understand the Stored Procedure transformation functionality by providing a sample stored procedure and a PowerCenter workflow.

Specifications

  • PowerCenter version 8.x, 9.1 and 9.5

Resources

Comments Comments (2)

Related