STORED PROCEDURE T/F

• Passive Transformation
• Connected and Unconnected Transformation
• Stored procedures are stored and run within the database. 

A Stored Procedure transformation is an important tool for populating and Maintaining databases. Database administrators create stored procedures to Automate tasks that are too complicated for standard SQL statements.

Use of Stored Procedure in mapping:

• 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. Mostly used for this in projects.

Data Passes Between IS and Stored Procedure One of the most useful features of stored procedures is the ability to send data to the stored procedure, and receive data from the stored procedure. There are three types of data that pass between the Integration Service and the stored procedure:

Input/output parameters: Parameters we give as input and the parameters returned from Stored Procedure.

Return values: Value returned by Stored Procedure if any.

Status codes: Status codes provide error handling for the IS during a workflow. The stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. We cannot see this value. The IS uses it to determine whether to continue running the session or stop. Specifying when the Stored Procedure Runs

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.

Pre-load of the Source: 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.

Post-load of the Source: After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.

Pre-load of the Target: Before the session sends data to the target, the stored procedure runs. This is useful for dropping indexes or disabling constraints.

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

Using a Stored Procedure in a Mapping :

1. Create the stored procedure in the database.
2. Import or create the Stored Procedure transformation.
3. Determine whether to use the transformation as connected or unconnected.
4. If connected, map the appropriate input and output ports.
5. If unconnected, either configure the stored procedure to run pre- or post-session, or configure it to run from an expression in another transformation.
6. Configure the session.

Stored Procedures:

Connect to Source database and create the stored procedures given below:

CREATE OR REPLACE procedure sp_agg (in_deptno in number, max_sal out number,

min_sal out number, avg_sal out number, sum_sal out number)

As

Begin

select max(Sal),min(sal),avg(sal),sum(sal) into max_sal,min_sal,avg_sal,sum_sal

from emp where deptno=in_deptno group by deptno;

End;

/

CREATE OR REPLACE procedure sp_unconn_1_value(in_deptno in number, max_sal out number)

As

Begin

Select max(Sal) into max_sal from EMP where deptno=in_deptno;

End;

/

1. Connected Stored Procedure T/F

Example: To give input as DEPTNO from DEPT table and find the MAX, MIN, AVG and SUM of SAL from EMP table.

• DEPT will be source table. Create a target table SP_CONN_EXAMPLE with fields DEPTNO, MAX_SAL, MIN_SAL, AVG_SAL & SUM_SAL.
• Write Stored Procedure in Database first and Create shortcuts as needed.

Creating Mapping:

1. Open folder where we want to create the mapping.

2. Click Tools -> Mapping Designer.

3. Click Mapping-> Create-> Give name. Ex: m_SP_CONN_EXAMPLE

4. Drag DEPT and Target table.

5. Transformation -> Import Stored Procedure -> Give Database Connection -> Connect -> Select the procedure sp_agg from the list.

6. Drag DEPTNO from SQ_DEPT to the stored procedure input port and also to DEPTNO port of target.

7. Connect the ports from procedure to target as shown below:

8. Mapping -> Validate

9. Repository -> Save 

• Create Session and then workflow.
• Give connection information for all tables.
• Give connection information for Stored Procedure also.
• Run workflow and see the result in table.  
• 

2. Unconnected Stored Procedure T/F :

An unconnected Stored Procedure transformation is not directly connected to the flow of data through the mapping. Instead, the stored procedure runs either:

• From an expression: Called from an expression transformation.
• Pre- or post-session: Runs before or after a session.

Method of returning the value of output parameters to a port:

• Assign the output value to a local variable.
• Assign the output value to the system variable PROC_RESULT. (See Later)

Example 1: DEPTNO as input and get MAX of Sal as output.

• DEPT will be source table.
• Create a target table with fields DEPTNO and MAX_SAL of decimal data type.
• Write Stored Procedure in Database first and Create shortcuts as needed.

Creating Mapping:

1. Open folder where we want to create the mapping.

2. Click Tools -> Mapping Designer.

3. Click Mapping-> Create-> Give name. Ex: m_sp_unconn_1_value

4. Drag DEPT and Target table.

5. Transformation -> Import Stored Procedure -> Give Database Connection -> Connect -> Select the procedure sp_unconn_1_value from the list. Click OK.

6. Stored Procedure has been imported.

7. T/F -> Create Expression T/F. Pass DEPTNO from SQ_DEPT to Expression T/F.

8. Edit expression and create an output port OUT_MAX_SAL of decimal data type.

9. Open Expression editor and call the stored procedure as below:Click OK and connect the port from expression to target as in mapping below: 

10. Mapping -> Validate

11. Repository Save.

• Create Session and then workflow.
• Give connection information for all tables.
• Give connection information for Stored Procedure also.
• Run workflow and see the result in table.

PROC_RESULT use:

• If the stored procedure returns a single output parameter or a return value, we the reserved variable PROC_RESULT as the output variable.

Example: DEPTNO as Input and MAX Sal as output :

:SP.SP_UNCONN_1_VALUE(DEPTNO,PROC_RESULT)

• If the stored procedure returns multiple output parameters, you must create variables for each output parameter.

Example: DEPTNO as Input and MAX_SAL, MIN_SAL, AVG_SAL and SUM_SAL

as output then:

1. Create four variable ports in expression VAR_MAX_SAL,VAR_MIN_SAL, VAR_AVG_SAL and iVAR_SUM_SAL.

2. Create four output ports in expression OUT_MAX_SAL, OUT_MIN_SAL, OUT_AVG_SAL and OUT_SUM_SAL.

3. Call the procedure in last variable port says VAR_SUM_SAL.

:SP.SP_AGG (DEPTNO, VAR_MAX_SAL,VAR_MIN_SAL, VAR_AVG_SAL, PROC_RESULT)

Example 2:

DEPTNO as Input and MAX_SAL, MIN_SAL, AVG_SAL and SUM_SAL as O/P Stored Procedure to drop index in Pre Load of Target Stored Procedure to create index in Post Load of Target

• DEPT will be source table. Create a target table SP_UNCONN_EXAMPLE with fields DEPTNO, MAX_SAL, MIN_SAL, AVG_SAL & SUM_SAL.
• Write Stored Procedure in Database first and Create shortcuts as needed. Stored procedures are given below to drop and create index on target.Make sure to create target table first. Stored Procedures to be created in next example in Target Database: 

 

Create or replace procedure CREATE_INDEX

As

Begin

Execute immediate 'create index unconn_dept on SP_UNCONN_EXAMPLE(DEPTNO)';

End;

/

Create or replace procedure DROP_INDEX

As

Begin

Execute immediate 'drop index unconn_dept';

End;

/

Creating Mapping:

1. Open folder where we want to create the mapping.

2. Click Tools -> Mapping Designer.

3. Click Mapping-> Create-> Give name. Ex: m_sp_unconn_1_value

4. Drag DEPT and Target table.

5. Transformation -> Import Stored Procedure -> Give Database Connection -> Connect -> Select the procedure sp_agg from the list. Click OK.

6. Stored Procedure has been imported.

7. T/F -> Create Expression T/F. Pass DEPTNO from SQ_DEPT to Expression T/F.

8. Edit Expression and create 4 variable ports and 4 output ports as shown below: 

 

9. Call the procedure in last variable port VAR_SUM_SAL.

10. :SP.SP_AGG (DEPTNO, VAR_MAX_SAL, VAR_MIN_SAL, VAR_AVG_SAL, PROC_RESULT)

11. Click Apply and Ok.

12. Connect to target table as needed.

13. Transformation -> Import Stored Procedure -> Give Database Connection for target -> Connect -> Select the procedure CREATE_INDEX and DROP_INDEX from the list. Click OK.

14. Edit DROP_INDEX -> Properties Tab -> Select Target Pre Load as Stored Procedure Type and in call text write drop_index. Click Apply -> Ok.

15. Edit CREATE_INDEX -> Properties Tab -> Select Target Post Load as Stored Procedure Type and in call text write create_index. Click Apply -> Ok.

16. Mapping -> Validate

17. Repository -> Save

• Create Session and then workflow.
• Give connection information for all tables.
• Give connection information for Stored Procedures also.
• Also make sure that you execute the procedure CREATE_INDEX on database before using them in mapping. This is because, if there is no INDEX on target table, DROP_INDEX will fail and Session will also fail.
• Run workflow and see the result in table.

SOURCE QUALIFIER T/F

• Active and Connected Transformation.
• The Source Qualifier transformation represents the rows that the Power Center Server reads when it runs a session.
• It is only transformation that is not reusable.
• Default transformation except in case of XML or COBOL files.

Tasks performed by Source Qualifier:

• Join data originating from the same source database: We can join two or more tables with primary key-foreign key relationships by linking the sources to one Source Qualifier transformation.
• Filter rows when the Power Center Server reads source data: If we Include a filter condition, the Power Center Server adds a WHERE clause to the Default query.
• Specify an outer join rather than the default inner join: If we include a User-defined join, the Power Center Server replaces the join information Specified by the metadata in the SQL query.
• Specify sorted ports: If we specify a number for sorted ports, the
• Power Center Server adds an ORDER BY clause to the default SQL query.
• Select only distinct values from the source: If we choose Select Distinct,the Power Center Server adds a SELECT DISTINCT statement to the default SQL query.
• Create a custom query to issue a special SELECT statement for the Power Center Server to read source data: For example, you might use a Custom query to perform aggregate calculations. The entire above are possible in Properties Tab of Source Qualifier t/f.

SAMPLE MAPPING TO BE MADE: 

• Source will be EMP and DEPT tables.
• Create target table as showed in Picture above.
• Create shortcuts in your folder as needed.

Creating Mapping:

1. Open folder where we want to create the mapping.
2. Click Tools -> Mapping Designer.
3. Click Mapping-> Create-> Give mapping name. Ex: m_SQ_example
4. Drag EMP, DEPT, Target.
5. Right Click SQ_EMP and Select Delete from the mapping.
6. Right Click SQ_DEPT and Select Delete from the mapping.
7. Click Transformation -> Create -> Select Source Qualifier from List -> Give Name -> Click Create
8. Select EMP and DEPT both. Click OK.
9. Link all as shown in above picture.
10. Edit SQ -> Properties Tab -> Open User defined Join -> Give Join condition EMP.DEPTNO=DEPT.DEPTNO. Click Apply -> OK
11. Mapping -> Validate
12. Repository -> Save

• Create Session and Workflow as described earlier. Run the Workflow and see the data in target table.
• Make sure to give connection information for all tables.

SQ PROPERTIES TAB

1) SOURCE FILTER:

We can enter a source filter to reduce the number of rows the Power Center Server queries.

Note: When we enter a source filter in the session properties, we override the customized SQL query in the Source Qualifier transformation.

Steps:

1. In the Mapping Designer, open a Source Qualifier transformation.
2. Select the Properties tab.
3. Click the Open button in the Source Filter field.
4. In the SQL Editor Dialog box, enter the filter. Example: EMP.SAL)2000
5. Click OK.

Validate the mapping. Save it. Now refresh session and save the changes. Now run the workflow and see output.

2) NUMBER OF SORTED PORTS:

When we use sorted ports, the Power Center Server adds the ports to the ORDER BY clause in the default query.

By default it is 0. If we change it to 1, then the data will be sorted by column that is at the top in SQ. Example: DEPTNO in above figure.

• If we want to sort as per ENAME, move ENAME to top.
• If we change it to 2, then data will be sorted by top two columns.

Steps:

1. In the Mapping Designer, open a Source Qualifier transformation.
2. Select the Properties tab.
3. Enter any number instead of zero for Number of Sorted ports.
4. Click Apply -> Click OK.

Validate the mapping. Save it. Now refresh session and save the changes. Now run the workflow and see output.

3) SELECT DISTINCT:

If we want the Power Center Server to select unique values from a source, we can use the Select Distinct option.

• Just check the option in Properties tab to enable it.

4) PRE and POST SQL Commands

• The Power Center Server runs pre-session SQL commands against the source database before it reads the source.
• It runs post-session SQL commands against the source database after it writes to the target.
• Use a semi-colon (;) to separate multiple statements. 

5) USER DEFINED JOINS

Entering a user-defined join is similar to entering a custom SQL query. However, we only enter the contents of the WHERE clause, not the entire query.

• We can specify equi join, left outer join and right outer join only. We Cannot specify full outer join. To use full outer join, we need to write SQL Query.

Steps:

1. Open the Source Qualifier transformation, and click the Properties tab.
2. Click the Open button in the User Defined Join field. The SQL Editor Dialog Box appears.
3. Enter the syntax for the join.
4. Click OK -> Again Ok.

Validate the mapping. Save it. Now refresh session and save the changes. Now run the workflow and see output.

Join Type	Syntax
Equi Join	DEPT.DEPTNO=EMP.DEPTNO
Left Outer Join	{EMP LEFT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}
Right Outer Join	{EMP RIGHT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

6) SQL QUERY

For relational sources, the Power Center Server generates a query for each Source Qualifier transformation when it runs a session. The default query is a SELECT statement for each source column used in the mapping. In other words, the Power Center Server reads only the columns that are connected to another Transformation.

In mapping above, we are passing only SAL and DEPTNO from SQ_EMP to Aggregator transformation. Default query generated will be:

• SELECT EMP.SAL, EMP.DEPTNO FROM EMP

Viewing the Default Query

1. Open the Source Qualifier transformation, and click the Properties tab.
2. Open SQL Query. The SQL Editor displays.
3. Click Generate SQL.
4. The SQL Editor displays the default query the Power Center Server uses to Select source data.
5. Click Cancel to exit.

Note: If we do not cancel the SQL query, the Power Center Server overrides the default query with the custom SQL query.

We can enter an SQL statement supported by our source database. Before entering the query, connect all the input and output ports we want to use in the mapping.

Example: As in our case, we can’t use full outer join in user defined join, we can write SQL query for FULL OUTER JOIN:

SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP FULL OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO WHERE SAL>2000

• We also added WHERE clause. We can enter more conditions and write More complex SQL.

We can write any query. We can join as many tables in one query as Required if all are in same database. It is very handy and used in most of the projects.

Important Points:

• When creating a custom SQL query, the SELECT statement must list the port names in the order in which they appear in the transformation.

Example: DEPTNO is top column; DNAME is second in our SQ   mapping.

So when we write SQL Query, SELECT statement have name DNAME first, DNAME second and so on. SELECT DEPT.DEPTNO, DEPT.DNAME

• Once we have written a custom query like above, then this query will Always be used to fetch data from database. In our example, we used WHERE SAL>2000. Now if we use Source Filter and give condition SAL) 1000 or any other, then it will not work. Informatica will always use the custom query only.
• Make sure to test the query in database first before using it in SQL Query. If query is not running in database, then it won’t work in Informatica too.
• Also always connect to the database and validate the SQL in SQL query editor

Transaction Control

Power Center lets you control commit and roll back transactions based on a set of rows that pass through a Transaction Control transformation. A transaction is the set of rows bound by commit or roll back rows. You can define a transaction based on a varying number of input rows. You might want to define transactions based on a group of rows ordered on a common key, such as employee ID or order entry date.

In Power Center, you define transaction control at the following levels:

• Within a mapping. Within a mapping, you use the Transaction Control transformation to define a transaction. You define transactions using an expression in a Transaction Control transformation. Based on the return value of the expression, you can choose to commit, roll back, or continue without any transaction changes.
• Within a session. When you configure a session, you configure it for user-defined commit. You can choose to commit or roll back a transaction if the Integration Service fails to transform or write any row to the target.

When you run the session, the Integration Service evaluates the expression for each row that enters the transformation. When it evaluates a commit row, it commits all rows in the transaction to the target or targets. When the Integration Service evaluates a roll back row, it rolls back all rows in the transaction from the target or targets. If the mapping has a flat file target you can generate an output file each time the Integration Service starts a new transaction. You can dynamically name each target flat file.

Properties Tab

On the Properties tab, you can configure the following properties:

• Transaction control expression
• Tracing level

Enter the transaction control expression in the Transaction Control Condition field. The transaction control expression uses the IIF function to test each row against the condition. Use the following syntax for the expression:

IIF (condition, value1, value2)

The expression contains values that represent actions the Integration Service performs based on the return value of the condition. The Integration Service evaluates the condition on a row-by-row basis. The return value determines whether the Integration Service commits, rolls back, or makes no transaction changes to the row.

When the Integration Service issues a commit or roll back based on the return value of the expression, it begins a new transaction. Use the following built-in variables in the Expression Editor when you create a transaction control expression:

• TC_CONTINUE_TRANSACTION. The Integration Service does not perform any transaction change for this row. This is the default value of the expression.
• TC_COMMIT_BEFORE. The Integration Service commits the transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.
• TC_COMMIT_AFTER. The Integration Service writes the current row to the target, commits the transaction, and begins a new transaction. The current row is in the committed transaction.
• TC_ROLLBACK_BEFORE. The Integration Service rolls back the current transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.
• TC_ROLLBACK_AFTER. The Integration Service writes the current row to the target, rolls back the transaction, and begins a new transaction. The current row is in the rolled back transaction.

If the transaction control expression evaluates to a value other than commit, roll back, or continue, the Integration Service fails the session.

Mapping Guidelines and Validation

Use the following rules and guidelines when you create a mapping with a Transaction Control transformation:

• If the mapping includes an XML target, and you choose to append or create a new document on commit, the input groups must receive data from the same transaction control point.
• Transaction Control transformations connected to any target other than relational, XML, or dynamic MQSeries targets are ineffective for those targets.
• You must connect each target instance to a Transaction Control transformation.
• You can connect multiple targets to a single Transaction Control transformation.
• You can connect only one effective Transaction Control transformation to a target.
• You cannot place a Transaction Control transformation in a pipeline branch that starts with a Sequence Generator transformation.
• If you use a dynamic Lookup transformation and a Transaction Control transformation in the same mapping, a rolled-back transaction might result in unsynchronized target data.
• A Transaction Control transformation may be effective for one target and ineffective for another target. If each target is connected to an effective Transaction Control transformation, the mapping is valid.
• Either all targets or none of the targets in the mapping should be connected to an effective Transaction Control transformation.

Example to Transaction Control:

Step 1: Design the mapping.

Step 2: Creating a Transaction Control Transformation.

• In the Mapping Designer, click Transformation > Create. Select the Transaction Control transformation.
• Enter a name for the transformation.[ The naming convention for Transaction Control transformations is TC_TransformationName].
• Enter a description for the transformation.
• Click Create.
• Click Done.
• Drag the ports into the transformation.
• Open the Edit Transformations dialog box, and select the Ports tab.

Select the Properties tab. Enter the transaction control expression that defines the commit and roll back behavior.

Go to the Properties tab and click on the down arrow to get in to the expression editor window. Later go to the Variables tab and Type IIF(EMpno=7654,) select the below things from the built in functions.

IIF (EMPNO=7654,TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)

• Connect all the columns from the transformation to the target table and save the mapping.
• Select the Metadata Extensions tab. Create or edit metadata extensions for the Transaction Control transformation.
• Click OK.

Step 3: Create the task and the work flow.

Step 4: Preview the output in the target table.

RANK TRANSFORMATION

• Active and connected transformation

The Rank transformation allows us to select only the top or bottom rank of data. It Allows us to select a group of top or bottom values, not just one value.

During the session, the Power Center Server caches input data until it can perform The rank calculations.

Rank Transformation Properties :

• Cache Directory where cache will be made.
• Top/Bottom Rank as per need
• Number of Ranks Ex: 1, 2 or any number
• Case Sensitive Comparison can be checked if needed
• Rank Data Cache Size can be set
• Rank Index Cache Size can be set

Ports in a Rank Transformation :

Ports	Number Required	Description
I	1 Minimum	Port to receive data from another transformation.
O	1 Minimum	Port we want to pass to other transformation.
V	not needed	can use to store values or calculations to use in an expression.
R	Only 1	Rank port. Rank is calculated according to it. The Rank port is an input/output port. We must link the Rank port to another transformation. Example: Total Salary

Rank Index

The Designer automatically creates a RANKINDEX port for each Rank transformation. The Power Center Server uses the Rank Index port to store the ranking position for Each row in a group.

For example, if we create a Rank transformation that ranks the top five salaried employees, the rank index numbers the employees from 1 to 5.

• The RANKINDEX is an output port only.
• We can pass the rank index to another transformation in the mapping or directly to a target.
• We cannot delete or edit it.

Defining Groups

Rank transformation allows us to group information. For example: If we want to select the top 3 salaried employees of each Department, we can define a group for Department.

• By defining groups, we create one set of ranked rows for each group.
• We define a group in Ports tab. Click the Group By for needed port.
• We cannot Group By on port which is also Rank Port.

1) Example: Finding Top 5 Salaried Employees

• EMP will be source table.
• Create a target table EMP_RANK_EXAMPLE in target designer. Structure should be same as EMP table. Just add one more port Rank_Index to store RANK INDEX.
• Create the shortcuts in your folder.

Creating Mapping:

1. Open folder where we want to create the mapping.
2. Click Tools -> Mapping Designer.
3. Click Mapping-> Create-> Give mapping name. Ex: m_rank_example
4. Drag EMP from source in mapping.
5. Create an EXPRESSION transformation to calculate TOTAL_SAL.
6. Click Transformation -> Create -> Select RANK from list. Give name and click Create. Now click done.
7. Pass ports from Expression to Rank Transformation.
8. Edit Rank Transformation. Go to Ports Tab
9. Select TOTAL_SAL as rank port. Check R type in front of TOTAL_SAL.
10. Click Properties Tab and Select Properties as needed.
11. Top in Top/Bottom and Number of Ranks as 5.
12. Click Apply -> Ok.
13. Drag target table now.
14. Connect the output ports from Rank to target table.
15. Click Mapping -> Validate
16. Repository -> Save

• Create Session and Workflow as described earlier. Run the Workflow and see the data in target table.
• Make sure to give connection information for all tables.

2) Example: Finding Top 2 Salaried Employees for every DEPARTMENT

• Open the mapping made above. Edit Rank Transformation.
• Go to Ports Tab. Select Group By for DEPTNO.
• Go to Properties tab. Set Number of Ranks as 2.
• Click Apply -> Ok.
• Mapping -> Validate and Repository Save.

Refresh the session by double clicking. Save the changed and run workflow to see the new result.

RANK CACHE

Sample Rank Mapping

When the Power Center Server runs a session with a Rank transformation, it compares an input row with rows in the data cache. If the input row out-ranks a Stored row, the Power Center Server replaces the stored row with the input row.

Example: Power Center caches the first 5 rows if we are finding top 5 salaried Employees. When 6^th row is read, it compares it with 5 rows in cache and places it in Cache is needed.

1) RANK INDEX CACHE:

The index cache holds group information from the group by ports. If we are Using Group By on DEPTNO, then this cache stores values 10, 20, 30 etc.

• All Group By Columns are in RANK INDEX CACHE. Ex. DEPTNO

2) RANK DATA CACHE:

It holds row data until the Power Center Server completes the ranking and is Generally larger than the index cache. To reduce the data cache size, connect Only the necessary input/output ports to subsequent transformations.

• All Variable ports if there, Rank Port, All ports going out from RANK Transformations are stored in RANK DATA CACHE.
• Example: All ports except DEPTNO In our mapping example