Load Data Warehouse job fails when trying to insert NULL into DWH_INV_TASK

Products

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

Issue/Introduction

Symptoms:

Load Data Warehouse job fails, the following example message gets thrown in bg-dwh.log:

2015/10/24 11:12:14 - User Defined Java Class.0 - 2015/10/24 11:12:14 - dwh_db_check_ - UPDATE STATISTICS DWH_INV_TASK;
2015/10/24 11:12:14 - User Defined Java Class.0 - 2015/10/24 11:12:14 - dwh_db_check_ - END
2015/10/24 11:12:14 - User Defined Java Class.0 - 2015/10/24 11:12:14 - dwh_db_check_ -
2015/10/24 11:12:14 - User Defined Java Class.0 - 2015/10/24 11:12:14 - dwh_db_check_ - [CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot insert the value NULL into column 'WBS_LEVEL', table 'PPM_DWH.ppm_dwh.DWH_INV_TASK'; column does not allow nulls. INSERT fails

(this message is an MSSQL example, there will be a similar error message for Oracle too)

Causes:

This is due to a known issue in CA PPM 14.2 and higher:

CLRT-79130 PRTASK.PRWBSLEVEL should not be NULLABLE: causing Load Data Warehouse job to fail on DWH_INV_TASK insert

Database column PRTASK.PRWBSLEVEL can have nulls, which subsequently fails the Load Data Warehouse job.

Resolution:

CLRT-79130 is in review with our Sustaining Engineering team.

Workaround:

Identify and fix the WBS levels on the tasks that can cause this issue through the application UI:

1. Run the queries to identify the tasks that have the issue, and the Projects they're on (on CA PPM database):
   • select * from dwh_task_v where wbs_level is null
   • select name from inv_investments where id in (select investment_key from dwh_task_v where wbs_level is null)
2. Save the results for yourself 
3. Now open the project that has the task with prwbslevel = null 
4. Go to Tasks - Task List 
5. Click on Options icon and add WBS Sort to the list view for display purposes 
6. Now open the Gantt, ensure you can see the tasks you want to correct in the view 
7. Select the affected task, and click on Move icon
8. In the popup that appears, ensure you move the task up/down the list with Insert Before/Insert After buttons, so it generates the prwbslevel correctly
9. Now close the Gantt and observe the Task List page - WBS Sort column. Ensure the nulls were corrected and you see the level for each task
10. Repeat for all investments / task affected
11. Once done, run Load Data Warehouse job 

If you still experience an issue, please contact CA Support and refer to this article.

 

Environment

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