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

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

Environment

Release : 15.7.1,15.8

Component : CLARITY DATA WAREHOUSE

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.

Resolution

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

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
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';
Navigate to Administration -> Objects and type in the object id.
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.
Take a backup of CMN_SEC_OBJECTS table, stop the services.
Start Services.
Run the below:

DELETE FROM CMN_SEC_OBJECTS WHERE COMPONENT_CODE='CMN' and upper(object_code)='ODF_CST_OBJECT_CODE';
Run the Load DWH job.

Additional Information

If the above doesn't help, see: