Incremental Load DWH Job Fails Intermittently on DWH_X_INV_TASK_SUM_FACTS Table

Products

• Clarity PPM On Premise

Issue/Introduction

The Incremental Load DWH job fails with duplicate insert value in DWH_X_INV_TASK_SUM_FACTS table.

Schedule incremental job to run before the full Load DWH job instance runs.

Expected Results: The job should complete if there is no metadata change

Actual Results: The job fails with error

--SQL Server
2020/06/01 19:03:41 - Error for table 'DWH_X_INV_TASK_SUM_FACTS' - Error Number 1505 'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_X_INV_TASK_SUM_FACTS' and the index name 'DWH_X_INV_TASK_SUM_FACTS_U1'. The duplicate key value is (21658340).'
2020/06/01 19:03:41 - Abort job - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Aborting job.
2020/06/01 19:03:41 - Error for table 'DWH_X_INV_TASK_SUM_FACTS' - Error Number 1505 'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_X_INV_TASK_SUM_FACTS' and the index name 'DWH_X_INV_TASK_SUM_FACTS_U1'. The duplicate key value is (21658340).'
2020/06/01 19:03:41 - Abort job - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Aborting job.
2020/06/01 19:03:41 - Abort job - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Aborting job.
2020/06/01 19:03:41 - Error for table 'DWH_X_INV_TASK_SUM_FACTS' - Error Number 1505 'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_X_INV_TASK_SUM_FACTS' and the index name 'DWH_X_INV_TASK_SUM_FACTS_U1'. The duplicate key value is (21658340).'
null

--Oracle
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INV_TASK_SUMMARY_FACTS. SQLERRM: ORA-00001: unique constraint(PPM_DWH.DWH_INV_TASK_SUM_FACTS_N1) violated
ORA-06512: at "PPM_DWH.DWH_INV_TASK_SUM_FACTS_LOAD", line 77

Workaround: Run the full Load DWH job

 

Resolution

This is reported as DE55404 and is resolved in 15.9+