Informatica Exceptions – 1
1. While doing “Generate SQL” in Source Qualifier and try to
validate it, getting below error.
“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
sequence of ports 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.
4. E.g., TC_0002.EXP_AUTH@TIMEP
2. When an Oracle table is used, getting below error
“[/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 Data Direct 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. 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.
Informatica Exceptions – 2
1.
When Session fails in informatica 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 – 3
1. 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:
1. Select
the Enable high precision option for the session.
2. Define
all numeric ports as Decimal data type 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).
Oracle Exceptions in Informatica
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.
