DWH Incremental fails after update to Lookups or Picklists - error ORA-30926

Products

• Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse - Incremental fails with error
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_LOOKUP_LOAD (DWH_LKP_TEST_CATEGORY_00). SQLERRM : ORA-30926: unable to get a stable set of rows in the source tables

The Full Load runs fine.

The Incremental fails after adding a new lookup mapping or a local picklist to Roadmap.

 

Environment

Component: PPMDWH

Cause

Lookup TEST_CATEGORY is a Static List with 19 values which are unique.

Issue is related to table: DWH_LKP_TEST_CATEGORY that's corresponding to this lookup in Data Warehouse.

Instead of only having 19 values the Data Warehouse inserts the entire DWH_LOOKUPS_V in there (7000+ values for all the lookups in the system).

This issue can happen when the attribute and DWH view on Clarity are no longer there and only the lookup value in DWH_META_COLUMNS and DWH LKP table in DWH_META_TABLES are there.

Resolution

Manually clean up the lookup orphans
To resolve a similar issue please contact Broadcom Support