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
- 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
- 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.
- 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
- 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:
- Connect to Clarity - 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
- Re-run the query, ensure it does not return any null records anymore
- Resume the upgrade