Wednesday, February 24, 2016

Stored Procedure In Informatica

Stored Procedure

Introduction to Stored Procedures:
Ø  A stored procedure is a precompiled collection of Transact-SQL, PL-SQL or other database procedural statements and optional flow control statements, similar to an executable script.
Ø  Stored procedures are stored and run within the database. You can run a stored procedure with the EXECUTE SQL statement in a database client tool, just as you can run SQL statements.
Generally we can 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.
Ø  Database developers and programmers use stored procedures for various tasks within databases, since stored procedures allow greater flexibility than SQL statements.

Ø  Stored procedures also provide error handling and logging necessary for critical tasks.

Ø  Developers create stored procedures in the database using the client tools provided with the database.
Stored Procedures in Informatica:
Ø  The stored procedure must exist in the database before creating a Stored Procedure transformation, and the stored procedure can exist in a source, target, or any database with a valid connection to the Integration Service.

Ø  You might use a stored procedure to perform a query or calculation that you would otherwise make part of a mapping. For example, if you already have a well-tested stored procedure for calculating sales tax, you can perform that calculation through the stored procedure instead of recreating the same calculation in an Expression transformation.

Connected Vs Unconnected Stored Procedures:

Ø  Stored procedures run in either connected or unconnected mode.
Ø  The mode you use depends on what the stored procedure does and how you plan to use it in a session. You can configure connected and unconnected Stored Procedure transformations in a mapping.
·         Connected. The flow of data through a mapping in connected mode also passes through the Stored Procedure transformation. All data entering the transformation through the input ports affects the stored procedure. You should use a 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. The unconnected Stored Procedure transformation is not connected directly to the flow of the mapping. It either runs before or after the session, or is called by an expression in another transformation in the mapping.
The following table compares connected and unconnected stored procedure transformation:
If you want to perform below task
Use the below mode
Run a stored procedure before or after a session.
Unconnected
Run a stored procedure once during a mapping, such as pre- or post-session.
Unconnected
Run a stored procedure every time a row passes through the Stored Procedure transformation.
Connected or Unconnected
Run a stored procedure based on data that passes through the mapping, such as when a specific port does not contain a null value.
Unconnected
Pass parameters to the stored procedure and receive a single output parameter.
Connected or Unconnected
Pass parameters to the stored procedure and receive multiple output parameters.
Connected or Unconnected
Run nested stored procedures.
Unconnected
Call multiple times within a mapping.
Unconnected
When you are using any connected stored procedure transformation in a mapping it can be called in two ways:
1. From an expression. Called from an expression written in the Expression Editor within another transformation in the mapping.
In an unconnected mapping, the Stored Procedure transformation does not connect to the pipeline.
2. Pre- or post-session. Runs before or after a session.
You may want to run a stored procedure once per session. For example, if you need to verify that tables exist in a target database before running a mapping, a pre-load target stored procedure can check the tables, and then either continue running the workflow or stop it. You can run a stored procedure on the source, target, or any other connected database
Configure the below settings as part of Un-connected stored procedure settings:
Select the stored procedure type.
The options for stored procedure type include:
·         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. Before the session sends data to the target, the stored procedure runs. This is useful for verifying target tables or disk space on the target system.

·         Target Post-load. After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.

No comments:

Post a Comment