Clarity DWH Load Job Fails on PostgreSQL with error about a missing function dwh_cast_curve_fct

Products

• Clarity PPM On Premise

Issue/Introduction

After upgrading to 16.3.3 patch 1 with PostgreSQL DB, an issue with Load DWH Job surfaced. 

The DWH Load job runs successfully unless the "SQL Curve Data" feature (used for reporting) is enabled. When this feature is active, the job fails.

The following error is observed in the bg logs:

ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_FIN_PLAN_CRV_FACTS. function dwh_cast_curve_fct(niku.nk_curve) does not exist
  Where: PL/pgSQL function inline_code_block line 24 at RAISE

Environment

Clarity 16.3.3 Patch 1 (16.3.3.1)

Cause

Investigation confirmed that the critical dwh_cast_curve_fct(niku.nk_curve) function is missing from the Data Warehouse (DWH) schema. This function is essential for processing "SQL Curve Data"

Resolution

To resolve this, execute the following commands on the Clarity server:

1. Recreate Database Link for Transactional DB:
   
   ./admin db create-db-link -Dtype=app -Dsysusername=postgres -Dsyspassword=changeit
   
   This command establishes or re-establishes the database link for the main Clarity transactional database.
   
   
   
2. Recreate Database Link and DWH Schema Objects:
   
   ./admin db create-db-link
   
   This command is crucial for the Data Warehouse (DWH) database. It not only establishes or re-establishes database link but also regenerates missing DWH schema objects, for e.g. foreign tables, user defined data types (for curves) are loaded,  including the dwh_cast_curve_fct function that was causing the error.