Load DWH job fails with error on index DWH_CFG_OBJECT_TYPE_U2
search cancel

Load DWH job fails with error on index DWH_CFG_OBJECT_TYPE_U2

book

Article ID: 191680

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse (DWH) fails immediately with an error in the BG-DWH log: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (SCHEMA.DWH_CFG_OBJECT_TYPE_U2) violated

More detailed error below:

2020/05/20 04:00:50 - ClarityDB - isOracle? - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
2020/05/20 04:00:50 - ClarityDB - isOracle? - Couldn't execute SQL: BEGIN
2020/05/20 04:00:50 - ClarityDB - isOracle? - RPT_CALENDAR_SP();
2020/05/20 04:00:50 - ClarityDB - isOracle? - RPT_INV_HIERARCHY_SP();
2020/05/20 04:00:50 - ClarityDB - isOracle? - RPT_RES_SKILLS_INDEX_SP();
2020/05/20 04:00:50 - ClarityDB - isOracle? - MERGE INTO dwh_cfg_object_type s USING
2020/05/20 04:00:50 - ClarityDB - isOracle? -      (SELECT CASE WHEN o.code = 'application' THEN 50663 
2020/05/20 04:00:50 - ClarityDB - isOracle? -                   WHEN o.code = 'asset' THEN 50660
2020/05/20 04:00:50 - ClarityDB - isOracle? -                   WHEN o.code = 'idea' THEN 51780
2020/05/20 04:00:50 - ClarityDB - isOracle? -                   WHEN o.code = 'other' THEN 50669
2020/05/20 04:00:50 - ClarityDB - isOracle? -                   WHEN o.code = 'product' THEN 50666
2020/05/20 04:00:50 - ClarityDB - isOracle? -                   WHEN o.code = 'project' THEN 663
2020/05/20 04:00:50 - ClarityDB - isOracle? -                   WHEN o.code = 'resource' THEN 664
2020/05/20 04:00:50 - ClarityDB - isOracle? -                   WHEN o.code = 'service' THEN 51101 ELSE 0 END object_key,
2020/05/20 04:00:50 - ClarityDB - isOracle? -              o.code object_code,
2020/05/20 04:00:50 - ClarityDB - isOracle? -              o.right_code table_name,
2020/05/20 04:00:50 - ClarityDB - isOracle? -              CASE WHEN o.code IN ('application','asset','idea','other','product','project','service') THEN 1 ELSE 0 END is_investment 
2020/05/20 04:00:50 - ClarityDB - isOracle? -       FROM   odf_objects o
2020/05/20 04:00:50 - ClarityDB - isOracle? -       WHERE  o.is_dw_enabled = 1
2020/05/20 04:00:50 - ClarityDB - isOracle? -       AND    o.code IN ('application','asset','idea','other','product','project','service','resource')
2020/05/20 04:00:50 - ClarityDB - isOracle? -       UNION
2020/05/20 04:00:50 - ClarityDB - isOracle? -       SELECT s.id object_key,
2020/05/20 04:00:50 - ClarityDB - isOracle? -              o.code object_code,
2020/05/20 04:00:50 - ClarityDB - isOracle? -              'odf_ca_' || o.right_code table_name,
2020/05/20 04:00:50 - ClarityDB - isOracle? -              1 is_investment
2020/05/20 04:00:50 - ClarityDB - isOracle? -       FROM   odf_objects o
2020/05/20 04:00:50 - ClarityDB - isOracle? -              INNER JOIN cmn_sec_objects s ON 'odf_cst_' || o.code = s.object_code 
2020/05/20 04:00:50 - ClarityDB - isOracle? -       WHERE  o.template_extension = 'inv_extension'
2020/05/20 04:00:50 - ClarityDB - isOracle? -       AND    o.is_dw_enabled = 1
2020/05/20 04:00:50 - ClarityDB - isOracle? -       AND    s.object_type_code = 'RECORD') o ON (s.object_key = o.object_key)
2020/05/20 04:00:50 - ClarityDB - isOracle? - WHEN MATCHED THEN UPDATE SET object_code = o.object_code, table_name = o.table_name, is_investment = o.is_investment
2020/05/20 04:00:50 - ClarityDB - isOracle? - WHEN NOT MATCHED THEN INSERT (object_key, object_code, table_name, is_investment) VALUES (o.object_key, o.object_code, o.table_name, o.is_investment);
2020/05/20 04:00:50 - ClarityDB - isOracle? - END;
2020/05/20 04:00:50 - ClarityDB - isOracle? - 
2020/05/20 04:00:50 - ClarityDB - isOracle? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (SCHEMA.DWH_CFG_OBJECT_TYPE_U2) violated
2020/05/20 04:00:50 - ClarityDB - isOracle? - ORA-06512: at line 5

Cause

There are custom objects that are not synchronized after a backup/refresh.

Resolution

  1. Run the following query on the Clarity database:

    select * FROM   odf_objects o
                 INNER JOIN cmn_sec_objects s ON 'odf_cst_' || o.code = s.object_code 
          WHERE  o.template_extension = 'inv_extension'
          AND    o.is_dw_enabled = 1

  2. Review the object code listing and make note of the object id.
  3. Exclude the object from the DWH via Administration > Studio > Objects > custom object > Properties.
  4. Click Save.
  5. Run the full Load DWH job to completion.
  6. Include the object from the DWH via Administration > Studio > Objects > custom object > Properties. Click Save.
  7. Run the full Load DWH job to completion.

Additional Information

Reference also:

Powered by Wolken Software