We run Load Data Warehouse - Incremental, and it fails with [CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_FIN_TRANSACTION_
Full error below:
ERROR 2020-01-28 12:42:23,328 [dwh_etl_interface_dim UUID: 654c8d3e-0847-4311-beb5-1c63b96ee922] dwh.event dwh_enable_constraints_oracle - Errors detected!
INFO 2020-01-28 12:42:23,333 [Thread-516738] dwh.event null - Job execution finished
ERROR 2020-01-28 12:42:23,415 [Dispatch Load Data Warehouse : [email protected] (tenant=clarity)] dwh.event ETL Job Failed. Log details below:
2020/01/28 12:42:23 - Execute SQL script.0 - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Unexpected error
2020/01/28 12:42:23 - Execute SQL script.0 - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : org.pentaho.di.core.exception.KettleStepException:
2020/01/28 12:42:23 - Execute SQL script.0 - Error while running this step!
2020/01/28 12:42:23 - Execute SQL script.0 -
2020/01/28 12:42:23 - Execute SQL script.0 - Couldn't execute SQL: ALTER TABLE DWH_FIN_TRANSACTION_LN ENABLE VALIDATE CONSTRAINT DWH_FIN_TRANSACTION_LN_FK1
2020/01/28 12:42:23 - Execute SQL script.0 -
2020/01/28 12:42:23 - Execute SQL script.0 - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_FIN_TRANSACTION_LN_FK1) - parent keys not found
2020/01/28 12:42:23 - Execute SQL script.0
Release : 15.5.1, 15.6, 15.6.1, 15.7, 15.7.1
Component : CA PPM DATA WAREHOUSE
This is a known defect: DE52941, fixed in 15.7.1.2 and 15.8
STEPS TO REPRODUCE:
1. Login to Clarity PPM.
2. Schedule Load Data Warehouse - Incremental to run every hour.
3. Adjust around 10K transactions through WIP adjustment.
4. Post to WIP those adjustments while the Load Data Warehouse job is executing.
Expected Results :- Load Data Warehouse - Incremental shouldn't have any issues.
Actual Results :- Load Data Warehouse - Incremental fails with error ORA-02298: cannot validate (PPM_DWH.DWH_FIN_TRANSACTION_LN_FK1) - parent keys not found when transactions are made whilst the job is running
Workaround: Avoid adjusting WIP transactions when the Incremental Load is running.
Solution: Upgrade to 15.7.1.2 or 15.8 (and higher)