informatica元数据库常用查询

使用时ETL_ODS_WIN应该换成自己的域名称。对于源与目标，目前获取的是建立表结构时给出的名称，实际跑任务时在配置中写入的目标表不知道怎么获取，有大佬知道的麻烦指点下。

-- mapping-->source_table
SELECT distinct
    OPB_SUBJECT.SUBJ_ID folder_ID,
    OPB_SUBJECT.SUBJ_NAME folder_name,
    OPB_MAPPING.MAPPING_ID,
    OPB_MAPPING.MAPPING_NAME,
    OPB_SRC.SRC_ID SOURCE_ID,
    OPB_SRC.SOURCE_NAME ,
    opb_src.ownername
FROM
    ETL_ODS_WIN.OPB_SRC,  
    ETL_ODS_WIN.OPB_SUBJECT, 
    ETL_ODS_WIN.OPB_MAPPING,  
    ETL_ODS_WIN.OPB_WIDGET_INST SOURCE_INSTANCES
WHERE
    OPB_SUBJECT.SUBJ_ID = OPB_MAPPING.SUBJECT_ID
    AND OPB_MAPPING.MAPPING_ID = SOURCE_INSTANCES.MAPPING_ID
    AND SOURCE_INSTANCES.WIDGET_TYPE = 1 
    AND OPB_SRC.SRC_ID = SOURCE_INSTANCES.WIDGET_ID
    AND OPB_MAPPING.REF_WIDGET_ID = 0
    AND OPB_MAPPING.VERSION_NUMBER = SOURCE_INSTANCES.VERSION_NUMBER
    AND OPB_MAPPING.IS_VISIBLE = 1
    AND OPB_SRC.IS_VISIBLE = 1    
--and OPB_MAPPING.mapping_id=40;

-- mapping-->target_table
SELECT distinct
    OPB_SUBJECT.SUBJ_ID folder_ID,
    OPB_SUBJECT.SUBJ_NAME folder_name,
    OPB_MAPPING.MAPPING_ID,
    OPB_MAPPING.MAPPING_NAME,
    OPB_TARG.TARGET_ID,
    OPB_TARG.TARGET_NAME
FROM
    ETL_ODS_WIN.OPB_TARG, 
    ETL_ODS_WIN.OPB_SUBJECT, 
    ETL_ODS_WIN.OPB_MAPPING, 
    ETL_ODS_WIN.OPB_WIDGET_INST TARGET_INSTANCES
WHERE
    OPB_SUBJECT.SUBJ_ID = OPB_MAPPING.SUBJECT_ID
    AND OPB_MAPPING.MAPPING_ID = TARGET_INSTANCES.MAPPING_ID 
    AND TARGET_INSTANCES.WIDGET_TYPE = 2 
    AND TARGET_INSTANCES.WIDGET_ID = OPB_TARG.TARGET_ID  
    AND OPB_MAPPING.REF_WIDGET_ID = 0
    AND OPB_MAPPING.VERSION_NUMBER = TARGET_INSTANCES.VERSION_NUMBER
    AND OPB_MAPPING.IS_VISIBLE = 1
    AND OPB_TARG.IS_VISIBLE = 1
--and OPB_MAPPING.mapping_id=126;

-- floder-->workflow-->session-->mapping
SELECT distinct
f.subj_id as folder_id,
f.subj_name AS folder_name,
wf.task_id as workflow_id,
wf.task_name AS workflow_name,
se.task_id as session_id,
se.instance_name AS session_name,
m.mapping_id,
m.mapping_name
  FROM ETL_ODS_WIN.opb_subject f,
       ETL_ODS_WIN.opb_task wf,
       (SELECT workflow_id,
               instance_id,
               task_id,
               task_type,
               instance_name,
               MAX(version_number)
          FROM ETL_ODS_WIN.opb_task_inst sess
         WHERE sess.task_type = 68
         GROUP BY workflow_id,
                  instance_id,
                  task_id,
                  task_type,
                  instance_name) se,
       (SELECT session_id, mapping_id, MAX(version_number)
          FROM ETL_ODS_WIN.opb_session
         GROUP BY session_id, mapping_id) s,
       ETL_ODS_WIN.opb_mapping m
 WHERE 
 wf.subject_id = f.subj_id
 AND se.workflow_id = wf.task_id
 --AND wf.task_type = 71
 AND se.task_id = s.session_id
 AND s.mapping_id = m.mapping_id
--and m.mapping_name='tfundinfo'
--and se.task_id=76;

--session-->connection
SELECT * from etl_dwmin.REP_SESSION_INSTANCES where connection_name='Greonn_Comm';

--session-->connection
SELECT
DISTINCT
    B.SUBJ_NAME SUBJECT_AREA,
    B.SUBJ_ID SUBJECT_ID,
    A.TASK_NAME SESSION_NAME,
    A.TASK_ID SESSION_ID,
    (E.OBJECT_TYPE - 78) IS_TARGET,
    C.OBJECT_NAME CONNECTION_NAME,
    D.REF_OBJECT_ID CONNECTION_ID,
    A.VERSION_NUMBER SESSION_VERSION_NUMBER
FROM
    OPB_TASK A, OPB_SUBJECT B, OPB_CNX C,
    OPB_SESS_CNX_REFS D, OPB_SESS_EXTNS E
WHERE
    A.SUBJECT_ID = B.SUBJ_ID AND
    A.TASK_TYPE = 68 AND -- A.IS_REUSABLE = 1 AND
    D.WORKFLOW_ID = 0 AND D.SESSION_INST_ID = 0 AND
    A.TASK_ID   = D.SESSION_ID AND
    D.SESSION_ID = E.SESSION_ID AND
    D.SESS_WIDG_INST_ID = E.SESS_WIDG_INST_ID AND
    C.OBJECT_ID = D.REF_OBJECT_ID AND
    A.VERSION_NUMBER = D.VERSION_NUMBER AND
    D.VERSION_NUMBER = E.VERSION_NUMBER AND
    A.IS_VISIBLE = 1
--and C.OBJECT_NAME='GJODS_DBINTERFACE';

--获取工作流运行时间等记录（只要有一个任务执行就会被记录到）
--修改后可获取某工作流的最后执行时间、某集成服务的最后执行时间等
select c.*
from etl_ods_win.OPB_WFLOW_RUN c, etl_ods_win.rep_workflows d
where c.workflow_id = d.workflow_id
and d.WORKFLOW_NAME='wf_table_migrate_timely02'
and c.server_name='inter_ods'
order by c.start_time desc

--查询连接信息
--alter session set current_schema=ETL_ODS;
SELECT DISTINCT
a.object_name as connection_name,
a.user_name as conn_user_name,
case when a.OBJECT_SUBTYPE = 101 then 'Oracle'
when a.OBJECT_SUBTYPE = 104 then 'Microsoft SQL Server'
when a.OBJECT_SUBTYPE = 106 then 'ODBC'
when a.OBJECT_SUBTYPE = 0 then 'FTP'
when a.OBJECT_SUBTYPE = 404000 then 'Lotus Notes' end as type,
a.connect_string
FROM ETL_ODS_WIN.opb_cnx a,  ETL_ODS_WIN.opb_object_access d
WHERE  a.object_id = d.object_id 
ORDER BY type,connection_name

--查询带有presql、postsql的任务
select t3.subj_name,t1.task_id,t1.task_name,t2.attr_id,
case when attr_id=6 then 'pre_sql'
when attr_id=7 then 'post_sql'
end attr_type,
t2.attr_value sql
from rep_prd.OPB_TASK t1
left join rep_prd.opb_swidget_attr t2 on t1.task_id=t2.session_id 
left join rep_prd.opb_subject t3 on t1.subject_id=t3.subj_id
where t2.attr_value like '%DELETE%' or t2.attr_value like '%delete%'
order by subj_name,task_name

--查看任务是否勾选了truncate选项（1表示选中truncate），仅适用于oracle目标，其他情况需修改
select t1.instance_name,t2.object_type,t2.attr_value,t2.attr_id from rep_gjzq_prd.opb_task_inst t1
left join rep_gjzq_prd.opb_extn_attr t2
on t1.task_id=t2.session_id
left join rep_gjzq_prd.opb_task t3
on t1.workflow_id=t3.task_id
left join rep_gjzq_prd.opb_subject t4
on t3.subject_id=t4.subj_id
where t4.subj_name='From_IR'
and t2.attr_id=9

--根据任务信息查找任务位置
select opb_subject.subj_name,opb_task.task_name,opb_task_inst.instance_name from
opb_subject,opb_task,opb_task_inst,opb_extn_attr
where 
opb_subject.subj_id=opb_task.subject_id
and opb_task.subject_id=opb_subject.subj_id
and opb_task_inst.workflow_id=opb_task.task_id
and opb_task_inst.task_id=opb_extn_attr.session_id
and opb_extn_attr.attr_value='para_tradedate_cltlast.txt'