Stored procedure Transformation in Informatica

Stored procedure Transformation is passive transformation. It can be used both connected and unconnected mode. It contain a pre-compiled collection of PL-SQL statements. The stored procedures in the database are executed using the Execute or Call statements. 
    Database administrators create stored procedures to automate tasks that are too complicated for standard SQL statements. A Stored Procedure transformation is an important tool for leveraging existing stored procedures from within PowerCenter. Informatica provides the stored procedure transformation which is used to run the stored procedures in the database.
    We might use stored procedures to complete the following tasks:
    • Check the status of a target database before loading data into it.
    • Determine if enough space exists in a database.
    • Perform a specialized calculation.
    • Drop and recreate indexes.
    We can send data to the stored procedure and receive data from the stored procedure. There are three types of data which pass between the integration service and the stored procedure.
    Input/output parameters: Send and receive the data from stored procedure.
    Return values: After running a stored procedure, most databases returns a value. This value can either be user-definable, which means that it can act similar to a single output parameter, or it may only return an integer value. If a stored procedure returns a result set rather than a single return value, the Stored Procedure transformation takes only the first value returned from the procedure.
    Status codes: Status codes provide error handling for the Integration Service during a workflow. Stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. You cannot see this value.

    Property: Stored Procedure Type 
    This "Stored procedure type" property is used to calling of stored procedures different times of session execution.
    Normal: The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. We pass some input to procedure and it returns some calculated values. Connected stored procedures run only in normal mode.
    Source Pre Load: Before the session retrieves data from the source, the stored procedure runs. This is useful for verifying the existence of tables or performing joins of data in a temporary table.
    Source Post Load: After the session retrieves data from the source, the stored procedure runs. This is 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.

    Connected and Unconnected Stored Procedure Transformation
    Connected Stored Procedure Transformation: When the Stored procedure transformation exists along the line of data flow, it is called a Connected Stored Procedure transformation. Use connected Stored Procedure transformation when you need data from an input port sent as an input parameter to the stored procedure, or the results of a stored procedure sent as an output parameter to another transformation.
    Unconnected Stored Procedure Transformation: This type of Stored Procedure transformation does not exist along the pipeline of the mapping and can be called as and when required. It is called by expression in other transformation.

    It is best practices to do not run unnecessary instances of stored procedures as it can impact performance. Each time a stored procedure runs during a mapping, the session must wait for the stored procedure to complete in the database. We have two possible options to avoid this
    Reduce the row count: Use an active transformation prior to the Stored Procedure transformation to reduce the number of rows that must be passed the stored procedure. Or, create an expression that tests the values before passing them to the stored procedure to make sure that the value does not really need to be passed.
    Create an expression: Most of the logic used in stored procedures can be easily replicated using expressions in the Designer.

      Powered by Blogger.