Datamart Extraction job failing post upgrade

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Post upgrade to 14.3, the Datamart Extraction job intermittently fails with an exception in the bg-ca.log file:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-06550: line 0, column 0:
PLS-00907: cannot load library unit CLARITY.NBI_EXTRACT_SP (referenced by )....

This problem is not seen in most environments.

Environment

Oracle 11.2.0.3, 11.2.0.4PPM 14.3

Cause

The PPM Stored Procedures may not rebuild the index "NBI_PRTF_FM_U1" correctly every time: "CMN_DROP_INDEX_SP" and "CMN_CREATE_INDEX_SP".

This problem may be isolated to certain environments or data sets.

 

 

 

Resolution

CA Support has not seen this in all environments.

The following workaround solution will resolve the issue.

1- Backup the following PPM schema Stored Procedure: "NBI_PROJECT_CURRENT_FACTS_SP".

2- Locate the following section: "V_STMT := 'Calling NBI_PRTF_FM_SP since FM is enabled';"

3- Modify this block:

Original:
V_STMT := 'Calling NBI_PRTF_FM_SP since FM is enabled';
EXECUTE IMMEDIATE 'TRUNCATE TABLE NBI_PRTF_FM';
SELECT NVL(tablespace_name,'') into v_u1_index_ts_name FROM user_indexes WHERE table_name = 'NBI_PRTF_FM' AND index_name='NBI_PRTF_FM_U1';
CMN_DROP_INDEX_SP('NBI_PRTF_FM_U1');
NBI_PRTF_FM_SP (P_CURRENCY_CODE);
CMN_CREATE_INDEX_SP('NBI_PRTF_FM','NBI_PRTF_FM_U1','UNIQUE','PROJECT_ID',v_u1_index_ts_name);

Please change this to:
V_STMT := 'Calling NBI_PRTF_FM_SP since FM is enabled';
EXECUTE IMMEDIATE 'TRUNCATE TABLE NBI_PRTF_FM';
SELECT NVL(tablespace_name,'') into v_u1_index_ts_name FROM user_indexes WHERE table_name = 'NBI_PRTF_FM' AND index_name='NBI_PRTF_FM_U1';
--CMN_DROP_INDEX_SP('NBI_PRTF_FM_U1');
NBI_PRTF_FM_SP (P_CURRENCY_CODE);
EXECUTE IMMEDIATE 'ALTER INDEX NBI_PRTF_FM_U1 REBUILD';

4- Rebuild the Stored Procedure, for example: "create or replace PROCEDURE NBI_PROJECT_CURRENT_FACTS_SP..."

5- Schedule the "Datamart Extraction" job and monitor to ensure the issue is resolved.

 

 

Additional Information

If there are any doubts, please work with CA Support to clarify.