ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Load DWH job fails with "ORA-00001: unique constraint DWH_CFG_OBJECT_TYPE_U2 Violated"

book

Article ID: 191482

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse (DWH) full load and incremental load are failing with the below error.

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

Cause

This can happen if you create a normal Master Object first and delete it, then create a different object with the same id but make this an extension to the Investment object.

Technically, there should be 3 rows pertaining to this new object in CMN_SEC_OBJECTS, but due to unknown reasons, there can be more rows (example 6 rows) present which causes this issue.

Environment

Release : 15.7.1,15.8

Component : CLARITY DATA WAREHOUSE

Resolution

Note: Reach out to Broadcom Support before following these steps.

  1. Run the below query to identify which object is having duplicates. 

    select object_code,count(*) from(
    SELECT CASE WHEN o.code = 'application' THEN 50663 
                       WHEN o.code = 'asset' THEN 50660
                       WHEN o.code = 'idea' THEN 51780
                       WHEN o.code = 'other' THEN 50669
                       WHEN o.code = 'product' THEN 50666
                       WHEN o.code = 'project' THEN 663
                       WHEN o.code = 'resource' THEN 664
                       WHEN o.code = 'service' THEN 51101 ELSE 0 END object_key,
                  o.code object_code,
                  o.right_code table_name,
                  CASE WHEN o.code IN ('application','asset','idea','other','product','project','service') THEN 1 ELSE 0 END is_investment 
           FROM   odf_objects o
           WHERE  o.is_dw_enabled = 1
           AND    o.code IN ('application','asset','idea','other','product','project','service','resource')
           UNION
           SELECT s.id object_key,
                  o.code object_code,
                  'odf_ca_' || o.right_code table_name,
                  1 is_investment
           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
           AND    s.object_type_code = 'RECORD')
        group by object_code having count(*) > 1

  2. Once the object code is identified run the below query:

    select id,object_code,object_type_code,component_id,component_code from cmn_sec_objects where upper(object_code)='ODF_CST_OBJECT_CODE';

  3. Navigate to Administration -> Objects and type in the object id.
  4. Validate if this object is an extension of the Investment object. If this is an extension of the Investment Object then the component_code in cmn_sec_objects should be INV, otherwise, it should be CMN.
  5. Take a backup of CMN_SEC_OBJECTS table, stop the services.
  6. Start Services.
  7. Run the below:

    DELETE FROM CMN_SEC_OBJECTS WHERE COMPONENT_CODE='CMN' and upper(object_code)='ODF_CST_OBJECT_CODE';

  8. Run the Load DWH job.

Additional Information

If the above doesn't help, see: