Informatica 8.6 for Developers - Part 1

Informatica has released its latest version 8.6 covering all the hot fixes it released for the prior version 8.5 and including few new features. Since version 8, a Unified Admin Console has been designed for managing Integration and Repository services.
What does PowerCenter 8.6 bring new for the developers? Let us discuss PowerCenter 8.6 Client enhancements which will be useful to the developers.

1. Creating Targets from Transformations
We can create targets based on transformations in the workspace or navigator.

To create a target,

1. Right-click the transformation in the workspace and select the Create and Add Target option.

2. Alternatively, we can drag and drop the transformation in the Target Designer.

The target that is created has the same port definitions as the transformation from which it was created. We can edit the target definitions later. In addition, the target type is the same as that of the repository used.

2. Invalid/Invalidated renamed

In PowerCenter 7, the two states of objects were known as Invalid and Invalidated.


The exact meaning of these states is as follows:

Invalid - an object will not run,

Invalidated - an object may be invalid or may not run.

The difference between the two terms was not very clear. Therefore, to avoid any confusion, in PowerCenter 8.6, the two states have been renamed as Invalid and Impacted. While the Invalid state still implies that an object will not run, Impacted means that an object is affected by a change, and therefore, may not run.

Apart from the naming convention the icons are also changed in PowerCenter 8.

3. Propagating Port Descriptions

In the Designer, in addition to the other properties of port propagation, we can edit a port description and propagate the description to other transformations in the mapping.

4. Environment SQL Enhancements

In PowerCenter 8, environment SQL can be used to execute an SQL statement at the beginning of each transaction. The Integration Service executes transaction environment SQL at the beginning of each transaction. Environment SQL can still be used to execute an SQL statement at each connection to the database.


Use SQL commands that depend upon a transaction being opened during the entire read or write process. For example, the following SQL command modifies how the session handles characters:

ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR

5. Flat File Enhancements

PowerCenter 8 includes enhancements for handling flat files. Some of these improve performance.

Flat files can now use Integer or Double data types.

In addition, target partitions can be merged. The flat file target merge options include:

• No Merge

• Sequential Merge

• File List

• Concurrent Merge

Append Data to Flat Files

Data can be appended to existing flat files else new ones will be created if they don't already exist.

Flat File Headers and Footers

Header and footer information can be written to flat files.

The text for the headers and footers comes from either target port names or commands that specify information. But target port names will provide details only for headers.

There are few more enhancements which we will discuss in the forthcoming blogs.

Thanks for Reading.

Informatica and Stored Procedures

Belated New Year Wishes to all.Hope the new year brings happiness to all.

Having relocated to a new place, it took sometime for me to settle...hence forth there will be a regular update in the blogs.

A. Described below is a scenario where the requirement is to have a stored procedure that returns a cursor as a source.

By and large PowerCenter does not support a stored procedure that returns a cursor as a source. The workaround for this is

1. The procedure that will load the data to a new table:

CREATE OR REPLACE procedure load (p_initial_date in date, p_final_Date in date) as

str_load varchar2 (500);
str_clean varchar2 (500);
begin
str_clean:= 'DELETE FROM EMP';
str_load:= 'INSERT INTO EMP select * from EMPLOYEE where DOJ between trunc

(p_initial_date) and trunc (p_final_Date) ';
execute immediate str_clean;
execute immediate str_load;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
end load;


2. Create the table that will receive the data from the procedure:

SQL> create table EMP as SELECT * from EMPLOYEE where 1 > 2;

3. Add a Store Procedure transformation to the PowerCenter mapping. This transformation will execute this new procedure called as LOAD on this example.

4. Set the run method to be Source Pre Load, to be executed before read the source table.

5. Import the EMP table as a Source Definition. This table will be populated by the new Store Procedure.

If the original store procedure is used by the customer application and you can't change the source code, you can create a new store procedure that call the original one (without inserting into a table), and execute the insert on the new table executing a loop on the returned cursor.

B. Given below is a situation where you wanted to pass a mapping variable to a stored procedure transformation (it can either be connected or unconnected).

Connected Stored Procedure

The parameters that are passed to a connected Stored Procedure have to be linked from another transformation.
Given below are the steps to pass mapping variable to a connected Stored Procedure transformation:

Create an Expression transformation.
Create an output port in the Expression transformation with the following expression:
$$mapping_variable

This sets the value of this output port to the mapping variable.

Link this output port to the Stored Procedure transformation.
Unconnected Stored Procedure

For unconnected Stored Procedure transformations you can use the mapping variable in the expression calling the stored procedure.
Follow the steps below to pass mapping variable to a unconnected Stored Procedure transformation:

Create an Expression transformation.
Create an output port in the Expression transformation with the following expression:
: SP.GET_NAME_FROM_ID ($$mapping_variable, PROC_RESULT)

In case if you are attempting to use a mapping variable to store the output value of the stored procedure, the session will fail with the below error.

"TE_7002 Transformation Parse Fatal Error; transformation stopped: invalid function reference. Failed to Initialize Server Transformation."

To resolve the issue replace the mapping variable with the PROC_RESULT system variable.

Example:

Incorrect, using a mapping variable:

:SP.PROCEDURE(FIELD1, $$mapping_variable)



Correct, using the PROC_RESULT system variable:

:SP.PROCEDURE(FIELD1,PROC_RESULT)

Or

:SP.PROCEDURE($$mapping_variable,PROC_RESULT)

The PROC_RESULT system variable assigns the stored procedure output to the port with this expression.

Thanks for reading.
Please do share your thoughts/comments.