1) Metaquery tools needs only repository access.
https://community.informatica.com/solutions/metaquery
2) Query against repository
SELECT DISTINCT TAR.SUBJECT_AREA,TAR.MAPPING_NAME,TAR.SESSION_NAME,SRC.SOURCE_NAME,TAR.TARGET_NAME FROM
(SELECT distinct OPB_TARG.TARGET_NAME TARGET_NAME,
OPB_TARG.BUSINESS_NAME TARG_BUSNAME,
OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
OPB_MAPPING.MAPPING_NAME MAPPING_NAME,
REP_SESS_LOG.SESSION_NAME SESSION_NAME,
OPB_MAPPING.MAPPING_ID MAPPING_ID
FROM OPB_TARG,
OPB_SUBJECT,
OPB_MAPPING,
REP_SESS_LOG,
OPB_TDS,
OPB_WIDGET_INST
WHERE OPB_TDS.MAPPING_ID = OPB_MAPPING.MAPPING_ID
AND OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID
and REP_SESS_LOG.mapping_name= OPB_MAPPING.MAPPING_NAME
AND OPB_WIDGET_INST.WIDGET_TYPE = 2
AND OPB_WIDGET_INST.WIDGET_ID = OPB_TARG.TARGET_ID
AND OPB_TDS.TARGET_INSTANCE_ID = OPB_WIDGET_INST.INSTANCE_ID
AND OPB_TARG.SUBJ_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_MAPPING.REF_WIDGET_ID = 0
AND OPB_MAPPING.IS_VISIBLE = 1
AND OPB_TARG.IS_VISIBLE = 1) TAR,
(SELECT distinct OPB_SRC.SOURCE_NAME,
OPB_SRC.SRC_ID SOURCE_ID,
OPB_SRC.BUSINESS_NAME SRC_BUSNAME,
OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
OPB_SUBJECT.SUBJ_ID SUBJECT_ID,
OPB_MAPPING.MAPPING_NAME,
REP_SESS_LOG.SESSION_NAME SESSION_NAME,
OPB_MAPPING.MAPPING_ID MAPPING_ID
FROM OPB_SRC,
OPB_SUBJECT,
OPB_MAPPING,
REP_SESS_LOG,
OPB_TDS,
OPB_WIDGET_INST
WHERE OPB_TDS.MAPPING_ID = OPB_MAPPING.MAPPING_ID
AND OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID
and REP_SESS_LOG.mapping_name= OPB_MAPPING.MAPPING_NAME
AND OPB_WIDGET_INST.WIDGET_TYPE = 1
AND OPB_WIDGET_INST.WIDGET_ID = OPB_Src.src_id
AND OPB_src.SUBJ_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_MAPPING.REF_WIDGET_ID = 0
AND OPB_MAPPING.IS_VISIBLE = 1
AND OPB_Src.IS_VISIBLE = 1) SRC
WHERE TAR.MAPPING_ID=SRC.MAPPING_ID
https://community.informatica.com/solutions/metaquery
2) Query against repository
SELECT DISTINCT TAR.SUBJECT_AREA,TAR.MAPPING_NAME,TAR.SESSION_NAME,SRC.SOURCE_NAME,TAR.TARGET_NAME FROM
(SELECT distinct OPB_TARG.TARGET_NAME TARGET_NAME,
OPB_TARG.BUSINESS_NAME TARG_BUSNAME,
OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
OPB_MAPPING.MAPPING_NAME MAPPING_NAME,
REP_SESS_LOG.SESSION_NAME SESSION_NAME,
OPB_MAPPING.MAPPING_ID MAPPING_ID
FROM OPB_TARG,
OPB_SUBJECT,
OPB_MAPPING,
REP_SESS_LOG,
OPB_TDS,
OPB_WIDGET_INST
WHERE OPB_TDS.MAPPING_ID = OPB_MAPPING.MAPPING_ID
AND OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID
and REP_SESS_LOG.mapping_name= OPB_MAPPING.MAPPING_NAME
AND OPB_WIDGET_INST.WIDGET_TYPE = 2
AND OPB_WIDGET_INST.WIDGET_ID = OPB_TARG.TARGET_ID
AND OPB_TDS.TARGET_INSTANCE_ID = OPB_WIDGET_INST.INSTANCE_ID
AND OPB_TARG.SUBJ_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_MAPPING.REF_WIDGET_ID = 0
AND OPB_MAPPING.IS_VISIBLE = 1
AND OPB_TARG.IS_VISIBLE = 1) TAR,
(SELECT distinct OPB_SRC.SOURCE_NAME,
OPB_SRC.SRC_ID SOURCE_ID,
OPB_SRC.BUSINESS_NAME SRC_BUSNAME,
OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
OPB_SUBJECT.SUBJ_ID SUBJECT_ID,
OPB_MAPPING.MAPPING_NAME,
REP_SESS_LOG.SESSION_NAME SESSION_NAME,
OPB_MAPPING.MAPPING_ID MAPPING_ID
FROM OPB_SRC,
OPB_SUBJECT,
OPB_MAPPING,
REP_SESS_LOG,
OPB_TDS,
OPB_WIDGET_INST
WHERE OPB_TDS.MAPPING_ID = OPB_MAPPING.MAPPING_ID
AND OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID
and REP_SESS_LOG.mapping_name= OPB_MAPPING.MAPPING_NAME
AND OPB_WIDGET_INST.WIDGET_TYPE = 1
AND OPB_WIDGET_INST.WIDGET_ID = OPB_Src.src_id
AND OPB_src.SUBJ_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_MAPPING.REF_WIDGET_ID = 0
AND OPB_MAPPING.IS_VISIBLE = 1
AND OPB_Src.IS_VISIBLE = 1) SRC
WHERE TAR.MAPPING_ID=SRC.MAPPING_ID
No comments:
Post a Comment