Few tips related to Informatica 8.x environment

Let us discuss some special scenarios that we might face in Informatica 8.x environments.

A. In Informatica 8.x, multiple integration services can be enabled under one node. In case if there is a need to determine the process associated with an Integration service or Repository service, then it can be done as follows.

If there are multiple Integration Services enabled in a node, there are multiple pmserver processes running on the same machine. In PowerCenter 8.x, it is not possible to differentiate between the processes and correlate it to a particular Integration Service, unlike in 7.x where every pmserver process is associated with a specific pmserver.cfg file. Likewise, if there are multiple Repository Services enabled in a node, there are multiple pmrepagent processes running on the same machine. In PowerCenter 8.x, it is not possible to differentiate between the processes and correlate it to a particular Integration Service.

To do these in 8.x do the following:

1.      Log on to the Administration Console

2.      Click on Logs > Display Settings.

3.      Add Process to the list of columns to be displayed in the Log Viewer.

4.      Refresh the log display.

5.      Use the PID from this column to identify the process as follows:

UNIX:

Run the following command:

ps –ef grep pid

Where pid is the process ID of the service process.

Windows:

1. 1. Run task manager.
   2. Select the Processes tab.

Scroll to the value in the PID column that is displayed in the PowerCenter Administration Console.

B. Sometimes, the PowerCenter Administration Console URL is inaccessible from some machines even when the Informatica services are running. The following error is displayed on the browser:

“The page cannot be displayed”

The reason for this is due to an invalid or missing configuration in the hosts file on the client machine.

To resolve this error, do the following:

1. Edit the hosts file located in the windows/system32/drivers/etc folder on the server from where the Administration Console is being accessed.
2. Add the host IP address and the host name (for the host where the PowerCenter services are installed).

Example

10.1.2.10 ha420f3

1. Launch the Administration Console and access the login page by typing the URL: http://<host>:<port>/adminconsole in the browser address bar.

It should be noted that the host name in the URL matches the host entry in the hosts file.

Read More about Informatica 8.x

Informatica and Stored Procedures

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 is1. 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:

1. Create an Expression transformation.
2. 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.

1. 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:

1. Create an Expression transformation.
2. 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.

Read More about Informatica

Informatica PowerCenter 8x Key Concepts – 2

The PowerCenter Repository is one of best metadata storage among all ETL products. The repository is sufficiently normalized to store metadata at a very detail level; which in turn means the Updates to therepository are very quick and the overall Team-based Development is smooth. The repository data structure is also useful for the users to do analysis and reporting.

Accessibility to the repository through MX views and SDK kit extends the repositories capability from a simple storage of technical data to a database for analysis of the ETL metadata.

PowerCenter Repository is a collection of 355 tables which can be created on any major relational database. The kinds of information that are stored in the repository are,

1. Repository configuration details
2. Mappings
3. Workflows
4. User Security
5. Process Data of session runs

For a quick understanding,

When a user creates a folder, corresponding entries are made into table OPB_SUBJECT; attributes like folder name, owner id, type of the folder like shared or not are all stored.
When we create\import sources and define field names, datatypes etc in source analyzer entries are made into opb_src and OPB_SRC_FLD.
When target and related fields are created/imported from any database entries are made into tables like OPB_TARG and OPB_TARG_FLD.
Table OPB_MAPPING stores mapping attributes like Mapping Name, Folder Id, Valid status and mapping comments.
Table OPB_WIDGET stores attributes like widget type, widget name, comments etc. Widgets are nothing but the Transformations which Informatica internally calls them as Widgets.
Table OPB_SESSION stores configurations related to a session task and table OPB_CNX_ATTR stores information related to connection objects.
Table OPB_WFLOW_RUN stores process details like workflow name, workflow started time, workflow completed time, server node it ran etc.
REP_ALL_SOURCES, REP_ALL_TARGETS and REP_ALL_MAPPINGS are few of the many views created over these tables.

PowerCenter applications access the PowerCenter repository through the Repository Service. The Repository Service protects metadata in the repository by managing repository connections and using object-locking to ensure object consistency.

We can create a repository as global or local. We can go for‘global’ to store common objects that multiple developers can use through shortcuts and go for local repository to perform of development mappings and workflows. From a local repository, we can create shortcuts to objects in shared folders in the global repository. PowerCenter supports versioning. A versioned repository can store multiple versions of an object.

Read More about Informatica PowerCenter

Handling Oracle Exceptions

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 Oracle ErrorActionFile 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,’<stored procedure name> 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:

1. Click Start, click Run, type regedit, and click OK.
2. 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

Read More about Oracle

Informatica Exceptions – 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).

Read More about Informatica