Incr Load DWH job fails with error "ORA-30926" on DWH_INV_STATUS_REPORT
search cancel

Incr Load DWH job fails with error "ORA-30926" on DWH_INV_STATUS_REPORT

book

Article ID: 143706

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

Incremental (Incr) Load Data Warehouse (DWH) fails when multiple languages are added to the DWH after updating / creating a status report. Error:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_STATUS_REPORT). SQLERRM : ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at line 24 

Steps to Reproduce:

  1. Go to Administration > General Settings > System Options and add the following DWH languages
    • Spanish
    • Portuguese
    • English
  2. Run Load DWH job Full
  3. Go to a project which has an existing status report.
  4. Modify the status report and save
  5. Create another Status Report on the same project
  6. Run the Load DWH incrementally 

Expected Results: The job succeeds
Actual result: The job fails with the following reference in logs:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_STATUS_REPORT). SQLERRM : ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at line 24 

Cause

The DWH schema dwh_meta_tables configuration is not correct

Run this query against the DWH dwh_meta_tables to identify if you are hitting this issue:

SELECT dwh_table, has_extension_table, is_extend, multi_lang FROM dwh_meta_tables WHERE dwh_table = 'DWH_INV_STATUS_REPORT'

The results should be: has_Extension_table = 1, is_extend = 0, multi_lang = 1

In this example, all 3 values are 0

On a healthy environment:

  • DWH_COP_PRJ_STATUSRPT_V at PPM schema contains 3 records per Status Report. One per language (there is a language identifier on the table)
  • DWH_INV_STATUS_REPORT at DWH schema shows 1 per status report (regardless of how many languages are added)
     
    select * from DWH_COP_PRJ_STATUSRPT_V where investment_key = 5003001
    3 records per Status Report

    select * from DWH_INV_STATUS_REPORT where investment_key = 5003001
    1 record per Status Report

On an environment where the issue is encountered:

  • DWH_COP_PRJ_STATUSRPT_V at PPM schema contains 3 records per Status Report. One per language (there is a language identifier on the table)
  • DWH_INV_STATUS_REPORT at DWH schema shows 3 per status report (same amount of records as on the PPM schema)

Error message in logs:

ERROR 2020-01-13 14:10:30,281 [Thread-903258] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry : 
Couldn't execute SQL: DECLARE
    V_SQL_TEXT CLOB;
    V_SQLCODE NUMBER;
    V_SQLERRM VARCHAR2(4000);
BEGIN
MERGE INTO DWH_INV_STATUS_REPORT TGT
     
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_STATUS_REPORT). SQLERRM : ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at line 24 

Resolution

  1. Update the seed data in the PPM schema:

    UPDATE dwh_meta_tables set has_extension_table = 1, multi_lang = 1 WHERE dwh_table_name = 'dwh_inv_status_report'

  2. Run the Full Load DWH job as this will update the DWH schema properly.

Additional Information

Reference also:

Powered by Wolken Software