10.1 List mapping names
SELECT SUBJECT_AREA, PARENT_MAPPING_NAME
FROM REP_ALL_MAPPINGS
ORDER BY 1, 2
10.2 List total count of mappings
SELECT SUBJECT_AREA, COUNT(PARENT_MAPPING_NAME) AS TOTAL_MAPPINGS
FROM REP_ALL_MAPPINGS
GROUP BY SUBJECT_AREA
ORDER BY 1, 2
10.3 List last saved user for a mapping
SELECT REP_SUBJECT.SUBJECT_AREA "FOLDER",REP_VERSION_PROPS.OBJECT_NAME
"MAPPING", REP_USERS.USER_NAME,REP_VERSION_PROPS.LAST_SAVED
FROM REP_USERS,REP_VERSION_PROPS,REP_SUBJECT
WHERE REP_USERS.USER_ID=REP_VERSION_PROPS.USER_ID
AND REP_VERSION_PROPS.OBJECT_TYPE
IN (21)
--AND REP_SUBJECT.SUBJECT_AREA
= 'FOLDER_NAME'
AND REP_SUBJECT.SUBJECT_ID
= REP_VERSION_PROPS.SUBJECT_ID
ORDER BY 1,2,3,4
10.4 List Mapping parameters and variables
select distinct rep_reposit_info.repository_name, rep_all_mappings.subject_area
as folder_name, rep_all_mappings.mapping_name as
object_name,
case when opb_map_parmvar.pv_flag = 2 then 'Mapping Parameter' else
case when opb_map_parmvar.pv_flag = 3 then 'Mapping Variable' end
end as parameter_type, opb_map_parmvar.pv_name as
parameter_name,
opb_map_parmvar.pv_default as
parameter_value,
opb_map_parmvar.pv_desc as
description
from rep_all_mappings, opb_map_parmvar,
rep_reposit_info
where rep_all_mappings.mapping_id = opb_map_parmvar.mapping_id
10.5 List all the mappings using PARALLEL hints
SELECT S.SUBJ_NAME, M.MAPPING_NAME, W.WIDGET_NAME, A.WIDGET_ID, W.VERSION_NUMBER,
SUBSTR(A.ATTR_VALUE, 1, 60)
ATTR_VALUE
FROM
OPB_WIDGET_ATTR A,OPB_WIDGET W,OPB_SUBJECT S,OPB_WIDGET_INST
I,
OPB_MAPPING
M
WHERE A.WIDGET_ID = W.WIDGET_ID
AND W.IS_VISIBLE = 1
AND A.VERSION_NUMBER = W.VERSION_NUMBER
AND A.WIDGET_TYPE IN(2, 3, 11) --Limit to Src/Tgt/Lkp
Transformations
AND W.WIDGET_ID = I.WIDGET_ID
AND W.VERSION_NUMBER = I.VERSION_NUMBER
AND I.MAPPING_ID = M.MAPPING_ID
AND I.VERSION_NUMBER = M.VERSION_NUMBER
AND W.SUBJECT_ID = S.SUBJ_ID
AND UPPER(A.ATTR_VALUE) LIKE '%PARALLEL%'
10.6 Mapping Parameter/variable Identifier
Below query identifies the Parameter/variables used in the mappings across all folders in Informatica repository.
SELECT sub.subj_name AS subject_name, MAP.mapping_name,
mpv.pv_name AS parameter_variable_name
FROM opb_map_parmvar mpv,
(SELECT subject_id, mapping_id, mapping_name, version_number
FROM opb_mapping
WHERE is_visible = 1 AND ref_widget_id = 0) MAP,
opb_subject sub
WHERE sub.subj_id = MAP.subject_id
AND MAP.mapping_id = mpv.mapping_id
AND MAP.version_number = mpv.version_number
/* Input your mapping name */
-- AND MAP.mapping_name = 'm_mappname' ORDER BY 1, 2, 3
10.7 Unconnected Ports in Mapping
Below query identifies the unconnected ports for a particular mapping in Informatica repository.
SELECT mapping_name, subject_area, object_type_name AS transformation,
object_instance_name AS transformation_name, field_name
FROM rep_mapping_unconn_ports
WHERE mapping_name = 'm_EXTRACT_CTRM_CX_CTA_XM'
10.8 Mapping level pre/post sql’s
Below query identifies mapping level pre/post sql quires.
/SELECT s.subj_name AS subject_name, m.mapping_name,
tgt.target_name target_table,
DECODE (OWA.attr_id,
6, 'PRE_SQL',
7, 'POST_SQL',
'Update Override'
) AS sql_type,
OWA.attr_value AS SQL
FROM opb_subject s,
(SELECT subject_id, mapping_id, mapping_name, version_number
FROM opb_mapping
WHERE is_visible = 1 AND ref_widget_id = 0) m,
opb_widget_inst owi,
opb_widget_attr OWA,
(SELECT target_id, subj_id, target_name, versionid, version_number,
is_visible
FROM opb_targ
WHERE is_visible = 1) tgt
WHERE s.subj_id = m.subject_id
AND m.mapping_id = owi.mapping_id
AND m.version_number = owi.version_number
AND owi.widget_id = OWA.widget_id
AND owi.instance_id = OWA.instance_id
AND owi.version_number = OWA.version_number
AND owi.widget_type = 2
AND owi.widget_type = OWA.widget_type
AND OWA.attr_value IS NOT NULL
AND owi.widget_id = tgt.target_id
-- AND m.mapping_name = 'm_LOAD_ODS_D'
ORDER BY 1, 2