Load Data Warehouse (Partial) gives error "DWH_INV_TASK_PERIOD_FACTS_LOAD (P_ARRAY_SIZE => 50000)"

Document ID : KB000097208
Last Modified Date : 21/05/2018
Show Technical Document Details
Question:
A scheduled Load Data Warehouse  (Partial) Job failed with onscreen message:
Job: Load Data Warehouse - Partial 
Run ID: 9643277 
Scheduler ID: <<Servername>>.ondemand.ca.com 
Failure message: NJS-0401: Execution of job failed.

The bg-dwh.log contains the message:
2018/05/20 12:02:37 - ClarityDB - isOracle? - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry : 
2018/05/20 12:02:37 - ClarityDB - isOracle? - Couldn't execute SQL: BEGIN
2018/05/20 12:02:37 - ClarityDB - isOracle? -   DWH_INV_TASK_PERIOD_FACTS_LOAD (P_ARRAY_SIZE => 50000);
2018/05/20 12:02:37 - ClarityDB - isOracle? - END;
2018/05/20 12:02:37 - ClarityDB - isOracle? - 
2018/05/20 12:02:37 - ClarityDB - isOracle? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INV_TASK_PERIOD_FACTS_LOAD. SQLERRM : ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INTERNAL_MD. SQLERRM : ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
2018/05/20 12:02:37 - ClarityDB - isOracle? - ORA-06512: at "<<SERVER NAME>>.DWH_INV_TASK_PERIOD_FACTS_LOAD", line 56

The above errors set is from CA PPM 15.2, Patch 3. However, the issue has also been seen on CA PPM 15.3.
Environment:
CA PPM 15.2 - 15.3.
Answer:
This is addressed by DE37008 in CA PPM versions:
  • CA PPM 15.2 Patch 7.
  • CA PPM 15.3 Patch 3
  • CA PPM 15.4 base.
Please see your relevant version of CA PPM patch notes, such as On-Premise or SaaS, and the version number.

For example, for CA PPM 15.3 SaaS:

See here CA PPM 15.3 Cumulative Patch 2 (15.3.0.2) General Availability Announcement

Which has links to here:
On Premise: https://docops.ca.com/x/34JEGQ
SaaS or On Demand: https://docops.ca.com/x/O4REGQ

The latter links to here:
https://docops.ca.com/ca-ppm-saas/15-3/en/release-information/ca-ppm-15-3-0-3-and-jaspersoft-cumulative-patch-readme-files

Which contains this patch:

DE37008: Load Data Warehouse - Incremental fails with error ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at "PPM_DWH152.DWH_INV_TASK_PERIOD_FACTS_LOAD"

Steps to Reproduce:
1. Ensure all pre-requisites for the Data Warehouse were met
2. Run the Load Data Warehouse - Full Job
3. Note that it completes successfully
4. Now run the Load Data Warehouse - Incremental Job

Expected Result: The incremental Load Data Warehouse Job should completely successfully
Actual Result: The jobs fails with the error message above 


Best practice would be to move to CA PPM 15.4 if possible, to gather this patch and other patches which have been through a combined QA.

Workaround

This issue may be worked around temporarily by running a "Full" Load Data Warehouse Job instead of a "Partial" Job.