SIEBEL IF_ROW_STAT IN_PROGRESS due to SBL-EIM-00107

Error: IF_ROW_STAT shows IN_PROGRESS after EIM process has completed.

Possible Cause: Unique constraint violated.  Check the EIM log file for the definite cause.  If you don't find anything in the EIM log file, try increasing the log level.  If trying to populate s_loy_txn for example, and we get IF_ROW_STAT IN_PROGRESS, then if the problem is truly because of the unique constraint being violated then we should expect to see the following in the EIM log file:

[DataDirect][ODBC Oracle driver][Oracle]ORA-00001: unique constraint (SIEBEL.S_LOY_TXN_U1) violated
Error SBL-EIM-00107: ODBC error 23000:
[DataDirect][ODBC Oracle driver][Oracle]ORA-00001: unique constraint
(SIEBEL.S_LOY_TXN_U1) violated
(native error 1)
Error SBL-EIM-00107: ODBC error 23000:
[DataDirect][ODBC Oracle driver][Oracle]ORA-00001: unique constraint
(SIEBEL.S_LOY_TXN_U1) violated
(native error 1)
Step 9: inserting new rows S_LOY_TXN 1s
Process [Import EIM_LOY_TXN] had all rows fail
on EIM_LOY_TXN for batch 6020000 in step 9, pass 528:
Import processing aborted due to SQL error. (severity 1, table aborted)
A low-level error occurred during import processing for this table. The error
could not be handled during the step in which it occurred and caused all import
processing for that table to stop.
ODBC error 23000: [DataDirect][ODBC Oracle driver][Oracle]ORA-00001: unique
onstraint (SIEBEL.S_LOY_TXN_U1) violated
This low-level failure caused all processing to halt for the specified
interface table. Check the error messages for more information.
 
Possible Solution: Ensure that you are populating the row_id of the corresponding EIM table with unique values for each rows.

Informatica: Lookup not returning data

Symptom: A lookup in informatica is not returning a row or rows of data, even if it is being supplied with the correct lookup condition values.

Possible Solution: If the field used in the lookup condition is type text in the database and that same field is being treated as type number in the informatica mapping, then this might cause the lookup not to work properly. In informatica, most probably, the field value will be having a decimal followed by zeroes since it is being treated as a number.  This will not be the case in the database, since it is of type Text. So in the lookup we will be doing a comparison such as "20.0000000 = 20", for example and it will not find the record.  To prevent such a situation, in the informatica mapping, an expression such as TO_CHAR(ROUND(FIELD_NAME,0)) can be used to convert the field value to type Text just prior to sending it as input to the lookup.  This solution will work, if the value stored in database is a whole number.

Informatica Database Error

Error Message: SQL Server Message: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'XXX'. The conflict occurred in database 'YYY', table 'ZZZ', column 'AAA'.

Issue Description: When running a session in informatica, the session succeeded, but there were target failed rows.  Upon inspection of the session log file, the following database error was noted: SQL Server Message: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'XXX'. The conflict occurred in database 'YYY', table 'ZZZ', column 'AAA'.

Issue Resolution: The informatica mapping was correct.  The problem was that there were parent-child tables that were being populated and child records were being populated before the parent records.  Due to this, at database level, since the parent record did not exist at the time the child was being populated, the record was rejected to maintain the database integrity.  To solve this:

(1) You can enable constraint based loading for that session.  For this, the parent-child relationships need to be defined at informatica level as well, in the warehouse designer.

(2) Modify the target load plan in designer.

(3) Remove the foreign key constraint at database level if you are sure that the data being populated by informatica will not corrupt the database integrity.  However, this solution is not recommended.