MSSQL: Load DWH Incremental job fails because of a query - hangs on DWH_X_INV_TASK_SUM_FACTS

Article Id: 141205
Status: Published
Updated On: 18-09-2020 14:44
Products:
Clarity PPM On Premise
Issue/Introduction:

Steps to Reproduce:

  1. Run Load Data Warehouse - Full
  2. Run Load Data Warehouse - Incremental
  3. Note it takes 10-15 min
  4. Now create a project, assign 3 resources to 3 tasks, assign ETC
  5. Run Incremental again

Expected Results: Job to run just a bit slower than previously
Actual Results: Job hangs for 500+ minutes and then fails with the below error:

ClarityDB - isOracle? - An error occurred executing this job entry : 
Couldn't execute SQL: DELETE FROM DWH_X_INV_TASK_SUM_FACTS 
WHERE EXISTS (
    SELECT 1
    FROM [PPMDBLINK].niku.niku.DWH_X_INV_TASK_SUM_FACTS_V src
   WHERE DWH_X_INV_TASK_SUM_FACTS.TASK_KEY = src.TASK_KEY
   AND  (src.CLARITY_UPDATED_DATE BETWEEN CONVERT(DATETIME,'2019-11-21 01:32:34') AND CONVERT(DATETIME, '2019/11/22 00:42:51')
   OR    DWH_X_INV_TASK_SUM_FACTS.task_key IN (SELECT d.task_key FROM dwh_tmp_deleted_keys d WHERE d.table_name = 'DWH_INV_TODO'))
)
[CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot continue the execution because the session is in the kill state.
The above query when run separately never completes.

Cause:

This is caused by DE48981/DE52543

Environment:

Release : 15.4.1, 15.5, 15.5.1, 15.6, 15.7.0, 15.7.1

Component : CA PPM DATA WAREHOUSE

Resolution:

This defect DE52758 is resolved in 15.8.1

Workaround: Kill the blocking process and run LDWH Full fixes the next incremental job.

 

Additional Information:

For more details on the Load DWH job, see: Load Data Warehouse Job