Informatica Performance Improvement Tips

We often come across situations where Data Transformation Manager (DTM) takes more time to read from Source or when writing in to a Target. Following standards/guidelines can improve the overall performance.

• Use Source Qualifier if the Source tables reside in the same schema
• Make use of Source Qualifer  “Filter” Properties if the Source type is Relational.
• If the subsequent sessions are doing lookup on the same table, use persistent cache in the first session. Data remains in the Cache and available for the subsequent session for usage.
• Use flags as integer, as the integer comparison is faster than the string comparison.
• Use tables with lesser number of records as master table for joins.
• While reading from Flat files, define the appropriate data type instead of reading as String and converting.
• Have all Ports that are required connected to Subsequent Transformations else check whether we can remove these ports
• Suppress ORDER BY using the ‘–‘ at the end of the query in Lookup Transformations
• Minimize the number of Update strategies.
• Group by simple columns in transformations like Aggregate, Source Qualifier
• Use Router transformation in place of multiple Filter transformations.
• Turn off the Verbose Logging while moving the mappings to UAT/Production environment.
• For large volume of data drop index before loading and recreate indexes after load.
• For large of volume of records Use Bulk load Increase the commit interval to a higher value large volume of data
• Set ‘Commit on Target’ in the sessions

To know more about Informatica

Exceptions in Informatica – 2

Let us see few more strange exceptions in Informatica

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

Read More about Informatica