DWH fails with ORA-02298: cannot validate - parent keys not found (DE61075)

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

DWH load failure with the same error.  This is something we started to see after our 15.9.1 upgrade.  It happened in all our environments and with full and incremental loads. 

ERROR 2021-04-29 13:47:04,753 [dwh_enable_constraints_oracle - Execute SQL script] dwh.event Execute SQL script - org.pentaho.di.core.exception.KettleStepException: 
Error while running this step!

Couldn't execute SQL: ALTER TABLE DWH_INV_TEAM_LN ENABLE VALIDATE CONSTRAINT DWH_INV_TEAM_LN_FK1

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_INV_TEAM_LN_FK1) - parent keys not found

 This is due to table processing order issue. 

STEPS TO REPRODUCE:

1. Run Load Data Warehouse - Incremental on a large dataset
2. Do a change on the teams when it's running (i.e. delete a team member)

Expected Results: DWH_INV_TEAM to be loaded before DWH_INV_TEAM_LN and no error with the job

Actual Results: DWH_INV_TEAM_LN is loaded first as you can see in DWH_CFG_AUDIT, and DWH job fails with error

Couldn't execute SQL: ALTER TABLE DWH_INV_TEAM_LN ENABLE VALIDATE CONSTRAINT DWH_INV_TEAM_LN_FK1

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_INV_TEAM_LN_FK1) - parent keys not found

Note: This may happen on other tables, confirm in DWH_CFG_AUDIT whether there is a problem with the order on a specific one.

 

 

Environment

Release : 15.9.1, 15.9.2

Component : CA PPM DATA WAREHOUSE

Cause

Caused by wrong processing order of the tables

Resolution

This is DE61075, fixed in 15.9.3

Workaround:

1. Run this query on the Clarity database:

update dwh_meta_tables set process_order = '510'
where dwh_table_name ='dwh_inv_team'

1 record should be updated

commit

2. Run a Full Load of DWH.