DWH job fails with duplicate keys on DWH_TME_ENTRY_FACTS after Time Slice Rollover - DE58253

book

Article ID: 206016

calendar_today

Updated On:

Products

STARTER PACK-CLARITY PPM

Issue/Introduction

DE58253 The Load Data Warehouse (DWH) job is failing with duplicate keys generated on DWH_TME_ENTRY_FACTS after Time Slice Rollover.  

 
STEPS TO REPRODUCE:
1. After Time Slice rollover run Data Warehouse job for an Incremental load

Expected Results: Incremental to run successfully
Actual Results: It fails with error: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
ORA-06512: at "PPM_DWH.DWH_TME_ENTRY_FACTS_LOAD", line 76


 
Full Error message:

ERROR 2020-10-02 20:21:46,486 [dwh_tme_entry_facts UUID: 1c244eec-ffc9-4431-bac6-347972b512a7] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry :
Couldn't execute SQL: BEGIN
DWH_TME_ENTRY_FACTS_LOAD(
   P_DBLINK => 'PPMDBLINK',
   P_LAST_LOAD_DATE => to_date('2020/09/30 22:47:56', 'yyyy/MM/dd HH24:mi:ss'),
   P_CURRENT_DIM_LOAD_DATE => to_date('2020/10/02 12:02:33', 'yyyy/mm/dd HH24:MI:SS'),
           P_ARRAY_SIZE => 50000
);
END;

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_TME_ENTRY_FACTS. SQLERRM : ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INTERNAL_MD. SQLERRM : ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
ORA-06512: at "PPM_DWH.DWH_TME_ENTRY_FACTS_LOAD", line 76
ORA-06512: at line 2

Environment

Release : 15.8.1, 15.9.0

Component : CA PPM DATA WAREHOUSE

Resolution

This is DE58253, affecting 15.8.1 and 15.9.0

Fixed in 15.8.1 for POSTGRES database

Fixed in 15.9.1 for ORACLE database

Additional Information

Similar issue was logged as DE55383(DE54887, DE48982) and fixed in 15.8.1 (POSTGRES only) and 15.9.1 (ORACLE).