ETL results in error for one universe in CA Governance

Products

• CA Identity Suite

Issue/Introduction

When we run ETL for the Universe, it results in the following error 

Caused by: com.eurekify.i2.core.etl.process.exception.EtlProcessException: Error during process of the I2_SP_DW2 stored procedure
    at com.eurekify.i2.core.etl.process.step.DW12Impl.DW2Execute(DW12Impl.java:75) [classes:]
    at com.eurekify.i2.etl.rcm_12_5.step.DW2Executer.execute(DW2Executer.java:37) [classes:]
    ... 28 more
Caused by: java.sql.SQLException: ORA-20001: ERROR WHILE EXECUTING THE I2_SP_DW2 STORED PROCEDURE -20001 -ERROR- ORA-20001: ERROR WHILE EXECUTING THE I2_SP_CREATEDWCONSTRAINTS STORED PROCEDURE -2298 -ERROR- ORA-02298: cannot validate (CAIG_REPORT_DB.I2FK_ACCSCD_PEN_ENTITYID) - parent keys not found
ORA-06512: at "CAIG_REPORT_DB.I2_SP_DW2", line 12

Environment

Release : Identity Governance 14.3

Cause

These are the orphan entries in I2_ACCOUNT_SCD table (with no corresponding foreign keys in I2_PARTY_ENTITY table ) 

Resolution

1. Execute the following query on reporting database to find the orphan entries in I2_ACCOUNT_SCD table.

select acc.client_id, acc.entity_id
from i2_account_scd acc
left join i2_party_entity par on acc.client_id = par.client_id and acc.entity_id = par.entity_id
where par.client_id is null and par.entity_id is null;

 

2. Run the following example query. You will need to put the list of orphan entries that was the result of the top queries

example

delete from i2_account_scd acc
where acc.client_id = '18037' and
acc.entity_id in ('-8459725735791385162', '-8443926764384656806');  

 

to delete the orphan entries.