Incr Load DWH job fails 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 Load Data Warehouse (DWH) fails when multiple languages are added to DWH after updating / creating a status report

Steps to Reproduce:

  1. DWH languages added: Spanish, Portuguese, English (Administration > General Settings > System Options)
  2. Run Load DWH Full is OK
  3. Go to a project which has got an existing Status Report. Modify and save
  4. Create also another Status Report on the same project
  5. Run Load DWH incrementally 

Expected Results: The job succeeds
Actual result: The job fails with 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 case, all 3 values were 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 the environment where the issue was 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 on 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
     USING (select CHANGE_EXPLANATION, CLARITY_CREATED_DATE, CLARITY_STATUS_REPORT_KEY, CLARITY_UPDATED_DATE, COST_EFFORT_EXPLANATION, INVESTMENT_KEY, IS_DELIVERABLE_SCOPE_CHANGE, IS_NEXT_MILESTONE_ON_TRACK, IS_PROJECT_EXTERNAL_FACTORS, IS_PROJECT_OBJECTIVE_CHANGE, IS_REVIEW_APPROVAL_PROBLEMS, IS_SCOPE_CHANGE_REQUIRED, IS_STAFF_AVAIL_ISSUES, KEY_ACCOMPLISHMENTS, OVERALL_STATUS, REPORT_DATE, REPORT_UPDATE, SR_COST_EFFORT_STATUS_KEY, SR_REPORT_STATUS_KEY, SR_SCHEDULE_STATUS_KEY, SR_SCOPE_STATUS_KEY, STATUS_REPORT_ID, STATUS_REPORT_KEY, STATUS_REPORT_NAME, UPCOMING_ACTIVITIES, VARIANCE_EXPLANATION, to_date('2020/01/13 14:10:11', 'yyyy/mm/dd HH24:MI:SS') as dw_updated_date  from [email protected] A where CLARITY_UPDATED_DATE >= to_date('2020/01/13 10:03:33', 'yyyy/MM/dd HH24:mi:ss') AND CLARITY_UPDATED_DATE <= (SELECT DISTINCT DWH_DIM_START_DATE FROM DWH_CFG_SETTINGS)) SRC ON (SRC.STATUS_REPORT_KEY = TGT.STATUS_REPORT_KEY)
WHEN MATCHED THEN UPDATE SET  TGT.CHANGE_EXPLANATION = SRC.CHANGE_EXPLANATION, TGT.CLARITY_CREATED_DATE = SRC.CLARITY_CREATED_DATE, TGT.CLARITY_STATUS_REPORT_KEY = SRC.CLARITY_STATUS_REPORT_KEY, TGT.CLARITY_UPDATED_DATE = SRC.CLARITY_UPDATED_DATE, TGT.COST_EFFORT_EXPLANATION = SRC.COST_EFFORT_EXPLANATION, TGT.INVESTMENT_KEY = SRC.INVESTMENT_KEY, TGT.IS_DELIVERABLE_SCOPE_CHANGE = SRC.IS_DELIVERABLE_SCOPE_CHANGE, TGT.IS_NEXT_MILESTONE_ON_TRACK = SRC.IS_NEXT_MILESTONE_ON_TRACK, TGT.IS_PROJECT_EXTERNAL_FACTORS = SRC.IS_PROJECT_EXTERNAL_FACTORS, TGT.IS_PROJECT_OBJECTIVE_CHANGE = SRC.IS_PROJECT_OBJECTIVE_CHANGE, TGT.IS_REVIEW_APPROVAL_PROBLEMS = SRC.IS_REVIEW_APPROVAL_PROBLEMS, TGT.IS_SCOPE_CHANGE_REQUIRED = SRC.IS_SCOPE_CHANGE_REQUIRED, TGT.IS_STAFF_AVAIL_ISSUES = SRC.IS_STAFF_AVAIL_ISSUES, TGT.KEY_ACCOMPLISHMENTS = SRC.KEY_ACCOMPLISHMENTS, TGT.OVERALL_STATUS = SRC.OVERALL_STATUS, TGT.REPORT_DATE = SRC.REPORT_DATE, TGT.REPORT_UPDATE = SRC.REPORT_UPDATE, TGT.SR_COST_EFFORT_STATUS_KEY = SRC.SR_COST_EFFORT_STATUS_KEY, TGT.SR_REPORT_STATUS_KEY = SRC.SR_REPORT_STATUS_KEY, TGT.SR_SCHEDULE_STATUS_KEY = SRC.SR_SCHEDULE_STATUS_KEY, TGT.SR_SCOPE_STATUS_KEY = SRC.SR_SCOPE_STATUS_KEY, TGT.STATUS_REPORT_ID = SRC.STATUS_REPORT_ID, TGT.STATUS_REPORT_NAME = SRC.STATUS_REPORT_NAME, TGT.UPCOMING_ACTIVITIES = SRC.UPCOMING_ACTIVITIES, TGT.VARIANCE_EXPLANATION = SRC.VARIANCE_EXPLANATION, TGT.dw_updated_date = SRC.dw_updated_date 
WHEN NOT MATCHED THEN
                INSERT (CHANGE_EXPLANATION, CLARITY_CREATED_DATE, CLARITY_STATUS_REPORT_KEY, CLARITY_UPDATED_DATE, COST_EFFORT_EXPLANATION, INVESTMENT_KEY, IS_DELIVERABLE_SCOPE_CHANGE, IS_NEXT_MILESTONE_ON_TRACK, IS_PROJECT_EXTERNAL_FACTORS, IS_PROJECT_OBJECTIVE_CHANGE, IS_REVIEW_APPROVAL_PROBLEMS, IS_SCOPE_CHANGE_REQUIRED, IS_STAFF_AVAIL_ISSUES, KEY_ACCOMPLISHMENTS, OVERALL_STATUS, REPORT_DATE, REPORT_UPDATE, SR_COST_EFFORT_STATUS_KEY, SR_REPORT_STATUS_KEY, SR_SCHEDULE_STATUS_KEY, SR_SCOPE_STATUS_KEY, STATUS_REPORT_ID, STATUS_REPORT_KEY, STATUS_REPORT_NAME, UPCOMING_ACTIVITIES, VARIANCE_EXPLANATION, dw_updated_date)
                VALUES (SRC.CHANGE_EXPLANATION, SRC.CLARITY_CREATED_DATE, SRC.CLARITY_STATUS_REPORT_KEY, SRC.CLARITY_UPDATED_DATE, SRC.COST_EFFORT_EXPLANATION, SRC.INVESTMENT_KEY, SRC.IS_DELIVERABLE_SCOPE_CHANGE, SRC.IS_NEXT_MILESTONE_ON_TRACK, SRC.IS_PROJECT_EXTERNAL_FACTORS, SRC.IS_PROJECT_OBJECTIVE_CHANGE, SRC.IS_REVIEW_APPROVAL_PROBLEMS, SRC.IS_SCOPE_CHANGE_REQUIRED, SRC.IS_STAFF_AVAIL_ISSUES, SRC.KEY_ACCOMPLISHMENTS, SRC.OVERALL_STATUS, SRC.REPORT_DATE, SRC.REPORT_UPDATE, SRC.SR_COST_EFFORT_STATUS_KEY, SRC.SR_REPORT_STATUS_KEY, SRC.SR_SCHEDULE_STATUS_KEY, SRC.SR_SCOPE_STATUS_KEY, SRC.STATUS_REPORT_ID, SRC.STATUS_REPORT_KEY, SRC.STATUS_REPORT_NAME, SRC.UPCOMING_ACTIVITIES, SRC.VARIANCE_EXPLANATION, SRC.dw_updated_date)
;
COMMIT;
EXCEPTION
    WHEN OTHERS THEN
    BEGIN
        V_SQLCODE := SQLCODE;
        V_SQLERRM := SQLERRM;
        V_SQL_TEXT := 'MERGE INTO DWH_INV_STATUS_REPORT TGT USING (select CHANGE_EXPLANATION, CLARITY_CREATED_DATE, CLARITY_STATUS_REPORT_KEY, CLARITY_UPDATED_DATE, COST_EFFORT_EXPLANATION, INVESTMENT_KEY, IS_DELIVERABLE_SCOPE_CHANGE, IS_NEXT_MILESTONE_ON_TRACK, IS_PROJECT_EXTERNAL_FACTORS, IS_PROJECT_OBJECTIVE_CHANGE, IS_REVIEW_APPROVAL_PROBLEMS, IS_SCOPE_CHANGE_REQUIRED, IS_STAFF_AVAIL_ISSUES, KEY_ACCOMPLISHMENTS, OVERALL_STATUS, REPORT_DATE, REPORT_UPDATE, SR_COST_EFFORT_STATUS_KEY, SR_REPORT_STATUS_KEY, SR_SCHEDULE_STATUS_KEY, SR_SCOPE_STATUS_KEY, STATUS_REPORT_ID, STATUS_REPORT_KEY, STATUS_REPORT_NAME, UPCOMING_ACTIVITIES, VARIANCE_EXPLANATION, to_date(**2020/01/13 14:10:11**, **yyyy/mm/dd HH24:MI:SS**) as dw_updated_date  from [email protected] A where CLARITY_UPDATED_DATE >= to_date(**2020/01/13 10:03:33**, **yyyy/MM/dd HH24:mi:ss**) AND CLARITY_UPDATED_DATE <= (SELECT DISTINCT DWH_DIM_START_DATE FROM DWH_CFG_SETTINGS)) SRC ON (SRC.STATUS_REPORT_KEY = TGT.STATUS_REPORT_KEY) WHEN MATCHED THEN UPDATE SET  TGT.CHANGE_EXPLANATION = SRC.CHANGE_EXPLANATION, TGT.CLARITY_CREATED_DATE = SRC.CLARITY_CREATED_DATE, TGT.CLARITY_STATUS_REPORT_KEY = SRC.CLARITY_STATUS_REPORT_KEY, TGT.CLARITY_UPDATED_DATE = SRC.CLARITY_UPDATED_DATE, TGT.COST_EFFORT_EXPLANATION = SRC.COST_EFFORT_EXPLANATION, TGT.INVESTMENT_KEY = SRC.INVESTMENT_KEY, TGT.IS_DELIVERABLE_SCOPE_CHANGE = SRC.IS_DELIVERABLE_SCOPE_CHANGE, TGT.IS_NEXT_MILESTONE_ON_TRACK = SRC.IS_NEXT_MILESTONE_ON_TRACK, TGT.IS_PROJECT_EXTERNAL_FACTORS = SRC.IS_PROJECT_EXTERNAL_FACTORS, TGT.IS_PROJECT_OBJECTIVE_CHANGE = SRC.IS_PROJECT_OBJECTIVE_CHANGE, TGT.IS_REVIEW_APPROVAL_PROBLEMS = SRC.IS_REVIEW_APPROVAL_PROBLEMS, TGT.IS_SCOPE_CHANGE_REQUIRED = SRC.IS_SCOPE_CHANGE_REQUIRED, TGT.IS_STAFF_AVAIL_ISSUES = SRC.IS_STAFF_AVAIL_ISSUES, TGT.KEY_ACCOMPLISHMENTS = SRC.KEY_ACCOMPLISHMENTS, TGT.OVERALL_STATUS = SRC.OVERALL_STATUS, TGT.REPORT_DATE = SRC.REPORT_DATE, TGT.REPORT_UPDATE = SRC.REPORT_UPDATE, TGT.SR_COST_EFFORT_STATUS_KEY = SRC.SR_COST_EFFORT_STATUS_KEY, TGT.SR_REPORT_STATUS_KEY = SRC.SR_REPORT_STATUS_KEY, TGT.SR_SCHEDULE_STATUS_KEY = SRC.SR_SCHEDULE_STATUS_KEY, TGT.SR_SCOPE_STATUS_KEY = SRC.SR_SCOPE_STATUS_KEY, TGT.STATUS_REPORT_ID = SRC.STATUS_REPORT_ID, TGT.STATUS_REPORT_NAME = SRC.STATUS_REPORT_NAME, TGT.UPCOMING_ACTIVITIES = SRC.UPCOMING_ACTIVITIES, TGT.VARIANCE_EXPLANATION = SRC.VARIANCE_EXPLANATION, TGT.dw_updated_date = SRC.dw_updated_date ' ||
                      'WHEN NOT MATCHED THEN INSERT (CHANGE_EXPLANATION, CLARITY_CREATED_DATE, CLARITY_STATUS_REPORT_KEY, CLARITY_UPDATED_DATE, COST_EFFORT_EXPLANATION, INVESTMENT_KEY, IS_DELIVERABLE_SCOPE_CHANGE, IS_NEXT_MILESTONE_ON_TRACK, IS_PROJECT_EXTERNAL_FACTORS, IS_PROJECT_OBJECTIVE_CHANGE, IS_REVIEW_APPROVAL_PROBLEMS, IS_SCOPE_CHANGE_REQUIRED, IS_STAFF_AVAIL_ISSUES, KEY_ACCOMPLISHMENTS, OVERALL_STATUS, REPORT_DATE, REPORT_UPDATE, SR_COST_EFFORT_STATUS_KEY, SR_REPORT_STATUS_KEY, SR_SCHEDULE_STATUS_KEY, SR_SCOPE_STATUS_KEY, STATUS_REPORT_ID, STATUS_REPORT_KEY, STATUS_REPORT_NAME, UPCOMING_ACTIVITIES, VARIANCE_EXPLANATION, dw_updated_date) VALUES (SRC.CHANGE_EXPLANATION, SRC.CLARITY_CREATED_DATE, SRC.CLARITY_STATUS_REPORT_KEY, SRC.CLARITY_UPDATED_DATE, SRC.COST_EFFORT_EXPLANATION, SRC.INVESTMENT_KEY, SRC.IS_DELIVERABLE_SCOPE_CHANGE, SRC.IS_NEXT_MILESTONE_ON_TRACK, SRC.IS_PROJECT_EXTERNAL_FACTORS, SRC.IS_PROJECT_OBJECTIVE_CHANGE, SRC.IS_REVIEW_APPROVAL_PROBLEMS, SRC.IS_SCOPE_CHANGE_REQUIRED, SRC.IS_STAFF_AVAIL_ISSUES, SRC.KEY_ACCOMPLISHMENTS, SRC.OVERALL_STATUS, SRC.REPORT_DATE, SRC.REPORT_UPDATE, SRC.SR_COST_EFFORT_STATUS_KEY, SRC.SR_REPORT_STATUS_KEY, SRC.SR_SCHEDULE_STATUS_KEY, SRC.SR_SCOPE_STATUS_KEY, SRC.STATUS_REPORT_ID, SRC.STATUS_REPORT_KEY, SRC.STATUS_REPORT_NAME, SRC.UPCOMING_ACTIVITIES, SRC.VARIANCE_EXPLANATION, SRC.dw_updated_date) ';
        ROLLBACK;
        INSERT INTO DWH_CMN_ERROR_MESSAGE VALUES('DWH_INV_STATUS_REPORT',V_SQLERRM,V_SQLCODE,REPLACE(V_SQL_TEXT,'**',''''));
        COMMIT;
        RAISE_APPLICATION_ERROR(-20100,'ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_STATUS_REPORT). SQLERRM : ' || V_SQLERRM);
    END;
END;
 
[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.