Informatica Repository Queries

These queries will easily help you to track the informatica run details...

alter session set current_schema = "SCHEMANAME";

Find sources, mappings that are using this table as target

SELECT DISTINCT SUB.SUBJ_NAME,M.MAPPING_NAME,S.SOURCE_NAME,t.target_name
FROM
OPB_MAPPING M,OPB_SUBJECT SUB,OPB_SRC S,
(SELECT WIDGET_ID, MAPPING_ID,WIDGET_TYPE,MAX(VERSION_NUMBER) VERSION_NUMBER FROM OPB_WIDGET_INST GROUP BY WIDGET_ID, MAPPING_ID, WIDGET_TYPE HAVING WIDGET_TYPE = '1') Ws,
(SELECT WIDGET_ID, MAPPING_ID,WIDGET_TYPE,MAX(VERSION_NUMBER)
VERSION_NUMBER FROM OPB_WIDGET_INST GROUP BY WIDGET_ID, MAPPING_ID, WIDGET_TYPE HAVING WIDGET_TYPE = '2') Wt,
(SELECT DISTINCT TARGET_ID,TARGET_NAME FROM OPB_TARG WHERE TARGET_NAME IN
('TABLENAME')
) t
WHERE
Ws.MAPPING_ID = M.MAPPING_ID AND
M.SUBJECT_ID = SUB.SUBJ_ID AND
M.REF_WIDGET_ID = 0 and
S.SRC_ID = Ws.WIDGET_ID AND
t.target_id = wt.widget_id and
wt.mapping_id = ws.mapping_id
ORDER BY
SUB.SUBJ_NAME, M.MAPPING_NAME,t.target_name,s.source_name;

Find session run details using a table/session name

SELECT R.START_TIME,R.END_TIME,round((R.END_TIME-R.START_TIME)*24*60*60) DURN, R.SUCCESSFUL_ROWS,R.LAST_ERROR,R.TABLE_NAME, R.SUBJECT_AREA,R.SESSION_NAME,R.*
FROM REP_SESS_TBL_LOG R WHERE
 R.SESSION_NAME IN (
'SESSIONNAME'
 )
--R.TABLE_NAME IN ('GL_JE_HEADERS')
--  AND TRUNC(R.START_TIME) between 'Nov 13 2012'  and 'Nov 17 2012'
ORDER BY r.START_TIME DESC;


Find mappings that are using this table as source

SELECT DISTINCT SUB.SUBJ_NAME "Folder",M.MAPPING_NAME "Mapping",  S.TARGET_NAME "Target"
--,T.TARGET_NAME
--,W.INSTANCE_NAME
,W.WIDGET_TYPE, W.version_number,W.WIDGET_ID
FROM
OPB_MAPPING M,OPB_SUBJECT SUB,
OPB_TARG S,
(SELECT WIDGET_ID, MAPPING_ID,WIDGET_TYPE,MAX(VERSION_NUMBER) VERSION_NUMBER FROM OPB_WIDGET_INST GROUP BY WIDGET_ID, MAPPING_ID, WIDGET_TYPE HAVING WIDGET_TYPE = '2') W
WHERE
W.MAPPING_ID = M.MAPPING_ID AND
M.SUBJECT_ID = SUB.SUBJ_ID AND
M.REF_WIDGET_ID = 0 and
S.TARGET_ID = W.WIDGET_ID AND
W.MAPPING_ID IN (
(SELECT DISTINCT WI.MAPPING_ID FROM (SELECT WIDGET_ID, MAPPING_ID,WIDGET_TYPE,MAX(VERSION_NUMBER),INSTANCE_NAME VERSION_NUMBER FROM OPB_WIDGET_INST
GROUP BY WIDGET_ID, MAPPING_ID, WIDGET_TYPE, INSTANCE_NAME HAVING WIDGET_TYPE = '1') WI WHERE WI.WIDGET_ID IN
(select distinct SR.SRC_ID from OPB_SRC SR where SR.SOURCE_NAME in ( /*,sr.SOURCE_NAME, sr.**/
'TABLENAME'
)
)
))
order by
SUB.SUBJ_NAME, M.MAPPING_NAME;
-- workflow runs
select * from REP_WFLOW_RUN where workflow_name = 'WORKFLOWNAME' order by start_time desc;

Find mapping in which a table is used in SQ override

SELECT distinct
       c.subject_area,
       c.mapping_name,
       d.instance_name source_qualifier_name,
       CASE WHEN a.attr_value IS NOT NULL
            THEN 1 ELSE 0
       END AS OVR_OK
--       , a.attr_value
FROM
       rep_all_mappings c,
       opb_widget_inst d,
       opb_widget_attr a
WHERE
       c.mapping_id = d.mapping_id
       AND c.mapping_version_number = d.version_number
       AND d.widget_type = 3
       AND d.widget_id = a.widget_id
       AND a.widget_type = d.widget_type
       AND A.ATTR_ID = 1 and a.attr_value like '%AP_INVOICE_DISTRIBUTIONS_ALL%';

Find mapping in which a table is used as lookup

Select distinct subject_area,
       all_map.mapping_name,
       wid.INSTANCE_NAME Lkp_name,
       Decode(widat.attr_id,2,widat.attr_value) Table_name,
       decode (widat.attr_id,6,widat.attr_value) src_tgt
FROM
       rep_all_mappings ALL_MAP,
       rep_widget_inst wid,
       OPB_WIDGET_ATTR widat
where
       Decode(widat.attr_id,2,widat.attr_value)='AP_INVOICE_DISTRIBUTIONS_ALL' AND all_map.mapping_id=wid.mapping_id
       and wid.WIDGET_ID=widat.WIDGET_ID
       and wid.WIDGET_TYPE=11
       AND WIDAT.WIDGET_TYPE=11
       and widat.ATTR_ID in (2,6) ;

/*workflows, sessions from mapping name*/

sessions,  mappings from workflow name

SELECT  sub.subj_name "Folder", T.TASK_NAME "Workflow", TI.INSTANCE_NAME "Session" ,M.MAPPING_NAME "Mapping" , S.MAPPING_ID FROM
OPB_MAPPING M, OPB_TASK t,OPB_SUBJECT sub, (SELECT MAX(VERSION_NUMBER) ,INSTANCE_NAME ,TASK_ID ,INSTANCE_ID ,WORKFLOW_ID
FROM OPB_TASK_INST WHERE TASK_TYPE = '68' GROUP BY INSTANCE_NAME ,TASK_ID ,INSTANCE_ID ,WORKFLOW_ID ) ti,
(SELECT SESSION_ID, MAPPING_ID, MAX(VERSION_NUMBER) FROM OPB_SESSION GROUP BY SESSION_ID, MAPPING_ID ) S WHERE
M.MAPPING_ID = S.MAPPING_ID and S.SESSION_ID = TI.TASK_ID and T.TASK_ID = TI.WORKFLOW_ID and T.IS_VISIBLE = 1 and M.IS_VISIBLE =  1
and sub.subj_id=t.subject_id AND M.MAPPING_NAME IN ('m_edwstg_edwbas_forecast');--and T.TASK_NAME in ('wf_reconciliation');

SELECT distinct SUBJECT_AREA,WORKFLOW_NAME,INSTANCE_NAME FROM  REP_TASK_INST_RUN WHERE
TASK_ID IN
(select distinct session_id from REP_LOAD_SESSIONS where mapping_id in (
SELECT DISTINCT MAPPING_ID FROM REP_ALL_MAPPINGS WHERE MAPPING_NAME IN (
'm_edwstg_edwbas_forecast'
    )
  )
);

Ref:

https://sites.google.com/site/ashishrkabra/informatica