Monday, 21 July 2014

Troubleshooting: Common Informatica Error Messages and the resolution


Common Informatica Error Messages and the resolution:

1)
ERROR  7/21/2014 5:16:25 PM    READER_1_1_1 HIER_28056        XML Reader: Error [ExpectedCommentOrPI] occurred while parsing:[FATAL: Error at (file /data/pmrootfolder/test.xml, line 1, char 28236 ): Expected comment or processing instruction.]; line number [1]; column number [28236]
Database Error: Failed to connect to database using user [TESTDB] and connection string [TOREST].].
ERROR  7/16/2014 10:40:18 AM  READER_1_1_1 BLKR_16001        Error connecting to database...


RESOLUTION: Check connectivity to database. If not able to connect to database, please contact the dba to resolve the issue with database.

For XML writer issues check also:
http://dwbitechguru.blogspot.ca/2014/11/xml-customizations-in-informatica_88.html


2)
Error message for the failure :
ERROR : (8806 | TRANSF_1_1_1) : (IS | ETL_IS) : : pmsql_50065 : [ERROR] ODL error:
 FnName: Execute -- Communication link failure.


RESOLUTION: Check connectivity to integration server or database.





Error connecting to database [
[IBM][CLI Driver] SQL30081N  A communication error has been detected.  Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "172.168.10.1".  Communication function detecting the error: "connect".  Protocol specific error code(s): "146", "*", "*".  SQLSTATE=08001
 


RESOLUTION: Check connectivity to database.



3)
FATAL   9/9/2013 9:31:28 AM      *********** FATAL ERROR : An unexpected condition occurred in file [/pmroot/pc9x_root/910HF/build/powrmart/server/cmnutils/soutstream.cpp] line [214].  Aborting the DTM process.  Contact Informatica Global Customer Support. *********** 

RESOLUTION: This is bug with informatica 9 version. Please recover the session and it should work properly.



4)
ERROR    9/13/2013 6:20:08 AM           DIRECTOR               REP_12400              Repository Error (
ORA-30032: the suspended (resumable) statement has timed out
ORA-01536: space quota exceeded for tablespace 'TEST_DATA'

RESOLUTION: Problem with oracle tablespace.Fix the table space issue.



5)
ERROR  4/5/2013 4:21:40 AM      WRITER_1_*_1 Net_1762            [ERROR] Connection timeout expired
ERROR  4/5/2013 4:21:40 AM      WRITER_1_*_1 Net_1762            [ERROR] Integration Service could not connect to Teradata Performance Server.

RESOLUTION: Check connectivity to database.If not able to connect to database, please contact the dba to resolve the issue with database.



6)
ERROR : (26061 | POST-SESS) : (IS | ETL_IS) : : CMN_1949 : Error: [Pre/Post Session Command] Process id 26217. The shell command failed with exit code 1.


RESOLUTION: Check the post session command and resolve the issue with this post session command.


7)
XML Reader: Error [XMLException_Fatal] occurred while parsing:[FATAL: Error at (file EMPTY, line 1, char 664 ): An exception occurred! Type:UnexpectedEOFException, Message:The end of input was not expected.]; line number [1]; column number [664]
HIER_28058    XML Reader Error
TRANSF_1_1_1    MXR_91003    Failed to process an XML document.

Resolution: I was reading an XML file for parsing and got the above error. The file was read as a string and somehow the string was getting truncated. Then I found the reason was that the column delimiter was comma (,) in the file properties for that input file on the session and comma was in the middle of the file I was reading and hence it was not reading the complete file. Fixed the issue by changing the file delimiter to something else like \050 and was able to read the complete file and parse it. 

See also http://dwbitechguru.blogspot.ca/2014/07/issue-xml-reader-error.html


8)
ERROR    9/8/2014 1:48:14 PM    my_informatica_server.com    MAPPING    CMN_1022    Database driver error...
CMN_1022 [select * from test_table
FnName: Execute Direct -- ERROR:  test_table does not exist]


Resolution: This is result of SQL error. In the above example test table does not exist. Create the table and try again. In general, resolve the SQL error.



9)
Error:
FATAL     *** FATAL ERROR : Failed to allocate memory. Out of virtual memory. *********** 
FATAL     *** FATAL ERROR : Aborting the DTM process due to memory allocation failure. *********** 

Resolution: There is not enough memory to complete the task. Either the swap space or disk space is full on the informatica server. Clear up some space. Recover the failed. Other things you can do is, reduce the cache size and auto memory settings and check if that helps.


10) ERROR READER_1_1_1 RR_4035 SQL Error [
FnName: Bind Col -- Invalid descriptor index
Database driver error...Delayed binding at fetch time failed.].

Resolution: Got this issue when the SQL in my session was different than the SQL in the mapping. The SQL override logic was not mapped properly to the ports in the mapping. Common issue in most of the mapping with SQL override. Always the fields in the SQL of SQ have to be mapped to the outgoing ports of the SQ and not to all of the ports in the SQL.


11)
[ERROR] Web Service invoker encountered an error while invoking the Web Service. Reason:
 List does not exist.
 The page you selected contains a list that does not exist.  It may have been deleted by another user.

Resolution: Got this error when connecting to sharepoint web service. Most likely the reason is you are not connecting properly to the list or the list does not exist on sharepoint and the sharepoint admin has to fix the issue. We had this issue since Informatica was not able to connect to the host server of the sharepoint site. We resolved it by moving to the sharepoint site that informatica could connect properly.


12)
ERROR    11/25/2014 9:36:55 PM    node91_host    TRANSF_1_1_1    JAVA PLUGIN_1762    [ERROR] java.lang.NullPointerException
ERROR    11/25/2014 9:36:55 PM    node91_host    TRANSF_1_1_1    JAVA PLUGIN_1762    [ERROR]     at com.informatica.powercenter.server.jtx.JTXPartitionDriverImplGen.execute(JTXPartitionDriverImplGen.java:419)
ERROR    11/25/2014 9:36:55 PM    node91_host    TRANSF_1_1_1    TM_6085    A fatal error occurred at  transformation [JAV_split_sku], and the session is terminating.

RESOLUTION:
The JAVA transformation is trying to assign null values to some variable or using some operation on a null variable. Filter the records  that have null values for those variables used in the java transformation.


13)
INFO : (7638 | READER_1_1_1) :  node01_DW : FR_3055 : Reading input filenames from the indirect file [/root/informatica/SrcFiles/myfilelist_.ind].
ERROR : (7638 | READER_1_1_1) : : node01_DW : FR_3000 : Error opening file [/root/informatica/SrcFiles/myfilelist_.ind].  Operating system error message [No such file or directory].
ERROR : (7638 | READER_1_1_1) :   : BLKR_16002 : ERROR: Initialization failed.

Resolution: The indirect file you are referring to does not exist. Create the file or check that path of the file.


14) ERROR      node91    TRANSF_1_1_1    JAVA PLUGIN_1762    [ERROR] Failed to bind column with index [0] to data type [BIG_DECIMAL]
ERROR   node91    TRANSF_1_1_1    JAVA PLUGIN_1762    [ERROR]     at com.informatica.powercenter.sdk.server.IBufferInit.bindColumnDataType(IBufferInit.java:96)
ERROR    node91   TRANSF_1_1_1    JAVA PLUGIN_1762    [ERROR]     at com.informatica.powercenter.server.jtx.JTXPartitionDriverImplGen.init(JTXPartitionDriverImplGen.java:74)

Resolution: This is caused of high precision settings on the java transformation and the session. Enable high precision on both the session and the java transformation.To enable high precision check out :
http://dwbitechguru.blogspot.ca/2014/12/informatica-java-transformation-failed.html



15) Error: RR_4035 SQL Error [
FnName: Execute -- ERROR: 191744 : Not enough memory for n-squared join
FnName: Execute -- 523 158].

Resolution: This was caused for me because I had two joins and the second join which was a master outer join I believe did not have enough memory.



16) Error: (95_IS 1/22/2015 12:11:12 PM) Abort Workflow: Request acknowledged
(95_IS 1/22/2015 12:11:12 PM) Abort Workflow: ERROR: Cannot stop or abort workflow [id = 1878] or a task within the workflow. The specified run id [3181132] is not found on this Integration Service.

Resolution: The workflow is in failed status so not able to abort it. Restart the workflow. If you want to recover the workflow, then let it suspend when it fails.


17) ERROR    1/18/2010 5:32:02 AM    nmynod_95    MAPPING    CMN_1022    Database driver error...CMN_1022 [update employeed set myflag='Y'
FnName: Execute Direct -- ERROR:  Concurrent update or delete of same row [tbl 669019 dsid 40 tx 0x89ec1c U prev 0x89ebc2]

Resolution: Two sessions trying to update or insert records to the same table. Let one of the session complete and then recover this session.



18) Error loading into target [MY_TARGET_TABLE] : Bad rows exceeded Session Threshold [1]
 WRT_8333 : Rolling back all the targets due to fatal session error.

Resolution: This errors occurs where are more then one transformation errors that occurs with the input data. If you set Bad row  session threshold (Set on Error = 5) in the session properties then upto 5 errors rows will be ignored and the 6th one will result in session to fail. To resolve this either increase the session error threshold or fix the mapping to handle the bad data.



19) Error: [Pre/Post Session Command] Process id 15555. The shell command failed with exit code 1.

Resolution: the pre or post session command failed and the session is configured to fail if the pre/post session command fails. Find the reason why the command is failing and fix it. If it pre command that failed then most likely you have start from the begining. If the post
session command failed then you can just run that post command separately.



20)ERROR    2/19/2015 10:48:52 AM    mynode WRITER_1_*_1    WRT_8229    Database errors occurred: ORA-26002: Table OJ_X.MYTABLE_WORK has index defined upon it.
Database driver error...
Function Name : Prepare DPL
SQL Stmt : INSERT INTO OJ_X.MYTABLE_WORK (COLUMN1,)  VALUES ( ?)
Oracle Fatal Error

Resolution: This error was caused because of Bulk mode turned on in the target setting in the session. Change it to Normal mode and it should solve it.


 21)*********** FATAL ERROR : An unexpected condition occurred in file [/export/home/builds/pc9x_root/910HF/build/powrmart/common/utils/ublkdesc.cpp] line [338].  Aborting the DTM process. 

Resolution: There are few resolutions that I found for this. For me, this issue was caused by insufficient memory so change the DTM buffer size to Auto looks like fixed it. Everytime I recovered the session, it used to work. There are other resolutions that I found, https://community.informatica.com/message/53392
https://community.informatica.com/message/124778#124778
https://community.informatica.com/message/124868#124868



22) ERROR  4/30/2015 5:55:34 PM    node01      READER_1_3_1 RR_4035               SQL Error [
[IBM][CLI Driver][DB2] SQL0805N  Package "MYDATABASE.NULLID.SYSSH200.5359534C564C3031" was not found.  SQLSTATE=51002
sqlstate = 51002


Resolution: Something to do with binding problem with DB2. http://www-01.ibm.com/support/docview.wss?uid=swg21574086 discusses about this. Issue /usr/local/bin/db2ls command to see the installation path of DB2. Ask your DBA to resolve this binding issue. After the issue is resolved, If your DB2 Powercenter module connection does not work, then try setting up odbc connection in odbc.ini and check if you can connect using odbc.


23:) ERROR  9/22/2015 2:26:45 PM    node01 WRITER_1_*_1 WRT_8229           Database errors occurred:
ORA-26002: Table MYTESTDB.MTYTABLE has index defined upon it. 
Database driver error...
Function Name : Prepare DPL
SQL Stmt : INSERT INTO MYTABLE(BATCH_ID,BATCH_START_DATETIME)  VALUES ( ?, ?,)
Oracle Fatal Error

Resolution: This happened because I was trying to insert data in bulk mode to the oracle target that has index defined upon it. I changed the target load type to Normal and it resolved the issue. You can do this in the session - > mapping -> target properties for that target table.


24: Issue with importing workflow or mappings in Informatica Cloud services: The below message appears:

Select the PowerCenter Workflow XML that was exported from PowerCenter Repository Manager.
  • javax.xml.transform.TransformerException: Unable to evaluate expression using this context
Resolution: The above message appears because you did not export the objects from the repository manager. This issue should be resolved if you export it from the repository manager and then try to import it into Informatica cloud services as a power center task.