Stored Procedure Transformation In Informatica
Introduction
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.
}Stored Procedure Transformation is a passive transformation.
}Stored procedure transformation can be used in both connected and
unconnected mode.
unconnected mode.
When to use Stored Procedure Transformation
}Complex calculations where Database Procedures perform better than using
multiple transformations in Informatica.
}Re-usability of the Complex procedures in different ETLs in the form of Stored
Procedure transformation.
}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.
When to use Stored Procedure Transformation
}Check the status of a target database before loading data into it.
}Determine if enough space exists in a database.
}Perform a specialized calculation.
}Dropping and recreating indexes.
Transformation Type
}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.
}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:
}Conditional execution of a Stored Procedure
}Execution of SP which does not have any input or output variable
Types:
}Calling of Stored Procedures during different times of session execution
◦Source Pre Load: Runs before the session reads data from the source. Useful for verifying the existence of tables or performing joins of data in a temporary table.
◦Source Post Load: Runs after reading data from the source. Useful for removing temporary tables.
◦Target Pre Load: Runs before the session sends data to the target. This is useful for verifying target tables or disk space on the target system.
◦Target Post Load: Runs after loading data into the target. This is useful for re-creating indexes on the database.
}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.
specified type.
}Normal: The stored procedure transformation runs for each row passed in the
mapping. This is useful when running a calculation against an input port.
Connected stored procedures run only in normal mode.
}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
How to call procedure residing in another 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.
}Executing Multiple sprocs one after other:
}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.
No comments:
Post a Comment