search cancel

16.0 upgrade failure on UNIQUE INDEX DWH_CMN_MV_LOOKUP

book

Article ID: 228464

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

Upgrade to 16.0 Failure when there are duplicates in DWH_CMN_MV_LOOKUP(PostgreSQL Only)
 
STEPS TO REPRODUCE: 
  1. Take an 15.9.3 environment on Postgres.
  2. Run the below query to check for the existence of duplicates in clarity (ODF_MULTI_VALUED_LOOKUPS) 
    SELECT OBJECT,pk_id,ATTRIBUTE,value,count(*) FROM ODF_MULTI_VALUED_LOOKUPS
    GROUP BY OBJECT,pk_id,ATTRIBUTE,value ORDER BY count(*) DESC;
  3. Run the full load Datawarehouse job
  4. Load job completes successfully
  5. Upgrade clarity to 16.0.
 
Expected Results :- Upgrade completes successfully. 
Actual Results :- Upgrade fails.

Install.logs:
(admin) org.postgresql.util.PSQLException: ERROR: could not create unique index "dwh_cmn_mv_lookup_u1"
Detail: Key (table_name, column_name, table_record_key, lookup_value_key, language_code)=(DWH_INV_INVESTMENT_LN, TU_PLATFORMS_KEY, 5460145, 1, en) is duplicated.

or

CREATE UNIQUE INDEX DWH_CMN_MV_LOOKUP_U1 ON DWH_CMN_MV_LOOKUP ( TABLE_NAME, COLUMN_NAME, TABLE_RECORD_KEY, LOOKUP_VALUE_KEY, LANGUAGE_CODE )  

Detail: Key (table_name, column_name, table_record_key, lookup_value_key, language_code)=(DWH_RIM_RISK, ASSOCIATED_ISSUES_KEY, 5414005, 5414012, en) is duplicated.

Cause

This is caused by product defect DE63197

Environment

Clarity 16.0

Resolution

As part of the Defect resolution a cleanup script to clear duplicate rows from PPM and DWH schemas have been provided and it would ensure a smooth 16.0 upgrade.

On-premise Customers running 16.0 upgrades on PostgreSQL environment can raise a Broadcom support case for the Delete script. 

Steps to resolve:

  1. Run the cleanup scripts shared against the PPM and DWH schemas
  2. Validate duplicates on PPM schema
    SELECT OBJECT,pk_id,ATTRIBUTE,value,count(*) FROM ODF_MULTI_VALUED_LOOKUPS GROUP BY OBJECT,pk_id,ATTRIBUTE,value ORDER BY count(*) DESC;
  3. Validate duplicates on Datawarehouse schema
    select table_name, column_name, table_record_key, lookup_value_key, language_code,count(*) from dwh_cmn_mv_lookup dcml group by table_name, column_name, table_record_key, lookup_value_key, language_code having count(*) >1;
  4. If Duplciates are clear proceed with 16.0 upgrade