When running Load Data Warehouse - Full after upgrade of APM and PMO, it's persistently failing with error:
Execute SQL script - org.pentaho.di.core.exception.KettleStepException:
Error while running this step!
Couldn't execute SQL: ALTER TABLE DWH_INV_APPLICATION ENABLE VALIDATE CONSTRAINT DWH_INV_APPLICATION_FK1
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_INV_APPLICATION_FK1) - parent keys not found
Nothing else is running at the time of the execution.
Release : All Clarity releases with DWH
Component : CA PPM DATA WAREHOUSE
Try the following:
1. Connect to Data Warehouse database
2. Run the SQL query:
delete from DWH_INV_APPLICATION where INVESTMENT_KEY not in
(select INVESTMENT_KEY from DWH_INV_INVESTMENT)
Commit
3. Now run a Load Data Warehouse - Full Load
If this does not help:
This issue can be due to orphan investment. To identify the investment, please run:
select * from DWH_INV_APPLICATION where INVESTMENT_KEY not in
(select INVESTMENT_KEY from DWH_INV_INVESTMENT)
By the ID try opening the investment in UI, if you have errors, that's a good indication there is a problem.
If the investment is corrupted we recommend marking it for deletion and running Delete Investments job.