How To Check Load DWH Job Runtime History / Performance
search cancel

How To Check Load DWH Job Runtime History / Performance

book

Article ID: 262104

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

The Load DWH incremental Job is taking longer to run.
Is there a way to find out why it is taking a long time to run?
Sometimes, it will fail. Is there a method to find out why an instance of the job failed?

 

Environment

Release: All

Resolution

The 3 reasons why the job would fail:

1. Meta data issue where the Clarity and DWH databases are not in synch, which requires a full Load DWH run.

2. There is data issue which needs an analysis and typically will fail until the data issue is resolved or the next full Load DWH run has completed.

3. There is a disruption in the job scheduler/BG service.

The job will recover at the next run, which in reviewing the job schedule and timings, has completed since that last failure.

The following query can be used to find out the history of the job:

select j.id, j.name, r.start_date, r.end_date,
ROUND(R.TOTAL_EXECUTION_TIME/1000/60, 3) EXEC_MINS,
r.status_code, r.user_id, r.scheduler_id, a.column_name, p.value as column_value
from cmn_sch_jobs j
left join cmn_sch_job_runs r on j.id = r.job_id
left join cmn_sch_job_run_params p on r.id = p.job_run_id
left join cmn_attributes a on p.attribute_id = a.id
where 1=1
and r.start_date is not null
and j.job_definition_id in
(select id from cmn_sch_job_definitions where id in (select id from cmn_sch_job_definitions where job_code = 'DWH_ETL_MASTER' and is_active = 1)
)
order by r.start_date desc

If assistance is needed where it happens again, perform the following;

1. Pause the job before it runs to completion on the subsequent run.
2. Gather the BG DWH and BG SYSTEM logs and review for reason why the job failed.
3. Open a case with Support.

 

Additional Information

Why does the incremental run take longer than the full run?

The incremental run will historically take longer than the full run, as it performs a comparison build, rather than a truncate and rebuild.
It is best to keep running the incremental at regular schedule, if you expect or plan users are accessing Jaspersoft reports because at least they will get the latest snapshot data.