Tuesday, 9 October 2012

Query on nformatica Repository query to get all the workflows,corresponding sessions,corresponding mappings -sources & Targets for a given folder

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

No comments:

Post a Comment