Stored Procedure T/R

STORED PROCEDURE TRANSFORMATION 

1. WHAT IS A STORED PROCEDURE TRANSFORMATION? 
2. HOW MANY TYPES OF STORED PROCEDURE TRANSFORMATION ARE THERE? 
3. HOW DO WE CALL AN UNCONNECTED STORED PROCEDURE TRANSFORMATION? 
4. HOW DO WE SET THE EXECUTION ORDER OF PRE-POST LOAD STORED PROCEDURE? 
5. HOW DO WE SET THE CALL TEXT FOR STORED PROCEDURE TRANSFORMATION? 
6. HOW DO WE RECEIVE OUTPUT/RETURN PARAMETERS FROM UNCONNECTED STORED PROCEDURE? 

105. What is a Stored Procedure Transformation?

  • Stored Procedure is a Passive transformation used to execute stored procedures pre-built on the database through Informatica ETL.
  • It can also be used to call functions to return calculated values.
  • A Stored Procedure transformation is an important tool for populating and maintaining databases.
  • Database administrators create stored procedures to automate time-consuming tasks that are too complicated for standard SQL statements.

106. How many types of Stored Procedure transformation are there?

  • There are two types of Stored Procedure transformation based on calling, Connected and Unconnected.
  • Based on the execution order they can be classified as Source Pre Load, Source Post Load,Normal, Target Pre Load and Target Post Load.
  • Normal Stored Procedure transformation can be configured as both connected and unconnected whereas Pre-Post Load Stored Procedures are unconnected ones.

107. How do we call an Unconnected Stored Procedure transformation?
The unconnected Stored Procedure transformation is called from expression transformation using the :SP.<Stored_Procedure_Name>(Argument1, Argument2).
Conditional execution of a Stored Procedure is possible using Unconnected Stored Procedure unlike the connected one.
108. How do we set the Execution order of Pre-Post Load Stored Procedure?
We set the execution order using the Stored Procedure Plan from the mapping property.
109. How do we set the Call Text for Stored Procedure transformation?
Once we specify the 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 along with arguments to be passed. E.g. <Stored_Procedure_Name>(Argument1, Argument2).

110. How do we receive output/return parameters from Unconnected Stored Procedure?

Configure the expression to send any input parameters and capture any output parameters or return value You must know whether the parameters shown in the Expression Editor are input or output parameters. You insert variables or port names between the parentheses in the exact order that they appear in the stored
procedure itself. The datatypes of the ports and variables must match those of the parameters passed to the stored procedure.
For example, when you click the stored procedure, something similar to the following appears:
:SP.GET_NAME_FROM_ID()
This particular stored procedure requires an integer value as an input parameter and returns a string value as an output parameter. How the output parameter or return value is captured depends on the number of output parameters and whether the return value needs to be captured.
If the stored procedure returns a single output parameter or a return value (but not both), you should use the reserved variable PROC_RESULT as the output variable. In the previous example, the expression would appear as:
:SP.GET_NAME_FROM_ID(inID, PROC_RESULT)
InID can be either an input port for the transformation or a variable in the transformation. The value of PROC_RESULT is applied to the output port for the expression.
If the stored procedure returns multiple output parameters, you must create variables for each output parameter.
For example, if you created a port called var OUTPUT2 for the stored procedure expression, and a variable called var OUTPUT1, the expression would appears as:
:SP.GET_NAME_FROM_ID (inID, varOUTPUT1, PROC_RESULT)
The value of the second output port is applied to the output port for the expression, and the value of the first output port is applied to var OUTPUT1.

The output parameters are returned in the order they are declared
in the stored procedure itself.
With all these expressions, the datatypes for the ports and variables must match the datatypes for the input/ output variables and return value.

4 Scenarios where we would use stored procedure transformation in Informatica-

  • To perform calculation: There will be many well tested calculations which we implement using expression.Instead of using expression we can use stored procedure to store these calculations and then use them by using connected or unconnected stored procedure transformation
  • Dropping and recreating indexes: Whenever we have huge number  of record to be loaded to target its better to drop the existing indexes and recreate it.For dropping and recreation of indexes we can make use of stored procedures
  • Check the status of a target database before loading data into it.
  • To check the space left in Database