Stored Procedure Transformation

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.

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 :

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()’

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.

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:

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:

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.

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