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

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM SaaS - Application
• 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: CODFSS99000-15.4.1-PPM SAAS FedRAMP-Sandbox-Small Environment
Component:

Resolution

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

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

If it returns 1, you might be hitting a known defect: DE43502 Upgrade checkinstall fails on check-dwh-load-job-success.xml (Data Warehouse successful run script has to be adjusted) - Oracle
With this defect there is an old job entry with END_DATE = NULL which causes the issue. 
 

Workaround for  DE43502 :
1.    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

Option 1:
Connect to CA PPM UI - Home - Jobs - Log
Find and delete the failed job entry

Option 2:
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