查询infa中session中的源表及抽取源表抽取SQL逻辑
SELECT t3.mapping_name,t1.INSTANCE_NAME,t2.ATTR_VALUE
FROM REP_WIDGET_INST t1 , REP_WIDGET_ATTR t2, OPB_MAPPING t3
where t1.WIDGET_ID=t2.WIDGET_ID and t1.MAPPING_ID=t3.mapping_id
and t2.ATTR_VALUE like '%TPPC_ORA_INDEX%';
查询infa作业流执行消息情况(每个session时间都有)
select s.subj_name as folder_name,w.workflow_run_id,w.workflow_name,t.task_name,
to_char(t.start_time,'yyyy-mm-dd hh24:mi:ss') as etl_time_start,
to_char(t.end_time,'yyyy-mm-dd hh24:mi:ss') as etl_time_start,
round((t.end_time-t.start_time)*24*60) as run_minute,
(t.end_time-t.start_time)*24*60*60 as run_seconds,
t.run_err_code ,t.run_err_msg from opb_task_inst_run t, opb_wflow_run w , opb_subject s
where t.workflow_run_id=w.workflow_run_id and w.subject_id=s.subj_id
and s.subj_name='TP_RMCS'
and w.workflow_name='WF_TPLIFE_021'
order by t.start_time desc;
查询源表、目标表所在session (映射)
select distinct c.mapping_name mapn, a.instance_name arc,b.instance_name tar from
(select * from opb_widget_inst t where t.widget_type=1) a
,(select * from opb_widget_inst t where t.widget_type=2) b
,opb_mapping c
where a.mapping_id=b.mapping_id
and a.mapping_id=c.mapping_id
and a.instance_name like '%TPRC_ORA_INDEX%';
查询session所在作业流
select distinct b.workflow_name, a.instance_name
from opb_task_inst a, opb_wflow_run b
where a.workflow_id = b.workflow_id
and a.instance_name like '%M_TPLIFE_014%';
根据存储过程或指标找到作业名
select distinct t3.workflow_name wfname , t2.instance_name,
replace( replace( replace( replace( replace( replace(replace(
substr(t1.pm_value, instr(t1.pm_value,'exec',1,1),instr(t1.pm_value,'exit',1,1)) ,
'exit','') ,'EOF',''),'''',''),chr(10),''),'($PMWorkflowName);',''),'exec ',''),';','')
from opb_task_val_list t1, OPB_TASK_INST t2, opb_wflow_run t3
where t1.TASK_ID = t2.task_id
and t2.workflow_id = t3.workflow_id
and t1.TASK_TYPE = '58'
and t2.instance_name like '命令%'
and t1.pm_value like '%PRC_TP_FSC000_020_AUTO_MONTH%';
查询计划中状态的任务:
SELECT
DISTINCT A.SUBJECT_AREA FOLDER, A.WORKFLOW_NAME, A.SCHEDULER_NAME,
CASE WHEN MOD(B.RUN_OPTIONS,2) = 0 THEN 'SHCEDULE WORKFLOW' ELSE 'ON DEMAND WORKFLOW' END RUN_TYPE
FROM REP_WORKFLOWS A, OPB_SCHEDULER B WHERE A.SCHEDULER_ID=B.SCHEDULER_ID;