Load Data Warehouse job fails if the combined length of custom attribute IDs exceed 4000 characters on a single object

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Issue:

Load Data Warehouse job will fail when on a single object the combined length of the IDs of all custom attribute that are included in the Data Warehouse exceed 4000 characters. This issue is due to the limitation from Oracle on its 11G and lower versions, the limit will be at 8000 characters starting from Oracle 12C.

Error shown in the bg log:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 11

 

The error message shown on the UI is generic, it should be more descriptive from the UI. 

 

MSSQL also faces the same issue. 

For MSSQL the following error message is shown in the log files.

 

no Records to Process? - An error occurred executing this job entry :

Couldn't execute SQL: DECLARE @V_SQL_TEXT nvarchar(max);

 

BEGIN

SET @V_SQL_TEXT = 'select <LONG LIST OF CUSTOM ATTRIBUTES BEING FLAGGED FOR DATA WAREHOUSE AND BEING CUT OFF IN THE JOB'S LOG>

User Defined Java Class - Unexpected error

User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_PROJECT

at Processor.processRow(Processor.java:64)

at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181)

at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)

at java.lang.Thread.run(Thread.java:745) 

 

 

Steps to Reproduce:

1. Add many custom attributes to the Investment object, and give them lengthy IDs such that the combined length of all their IDs is over 4000 characters  
2. Check the 'Include in the Data Warehouse' box for all of the above attributes  
3. Run the 'Load Data Warehouse' (LDWH) job  

Expected Result: The job fails with an error stating the cause being with the combined length of custom attribute IDs on the object

Actual Result: The job fails with a generic error that that the job has failed and user should check the log for details

Environment:

Applies to all supported PAS environments for specified releases.

Cause:

Caused by CLRT-78443

Resolution:

Resolved in CA PPM 14.2 Generic Patch #7.

Resolved in CA PPM 14.3

Workaround:

Un-check some of the custom attributes for inclusion to the Data Warehouse and run the LDWH job again  

Additional Information:

Reference TEC1398937 : PPM 14.2 Generic Patch available to resolve reported issues

Reference CA PPM Resolved Defects Index for CA PPM 14.3

 

Environment

Release: ESPCLA99000-14.3-Clarity-Extended Support Plus
Component: