Jump to: navigation, search

View ADMIN_ETL_JOB_STATUS

Description

This view provides information about the most recent execution of each ETL job. A row is added to this view after each ETL job starts and is updated as the job status changes.

Column List

Column Description
JOB_ID ID that uniquely identifies the execution instance of the job.
JOB_NAME The name of the job, such as Job_ExtractICON.
JOB_VERSION The version of the job, such as 8.1.000.10.
START_TIME The date and time at which the first step started (UTC time zone).
END_TIME The date and time at which the last step ended (UTC time zone).
DURATION The duration of the job, in seconds.
STATUS The status of the step, such as COMPLETE or FAILED.

SQL Query

select
MAX(JOB_ID) as JOB_ID,
JOB_NAME,
MAX(JOB_VERSION) as JOB_VERSION,
MIN(START_TIME)  as START_TIME,
CASE
WHEN MIN(START_TIME)
< MAX(END_TIME)
THEN MAX(END_TIME)
ELSE NULL
END as END_TIME,
CASE
WHEN MIN(START_TIME) < MAX(END_TIME)
THEN DATEDIFF(SECOND,MIN(START_TIME),MAX(END_TIME))
ELSE NULL
END as DURATION,
MAX(STATUS) as STATUS
from
CTL_WORKFLOW_STATUS
where
STATUS NOT IN ('NOT_CONFIGURED','INSTALLED')
AND JOB_ID in (select MAX(JOB_ID) from CTL_WORKFLOW_STATUS group by JOB_NAME)
group by
JOB_NAME

Feedback

Comment on this article:

blog comments powered by Disqus
This page was last modified on 29 August 2016, at 14:54.