Leveraging Metadata in Informatica Workflow-Session/Analysis

We can leverage the metadata collected in the Informatica repository for many interesting analysis, few of the scenarios where I have leveraged the Informatica Metadata are as following.

This SQL Queries can be executed in Oracle database with no changes and requires little modification with other databases.

Failed Sessions

The following query lists the failed sessions. To make it work for the last ‘n’ days, replace SYSDATE-1 with SYSDATE – n

QUERY:

SELECT SUBJECT_AREA AS FOLDER_NAME,

SESSION_NAME,

LAST_ERROR AS ERROR_MESSAGE,

DECODE (RUN_STATUS_CODE,3,’Failed’,4,’Stopped’,5,’Aborted’) AS STATUS,

ACTUAL_START AS START_TIME,

SESSION_TIMESTAMP

FROM REP_SESS_LOG

WHERE RUN_STATUS_CODE != 1

AND TRUNC(ACTUAL_START) BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE)

RESULT:

Long running Sessions

The following query lists long running sessions. To make it work for the last ‘n’ days, replace SYSDATE-1 with SYSDATE – n

QUERY:

SELECT SUBJECT_AREA AS FOLDER_NAME,

SESSION_NAME,

SUCCESSFUL_SOURCE_ROWS AS SOURCE_ROWS,

SUCCESSFUL_ROWS AS TARGET_ROWS,

ACTUAL_START AS START_TIME,

SESSION_TIMESTAMP

FROM REP_SESS_LOG

WHERE RUN_STATUS_CODE = 1

AND TRUNC(ACTUAL_START) BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE)

AND (SESSION_TIMESTAMP – ACTUAL_START) > (10/(24*60))

ORDER BY SESSION_TIMESTAMP

RESULT:

Invalid Tasks

The following query lists folder names and task name, version number, and last saved for all invalid tasks.

QUERY:

SELECT SUBJECT_AREA AS FOLDER_NAME,

DECODE(IS_REUSABLE,1,’Reusable’,’ ‘) || ‘ ‘ ||TASK_TYPE_NAME AS TASK_TYPE,

TASK_NAME AS OBJECT_NAME,

VERSION_NUMBER,

LAST_SAVED

FROM REP_ALL_TASKS

WHERE IS_VALID=0

AND IS_ENABLED=1

ORDER BY SUBJECT_AREA,TASK_NAME

RESULT:

Thanks for reading, do you have other scenarios where Workflow Metadata has been effective …wish you a very happy new year 2011.

To know more about Informatica Workflow

Informatica Development Best Practice – Workflow

Workflow Manager default properties can be modified to improve the overall performance and few of them are listed below.    This properties can impact the ETL runtime directly and needs to configured based on :

i)  Source Database
ii) Target Database
iii) Data Volume

Category	Technique
Session Properties	While loading Staging Tables for FULL LOADS,  Truncate target table option should be checked. Based on the Target database and the primary key defined, Integration Service fires TRUNCATE or DELETE statement.Database                  Primary Key Defined                   No Primary KeyDB2                             TRUNCATE                                       TRUNCATE INFORMIX                 DELETE                                              DELETE ODBC                         DELETE                                                DELETE ORACLE                    DELETE UNRECOVERABLE            TRUNCATE MSSQL                       DELETE                                               TRUNCATE SYBASE                     TRUNCATE                                        TRUNCATE Workflow Property “Commit interval” (Default value : 10,000) should be increased for increased for Volumes more than 1 million records.  Database Rollback Segment size should also be updated, while increasing “Commit Interval”. Insert/Update/Delete options should be set as determined by the target population method. Target Option                                   Integration Service Insert                                                   Uses Target update Option Update as Update Update as Insert Update else Insert Update as update                             Updates all rows as Update Update as Insert                               Inserts all rows Update else Insert                            Updates existing rows else Insert
Partition	Maximum number of partitions for a session should be 1.5 times the number of processes in the Informatica server. i.e. 1.5 X 4 Processors = 6 partitions. Key Value partitions should be used only when an even Distribution of data can be obtained.  In other cases, Pass Through partitions should be used. A Source filter should be added to evenly distribute the data between Pass through Partitions. Key Value should have ONLY numeric values. MOD(NVL(<Numeric Key Value>,0),# No of Partitions defined)  Ex: MOD(NVL(product_sys_no,0),6) If a session contains “N” partition, increase the DTM Buffer Size to at least “N” times the value for the session with One partition If the Source or Target database is of MPP( Massively Parallel Processing ), enable Pushdown Optimization.  By enabling this, Integration Service will push as much Transformation Logic to Source database or Target database or FULL ( both ) , based on the settings.  This property can be ignored for Conventional databases.

To know more about Informatica

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

Exceptions in Informatica

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

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:

1. The order of ports may be wrong
2. The number of ports in the transformation may be more/less.
3. 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

1. Use DataDirect Oracle ODBC driver instead of the driver “Oracle in
2. 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

1. Increase the Virtual Memory in the system
2. 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.

Read More about Informatica