Stored Procedure Transformation - as the name suggests is used to execute stored procedures through Informatica ETL. It can also be used to call functions to return calculated values. The Stored Procedures that are to be executed should be pre-built on the database which can be connected through Informatica.

This transformation provided by Informatica though rarely used, can prove to be a major stress buster at times of performance bottleneck due to major calculations.

When to use Stored Procedure Transformation

The Stored Procedure transformation introduced by Informatica has got multifaceted usability. Some of the main reasons are listed below:

  1. Complex calculations where Database Procedures perform better than using multiple transformations in Informatica.
  2. Re-usability of the Complex procedures in different ETLs in the form of Stored Procedure transformation.
  3. Breaking of complex calculations into multiple Stored Procedures and using those as Stored Procedure transformation in the same ETL. The output of one SP transformation can act as the input to the other.

Difference between Connected and Unconnected Stored Procedure Transformation and their mode of execution

Stored Procedure transformation is a passive transformation and like our Lookup transformation - can be either of connected or unconnected type.

Connected SP transformation

When the SP transformation exists along the line of data flow, it is called a Connected SP transformation. Given below is a typical example of a connected SP transformation. The Stored Procedure type for a Connected SP Transformation is always set to ‘Normal’. This means that the Procedure would be called for every row processed.

Connected Stored Procedure transformation

Unconnected SP transformation:

This type of SP transformation does not exist along the pipeline of the mapping and can be called as and when required. There are different scenarios of using an Unconnected SP transformation. A few of them are given below:

  1. Conditional execution of a Stored Procedure. Suppose we have a Procedure which predicts the RACE of an EMPLOYEE and our requirement says that we are required to predict the race of only those employees whose names starts with ‘A’. Then in such a case the ETL design would be something like :

UnConnected Stored Procedure transformation

The unconnected SP transformation is called in the expression using the following syntax:

IIF(SUBSTR(NAME,1,1) = 'A',:SP.PROC_PREDICTED_RACE(NAME,PROC_RESULT),'OTHERS')

The part ":SP.PROC_PREDICTED_RACE (NAME, PROC_RESULT)” calls the procedure. The input NAME from the EMP table acts as the input to the Procedure and the system variable PROC_RESULT becomes the output for the same.

In this type of Stored Procedure execution the Stored Procedure Type is also set to ‘Normal’.

  1. Execution of SP which does not have any input or output variable. There may be such requirement wherein we pre-populate the predicted RACE for all employees in a table using a procedure SH_CDW_PROC_PREDICTED_RACE. The design of the ETL in this case would be as shown below and we would call the procedure whenever required through an expression using the syntax: ‘:SP.PROC_PREDICTED_RACE()’

Normal Stored Procedure Transformation

In the above example the Stored Procedure Type would be ‘Normal’.

Stored Procedure Type: Calling of Stored Procedures during different times of session execution

Unconnected Stored Procedure Transformation also enables execution of the underlying procedure before the source reading starts or after source reading completes or before target loading or after target loading using the below types provided in the Stored Procedure Type:

  • Source Pre Load
  • Source Post Load
  • Target Pre Load
  • Target Post Load

In this case we don’t have to explicitly call the procedure in any expression or other transformation. The procedure would be executed according to the specified type.

SP Type

Once we specify any of the other Stored Procedure Type other than ‘Normal’, the ‘Call Text’ Attribute in the Properties tab gets enabled. Here we have to specify how the procedure has to be called. Given below is an example for the same:

SP Call Text

How to call a procedure residing in a different schema?

There is a scenario where our Informatica connects to the Target database through a user ‘USER_INFA’ and the procedure that we have to execute exists in another schema ‘EMP’. Now in such a case we can use our SP transformation to execute the procedure by making a very simple change. We have to prefix the procedure call with the schema name in the Call text attribute provided the user has execute privilege on the object. Please find below an example for the same:

SP Schema Name

How to execute multiple Stored Procedures one after another in a single ETL?

If the Stored procedure type is any of the available four types other than ‘Normal’, we have to use the Mapping Property – Stored Procedure Plan.

SP Execution Plan

If the Stored Procedure Type is ‘Normal’ then we have to call the procedures through an expression one by one.


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • How to Tune Performance of Informatica Aggregator Transformation

    Similar to what we discussed regarding the Performance Tuning of Joiner Transformation, the basic rule for tuning aggregator is to avoid aggregator transformation altogether...

  • Challenges of Informatica Partitioning with Sequencing

    In the previous article, we showed how surrogate keys can be generated without using Sequence Generator transformation. However, if Informatica partitioning is implemented for such cases, then since each partition pipeline will call the lookup...

  • CDC Implementation using Informatica Variable

    This article explains the Change Data Capture mechanism using Informatica Mapping Variable. We can use the Informatica Mapping Variable to extract the CDC data without using any other custom table. Here it goes.

  • Informatica Join Vs Database Join

    In this yet another "DWBI Concepts' Original article", we test the performance of Informatica PowerCentre 8.5 Joiner transformation versus Oracle 10g database join. This article gives a crucial insight to application developer in order to take...

  • Informatica Tuning - Step by Step Approach

    This is the first of the number of articles on the series of Data Warehouse Application performance tuning scheduled to come every week. This one is on Informatica performance tuning.

  • Implementing Informatica Incremental Aggregation

    Using incremental aggregation, we apply captured changes in the source data (CDC part) to aggregate calculations in a session. If the source changes incrementally and we can capture the changes, then we can configure the session to process those...

  • Implementing Informatica Persistent Cache

    You must have noticed that the "time" Informatica takes to build the lookup cache can be too much sometimes depending on the lookup table size/volume. Using Persistent Cache, you may save lot of your time. This article describes how to do it.

  • What is Active Lookup Transformation

    Informatica 9x allows us to configure Lookup transformation to return multiple rows. So now we can retrieve multiple rows from a lookup table thus making Lookup transformation an Active transformation type.

  • Calling C executable from Java Transform

    We are going to do is, to call C++ Executable from Informatica, using Passive Java Transform and capture the output of the C++ using Java and write the result to corresponding target column.

  • Informatica Performance Tuning - Complete Guide

    This article is a comprehensive guide to the techniques and methodologies available for tuning the performance of Informatica PowerCentre ETL tool. It's a one stop performance tuning manual for Informatica.