SELECT JB.ID_JOB, ---作业ID
JB.NAME, ---作业名称
to_char(JB.DESCRIPTION) as JOB_DESCRIPTION , --作业描述 case when jl.status='end' then
'完成'
when jl.status='running' then
'运行中'
when jl.status like 'stop%' then
'停止'
ELSE
JL.STATUS
END STATUS, ---作业运行状态
JL.ERRORS AS JOB_ERRORS_NUMBER, ---作业运行错误数
JL.LOG_FIELD AS JOB_LOG_COMMENT, ---作业日志内容
JL.STARTDATE AS JOB_START_DATE, ---作业运行开始时间
JL.ENDDATE AS JOB_END_DATE, ---作业运行结束时间
JL.LOGDATE AS JOB_LOG_DATE ---作业日志记录时间
FROM R_JOB JB,ETL_JOB_LOG JL
WHERE JB.NAME= JL.JOBNAME(+)
AND JB.ID_JOB IN
(SELECT J.ID_JOB FROM R_JOBENTRY J
WHERE J.ID_JOBENTRY_TYPE=)
ORDER BY JB.ID_DIRECTORY,JB.NAME,JL.LOGDATE
;
1.0查询 主JOB 运行 日志
SELECT JB.ID_JOB, ---作业ID
JB.NAME,
SUBJ.ID_JOB,
RY.NAME as SUB_JOB_NAME, ---作业名称
to_char(JB.DESCRIPTION) as JOB_DESCRIPTION , --作业描述 case when jl.status='end' then
'完成'
when jl.status='running' then
'运行中'
when jl.status like 'stop%' then
'停止'
ELSE
JL.STATUS
END STATUS, ---作业运行状态
JL.ERRORS AS JOB_ERRORS_NUMBER, ---作业运行错误数
JL.LOG_FIELD AS JOB_LOG_COMMENT, ---作业日志内容
JL.STARTDATE AS JOB_START_DATE, ---作业运行开始时间
JL.ENDDATE AS JOB_END_DATE, ---作业运行结束时间
JL.LOGDATE AS JOB_LOG_DATE ---作业日志记录时间
FROM R_JOB JB,ETL_JOB_LOG JL ,R_JOBENTRY RY,R_JOB SUBJ
WHERE JB.ID_JOB=RY.ID_JOB(+)
AND JB.NAME= JL.JOBNAME(+)
AND RY.NAME=SUBJ.NAME
AND RY.id_jobentry_type=22
ORDER BY JB.ID_DIRECTORY,JB.NAME,JL.LOGDATE
;
2. 查询 子JOB 运行 日志
查询条件 ： 执行批次，作业名称，作业描述，转换运行状态，转换日志时间
SELECT ET.ID_BATCH, ----转换执行批次
JB.ID_JOB, ----作业ID
JB.NAME AS JOB_NAME, ----作业名称
JB.DESCRIPTION AS JOB_DESCRIPTION,----作业描述
RT.ID_TRANSFORMATION, ----转换ID
RT.NAME AS TRANS__NAME, ----转换名称
RT.DESCRIPTION AS TRANS_DESCRIPTION, ----转换描述
case when et.status='end' then
'完成'
when et.status='running' then
'运行中'
when et.status like 'stop%' then
'停止'
ELSE
ET.STATUS
END AS TRANS_RUN_STATUS, ----转换运行状态
ET.ERRORS AS TRANS_ERROR_NUMBER, ----转换运行错误数
ET.LOG_FIELD AS TRANS_LOG_COMMENT, ----转换运行日志内容
ET.STARTDATE AS TRANS_STARTDATE, ----转换运行开始时间
ET.ENDDATE AS TRANS_ENDDATE, ----转换运行结速时间
ET.LOGDATE AS TRANS_LOGDATE ----转换日志记录时间 FROM ETL_TRANSFORMATION_LOG ET,
R_TRANSFORMATION RT,
R_JOBENTRY RE,
R_JOBENTRY_TYPE JP,
R_JOB JB
WHERE ET.TRANSNAME = RT.NAME
AND RE.NAME = RT.NAME
AND RE.ID_JOBENTRY_TYPE = JP.ID_JOBENTRY_TYPE
AND JB.ID_JOB = RE.ID_JOB
AND JP.CODE = 'TRANS'
ORDER BY ET.ID_BATCH,JB.ID_JOB,RT.ID_TRANSFORMATION,ET.LOGDATE
;
3.查询ETL 转换运行日志
查询条件 ： 转换执行批次，转换名称，转换描述，转换运行状态，转换日志时间
SELECT ETSL.ID_BATCH, ----转换步骤批次
JB.ID_JOB, ----作业ID
JB.NAME AS JOB_NAME, ----作业名称
JB.DESCRIPTION AS JOB_DESCRIPTION,----作业描述
RS.ID_TRANSFORMATION, ----转换ID
RT.NAME AS TRANS_NAME, ----转换I名称
RS.ID_STEP AS TRANS_STEP_ID, ----转换步骤ID
RS.NAME AS TRANS_STEP_NAME, ----转换步骤名称
RST.CODE AS TRANS_STEP_TYPE_CODE, ----转换步骤类型代码
RST.DESCRIPTION AS TRANS_STEP_DESCRIPTION, ----转换步骤描述
CASE WHEN RST.ID_STEP_TYPE=139 THEN
'数据输入'
WHEN RST.ID_STEP_TYPE IN（10, 163, 100) THEN
'数据输出'
END AS TRANS_STEP_INPUT_OUTPUT, ----转换步骤数据流
ETSL.LINES_READ , ----转换步骤读数据行数
ETSL.LINES_WRITTEN, ----转换步骤写数据行数
ETSL.LINES_UPDATED, ----转换步骤更新数据行数
ETSL.LINES_INPUT, ----转换步骤输入数据行数
ETSL.LINES_OUTPUT, ----转换步骤输出数据行数
ETSL.LINES_REJECTED, ----转换步骤抛出数据行数
ETSL.ERRORS AS TRANS_STEP_ERRORS_NUMBER,----转换步骤错误数
ETSL.LOG_DATE AS TRANS_STEP_LOG_DATE ----转换步骤日志记录时间
FROM R_STEP RS,
R_STEP_TYPE RST,
R_TRANSFORMATION RT,
ETL_TRANS_STEP_LOG ETSL,
R_JOBENTRY RE,
R_JOBENTRY_TYPE JP,
R_JOB JB
WHERE RS.ID_STEP_TYPE = RST.ID_STEP_TYPE
AND RS.ID_TRANSFORMATION = RT.ID_TRANSFORMATION
AND ETSL.TRANSNAME = RT.NAME
AND ETSL.STEPNAME = RS.NAME
AND RE.NAME = RT.NAME
AND RE.ID_JOBENTRY_TYPE = JP.ID_JOBENTRY_TYPE
AND JB.ID_JOB = RE.ID_JOB
AND JP.CODE = 'TRANS'
AND RST.ID_STEP_TYPE IN (139, 10, 163, 100)
ORDER BY ETSL.ID_BATCH,RS.ID_TRANSFORMATION, RS.ID_STEP,ETSL.LOG_DATE
4.查询ETL 转换输入输出端 运行日志