Checkinstall on upgrade fails on check-dwh-load-job-success.xml

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Checkinstall fails with error:  check-dwh-load-job-success.xml               ERROR Load Data Warehouse job has failed last time it was run. Make sure to run it successfully before proceeding the upgrade.
 

Environment

Release: 15.4,15.4.1,15.5, 15.5.1,15.6.1

Resolution

Determine the issue

The checkinstall script check-dwh-load-job-success.xml would check the latest the job has run and will return either Completed or Failed based on that.  The tables involved are CMN_SCH_JOB_RUNS, CMN_SCH_JOBS, CMN_SCH_JOB_DEFINITIONS

1. Make sure the Load Data Warehouse job has run recently and is completed. If the job is not completed successfully or was running when you started the upgrade, this would cause a problem
2. Once the Load Data Warehouse job - Full has run, make sure the record of it running is not removed. Do not run any SQL statements that could affect the tables stated above. Do not Remove Job Logs and Report Library entries to remove ALL jobs.
3. Run the query from check-dwh-load-job-success.xml:
   SELECT CASE STATUS_CODE WHEN 'FAILED' THEN 1 ELSE 0 END hasjobfailed FROM
   ( SELECT STATUS_CODE, END_DATE FROM
   (
   SELECT R.STATUS_CODE ,R.END_DATE
   FROM CMN_SCH_JOB_RUNS R, CMN_SCH_JOBS J, CMN_SCH_JOB_DEFINITIONS D
   WHERE R.JOB_ID = j.ID
   AND J.JOB_DEFINITION_ID = D.ID
   AND D.JOB_CODE = 'DWH_ETL_MASTER'
   AND D.IS_ACTIVE = 1
   AND R.STATUS_CODE IN ('FAILED', 'COMPLETED')
   UNION ALL
   SELECT J.STATUS_CODE, J.END_DATE
   FROM CMN_SCH_JOBS J, CMN_SCH_JOB_DEFINITIONS D
   WHERE J.JOB_DEFINITION_ID = D.ID
   AND D.JOB_CODE = 'DWH_ETL_MASTER'
   AND D.IS_ACTIVE = 1
   AND J.STATUS_CODE IN ('FAILED', 'COMPLETED')
   ) JOBS ORDER BY JOBS.END_DATE DESC
   ) WHERE ROWNUM= 1
   
   
4. Review the results:

If it returns 0, you can continue with the upgrade.

If it returns 1, you might be hitting a known defect: DE43502 fixed in 15.6.0
With this defect there is an old job entry with END_DATE = NULL which causes the issue. 

Identify the job that causes the issue by running:
SELECT R.ID, R.STATUS_CODE ,R.END_DATE
FROM CMN_SCH_JOB_RUNS R, CMN_SCH_JOBS J, CMN_SCH_JOB_DEFINITIONS D
WHERE R.JOB_ID = j.ID
AND J.JOB_DEFINITION_ID = D.ID
AND D.JOB_CODE = 'DWH_ETL_MASTER'
AND D.IS_ACTIVE = 1
AND R.STATUS_CODE IN ('FAILED', 'COMPLETED')
and R.END_DATE is NULL

Workaround for  DE43502 :

Option 1:

1. Connect to Clarity - Home - Jobs - Log
2. Find and delete the failed job entry

Option 2:

1. Update the END_DATE returned by the query above with a corresponding END_DATE:
   update CMN_SCH_JOB_RUNS set end_date =start_date where  id =<the R.ID returned by the query above>
   commit
2. Re-run the query, ensure it does not return any null records anymore
3. Resume the upgrade
    

Additional Information

Issue found in 15.7.1 as well, so not sure how far the fix made it to the releases after 15.6.0