Stored Procedure Transformation is Passive transformation.
It is both connected and unconnected.
Procedure are stored in database and we call the stored procedure through stored procedure transformation in Informatica.
To perform the calculations like Salary hike, Min and Max salary, we use stored procedure Transfomation.
Examples :-
------------
If an application updates the customer table in ten different places, there can be a single stored procedure and a standard procedure call from the application for this functionality.
If a stored procedure has four SQL statements in the code, then there only needs to be a single call to the database instead of four calls for each individual SQL statement.
Two types of ports :-
----------------------
Input : This port is used to get Input parameter.
Output: This port will display the output values as per the procedure defined in database.
Return : This port is used to return values.
Stored Procedure Type :-
--------------------------
There are 5 different Options available in Stored Procedure Transformation Properties.
- Normal : It will run row by row basis.
- Source Pre Load : It works like property in Source Qualifier as 'Pre Sql'.
Before reading the data from the source Source Pre Load would act on database.
- Source Post Load : It works like property in Source Qualifier as 'Post Sql'.
After the load, Source Post Load would act on database.
- Target Pre Load : It works like property in Source Qualifier as 'Pre Sql'.
Before reading the data to the target Target Pre Load would act on database.
- Target Post Load : It works like property in Source Qualifier as 'Post Sql'.
After the load, Source Post Load would act on database
To create a stored procedure transformation :-
- Go to the transformations tab and click import stored procedure option.
- Connect to the database where the stored procedures is stored and select it.
- Now connect the appropriate ports to input and out put ports.
- Select the connection information
- Now execute the workflow, we get the expected results.
It is both connected and unconnected.
Procedure are stored in database and we call the stored procedure through stored procedure transformation in Informatica.
To perform the calculations like Salary hike, Min and Max salary, we use stored procedure Transfomation.
Examples :-
------------
If an application updates the customer table in ten different places, there can be a single stored procedure and a standard procedure call from the application for this functionality.
If a stored procedure has four SQL statements in the code, then there only needs to be a single call to the database instead of four calls for each individual SQL statement.
Two types of ports :-
----------------------
Input : This port is used to get Input parameter.
Output: This port will display the output values as per the procedure defined in database.
Return : This port is used to return values.
Stored Procedure Type :-
--------------------------
There are 5 different Options available in Stored Procedure Transformation Properties.
- Normal : It will run row by row basis.
- Source Pre Load : It works like property in Source Qualifier as 'Pre Sql'.
Before reading the data from the source Source Pre Load would act on database.
- Source Post Load : It works like property in Source Qualifier as 'Post Sql'.
After the load, Source Post Load would act on database.
- Target Pre Load : It works like property in Source Qualifier as 'Pre Sql'.
Before reading the data to the target Target Pre Load would act on database.
- Target Post Load : It works like property in Source Qualifier as 'Post Sql'.
After the load, Source Post Load would act on database
To create a stored procedure transformation :-
- Go to the transformations tab and click import stored procedure option.
- Connect to the database where the stored procedures is stored and select it.
- Now connect the appropriate ports to input and out put ports.
- Select the connection information
- Now execute the workflow, we get the expected results.
No comments:
Post a Comment