informatica数据字典最常用手册

查询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;