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.

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