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.

Handling Oracle Exceptions in Infa

There may be requirements wherein certain oracle exceptions need to be treated as Warnings and certain exceptions need to be treated as Fatal.

Normally, a fatal Oracle error may not be registered as a warning or row error and the session may not fail, conversely a non-fatal error may cause a PowerCenter session to fail.This can be changed with few tweaking in

A. Oracle Stored Procedure,
B. The OracleErrorActionFile, and
C. Server Settings

Let us see this with an example.

An Oracle Stored Procedure under certain conditions returns the exception NO_DATA_FOUND. When this exception occurs, the session calling the Stored Procedure does not fail.

Adding an entry for this error in the ora8err.act file and enabling the OracleErrorActionFile option does not change this behavior (Both ora8err.act and OracleErrorActionFile are discussed in later part of this blog).

When this exception (NO_DATA_FOUND) is raised in PL/SQL it is sent to the Oracle client as an informational message not an error message and the Oracle client sends this message to PowerCenter. Since the Oracle client does not return an error to PowerCenter the session continues as normal and will not fail.

A. Modify the Stored Procedure to return a different exception or a custom exception. A custom exception number (only between -20000 and -20999) can be sent using the raise_application_error PL/SQL command as follows:

raise_application_error (-20991,' has raised an error’, true);

Additionally add the following entry to the ora8err.act file:
20991, F

B. Editing the Oracle Error Action file can be done as follows:
1. Go to the server/bin directory under the Informatica Services installation directory (8.x) or the Informatica Server installation directory (7.1.x).
E.g.,

For Infa 7.x
C:\Program Files\Informatica PowerCenter 7.1.3\Server\ora8err.act

For Infa 8.x
C:\Informatica\PowerCenter8.1.1\server\bin

2. Open the ora8err.act file.
3. Change the value associated with the error.
"F" is fatal and stops the session."R" is a row error and writes the row to the reject file and continues to the next row.
Examples:
To fail a session when the ORA-03114 error is encountered change the 03114 line in the file to the following:
03114, F
To return a row error when the ORA-02292 error is encountered change the
02292 line to the following:
02292, R

Note that the Oracle action file only applies to native Oracle connections in the session. If the target is using the SQL*Loader external loader option, the message status will not be modified by the settings in this file.

C. Once the file is modified, following changes need to be done in the server level.
Infa 8.x
Set the OracleErrorActionFile Integration Service Custom Property to the name of the file (ora8err.act by default) as follows:
1. Connect to the Administration Console.
2. Stop the Integration Service.
3. Select the Integration Service.
4. Under the Properties tab, click Edit in the Custom Properties section.
5. Under Name enter OracleErrorActionFile.
6. Enter ora8err.act for the parameter under Value.
7. Click OK.
8. Start the Integration Service.

PowerCenter 7.1.x
In PowerCenter 7.1.x do the following:
UNIX
For the server running on UNIX:
1. Using a text editor open the PowerCenter server configuration file (pmserver.cfg).
2. Add the following entry to the end of the file:
OracleErrorActionFile=ora8err.act
3. Re-start the PowerCenter server (pmserver).

Windows
For the server running on Windows:
· Click Start, click Run, type regedit, and click OK.
· Go to the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\PowerMart\Parameters\Configuration
· Select Edit > New > String Value.
· Enter the "OracleErrorActionFile" for the string value.
· Select Edit > Modify.
· Enter the directory and the file name of the Oracle error action file:
\ora8err.act
Example:
The default entry for PowerCenter 7.1.3 would be:
C:\Program Files\Informatica PowerCenter 7.1.3\Server\ora8err.act
And for PowerCenter8.1.1 it would be
C:\Informatica\PowerCenter8.1.1\server\bin
· Click OK

Exceptions in Informatica - 3

Here are few more Exceptions:
1. There are occasions where sessions fail with the following error in the Workflow Monitor:
“First error code [36401], message [ERROR: Session task instance [session XXXX]: Execution terminated unexpectedly.] “
where XXXX is the session name.
The server log/workflow log shows the following:
“LM_36401 Execution terminated unexpectedly.”
To determine the error do the following:
a. If the session fails before initialization and no session log is created look for errors in Workflow log and pmrepagent log files.
b. If the session log is created and if the log shows errors like
“Caught a fatal signal/exception” or
“Unexpected condition detected at file [xxx] line yy”
then a core dump has been created on the server machine. In this case Informatica Technical Support should be contacted with specific details. This error may also occur when the PowerCenter server log becomes too large and the server is no longer able to write to it. In this case a workflow and session log may not be completed. Deleting or renaming the PowerCenter Server log (pmserver.log) file will resolve the issue.
2. Given below is not an exception but a scenario which most of us would have come across.
Rounding problem occurs with columns in the source defined as Numeric with Precision and Scale or Lookups fail to match on the same columns. Floating point arithmetic is always prone to rounding errors (e.g. the number 1562.99 may be represented internally as 1562.988888889, very close but not exactly the same). This can also affect functions that work with scale such as the Round() function. To resolve this do the following:
a. Select the Enable high precision option for the session.
b. Define all numeric ports as Decimal datatype with the exact precision and scale desired. When high precision processing is enabled the PowerCenter Server support numeric values up to 28 digits. However, the tradeoff is a performance hit (actual performance really depends on how many decimal ports there are).

Exceptions in Informatica - 2

Let us see few more strange exceptions in Informatica
Sometimes the Session fails with the below error message.
“FATAL ERROR : Caught a fatal signal/exception
FATAL ERROR : Aborting the DTM process due to fatal signal/exception."
There might be several reasons for this. One possible reason could be the way the function SUBSTR is used in the mappings, like the length argument of the SUBSTR function being specified incorrectly.
Example:
IIF(SUBSTR(MOBILE_NUMBER, 1, 1) = '9', SUBSTR(MOBILE_NUMBER, 2, 24), MOBILE_NUMBER)
In this example MOBILE_NUMBER is a variable port and is 24 characters long. When the field itself is 24 char long, the SUBSTR starts at position 2 and go for a length of 24 which is the 25th character.
To solve this, correct the length option so that it does not go beyond the length of the field or avoid using the length option to return the entire string starting with the start value.
Example:
In this example modify the expression as follows:
IIF(SUBSTR(MOBILE_NUMBER, 1, 1) = '9', SUBSTR(MOBILE_NUMBER, 2, 23), MOBILE_NUMBER)
OR
IIF(SUBSTR(MOBILE_NUMBER, 1, 1) = '9', SUBSTR(MOBILE_NUMBER, 2), MOBILE_NUMBER).
2. The following error can occur at times when a session is run
“TE_11015 Error in xxx: No matching input port found for output port OUTPUT_PORT TM_6006 Error initializing DTM for session...”
Where xxx is a Transformation Name.
This error will occur when there is corruption in the transformation. To resolve this do one of the following: * Recreate the transformation in the mapping having this error.
3. At times you get the below problems,
1. When opening designer, you get "Exception access violation", "Unexpected condition detected".
2. Unable to see the navigator window, output window or the overview window in designer even after toggling it on.
3. Toolbars or checkboxes are not showing up correctly.
These are all indications that the pmdesign.ini file might be corrupted. To solve this, following steps need to be followed.
1. Close Informatica Designer 2. Rename the pmdesign.ini (in c:\winnt\system32 or c:\windows\system). 3. Re-open the designer.
When PowerMart opens the Designer, it will create a new pmdesign.ini if it doesn't find an existing one. Even reinstalling the PowerMart clients will not create this file if it finds one.

Informatica Exceptions - 1

There exists no product/tool without strange exceptions/errors, we will see some from Informatica.

1. You get the below error when you do “Generate SQL” in Source Qualifier and try to validate it.

“Query should return exactly n field(s) to match field(s) projected from the Source Qualifier”
Where n is the number of fields projected from the Source Qualifier.

Possible reasons for this to occur are:
The order of ports may be wrong
The number of ports in the transformation may be more/less.
Sometimes you will have the correct number of ports and in correct order too but even then you might face this error in that case make sure that Owner name and Schema name are specified correctly for the tables used in the Source Qualifier Query.
E.g., TC_0002.EXP_AUTH@TIMEP

2. The following error occurs at times when an Oracle table is used

“[/export/home/build80/zeusbuild/vobs/powrmart/common/odl/oracle8/oradriver.cpp] line [xxx]”
Where xxx is some line number mostly 241, 291 or 416.

Possible reasons are
Use DataDirect Oracle ODBC driver instead of the driver “Oracle in
If the table has been imported using the Oracle drivers which are not supported, then columns with Varchar2 data type are replaced by String data type and Number columns are imported with precision Zero(0).
3. Recently I encountered the below error while trying to save a Mapping.

Unexpected Condition Detected Warning: Unexpected condition at: statbar.cpp: 268 Contact Informatica Technical Support for assistance

When there is no enough memory in System this happens. To resolve this we can either
Increase the Virtual Memory in the system
If continue to receive the same error even after increasing the Virtual Memory, in Designer, go to ToolsàOptions, go to General tab and clear the “Save MX Data” option.

Thanks for reading.Post your comments, if any.

Intro to Infa

The blogs in this area will dicuss about Informatica,its architecture,exceptions,latest version and features in it.
Will try to answer questions if any raised here.

Thanks.