Informatica Metadata Queries
Post date: 29-Jul-2010 13:49:14
Informatica Metadata Queries
In Informatica normally we come across situation like we need to find all mapping which use a particular table as source or something similar .It is very difficult to find it through Informatica tools.In such a situation Repository tables are very useful.
Repository tables name start with OPB and are continuously updated when we make changes.Along with OPB tables REP views are also present.
It is very dangerous to modify these tables so due care should be taken while dealing with OPB Tables.
In this article we will focus on some useful meta data queries
Purpose : Search for a table in Source Qualifiers Sql Override:
Query :
select distinct SUB.subj_name, MAP.mapping_name
from opb_widget_attr WID, opb_mapping MAP, opb_widget_inst WIDINST,
opb_subject SUB
where WID.widget_id = WIDINST.widget_id
and WID.widget_type = WIDINST.widget_type
and WID.widget_type = 3
and WID.attr_id = 1
and WIDINST.mapping_id = MAP.mapping_id
and MAP.subject_id = SUB.subj_id
and upper(WID.attr_value) like '%TNAME%' ;
Description : This query will give list of all mappings where a particular table is being used in sql override.
Purpose : Search for a table in Sources and Targets :
Query :
select SUB.subj_name, decode(WIDG.widget_type,1,'Source',2,'Target'),
MAP.mapping_name,
WIDG.instance_name
from opb_widget_inst WIDG, opb_mapping MAP, opb_subject SUB
where SUB.subj_id = MAP.subject_id
and WIDG.mapping_id = MAP.mapping_id
and WIDG.widget_type in (1,2)
and WIDG.instance_name like '%TNAME_%'
and SUB.subJ_NAME='YOUR_FOLDER_NAME'
Description : This query will give list of all folders,mappings where a particular table is being used as source or target instance.
Purpose : Query to give lookup information
Query :
Select distinct wid.WIDGET_ID, 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 all_map.mapping_id=wid.mapping_id
and wid.WIDGET_ID=widat.WIDGET_ID
and all_map.subject_area='DCM_SPP_UPL_DEVT'
and wid.WIDGET_TYPE=11
and widat.WIDGET_TYPE=11
and widat.ATTR_ID in (2,6)
Description : This query will give information about lookup transformations like lookup name,Tablename ,Mapping name etc.
Purpose : Query to give Invalid workflows
Query :
select opb_subject.subj_name, opb_task.task_name
from opb_task, opb_subject
where task_type = 71
and is_valid = 0
and opb_subject.subj_id = opb_task.subject_id
and UPPER(opb_subject.SUBJ_NAME) like UPPER('YOUR_FOLDER_NAME')
Description : This query will list of all invalid
Purpose : To Find Truncate Table Option
Query :
select task_name,'Truncate Target Table' ATTR,decode(attr_value,1,'Yes','No') Value
from OPB_EXTN_ATTR OEA,REP_ALL_TASKS RAT
where OEA.SESSION_ID=rat.TASK_ID
and attr_id=9
Description : This query will give all sessions where Truncate Table Option is on
Purpose : This query will give count of Mapping,Instance where Sqloverride has been done
Query : WITH detail AS
(SELECT 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
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
)
SELECT subject_area, 'SQ_OVERIDE' STATUS,
COUNT (DISTINCT mapping_name) NO_OF_Mapping,
COUNT (DISTINCT (mapping_name || source_qualifier_name)) NO_OF_SQ_IN_MAPPING,
COUNT (DISTINCT (source_qualifier_name)) NO_OF_DISTINCT_SQ
FROM detail
WHERE OVR_OK =1
GROUP BY subject_area
UNION
SELECT subject_area, 'SQ_NON_OVERIDE',
COUNT (DISTINCT mapping_name) nb_mapping,
COUNT (DISTINCT (mapping_name || source_qualifier_name)) nb_map_inst,
COUNT (DISTINCT (source_qualifier_name)) nb_inst
FROM detail
WHERE OVR_OK =0
GROUP BY subject_area
Description : This query will count of Mapping,SQ in mappings,SQ where override has been done.
Purpose : This query will SQL Override queries upto 4000 length
Query :
SELECT subj_name, mapping_name,widget_id,
replace(replace(replace (next_attr_val0||next_attr_val1,' ',' '),chr(13),' '),chr(10),' ') QUERY_OP_1
fROM
(
select distinct SUB.subj_name subj_name, MAP.mapping_name mapping_name,wid.widget_id,
WID.attr_value next_attr_val0,
LEAD(WID.ATTR_VALUE,1,'') OVER ( PARTITION BY wid.WIDGET_ID,wid.MAPPING_ID ORDER BY wid.WIDGET_ID,wid.MAPPING_ID,wid.LINE_NO) NEXT_ATTR_VAL1,
WID.LINE_NO
from opb_widget_attr WID, opb_mapping MAP, opb_widget_inst WIDINST,
opb_subject SUB
where WID.widget_id = WIDINST.widget_id
and WID.widget_type = WIDINST.widget_type
and WID.widget_type = 3
and WID.attr_id = 1
and WIDINST.mapping_id = MAP.mapping_id
and MAP.subject_id = SUB.subj_id
ORDER BY SUB.subj_name, MAP.mapping_name,wid.widget_id,WID.LINE_NO
)
WHERE next_attr_val0||next_attr_val1 LIKE '%UPL%'
and line_no=1
Purpose : This query will SQL Override queries upto 4000 length.This query will work in oracle database