Informatica Repository Query to get Workflow, Session, Mapping and Source/Target of Mapping

Question

for cleaning up unused IPC-Sources I need a Repository Query for getting Workflow, Session, Mapping and Source/Target of Mapping.I have startet by joining REP_LOAD_SESSIONS and REP_TBL_MAPPING on mapping_id but only a fraction of mappings seem to be present in the joined output. I can't find the right tables to join to get the job done. Any help will be greatly appreciated!

Answer 1

I was struggling with the same issue. Here is my query. Hope it helps

SELECT SUBJECT_AREA,SESSIONNAME,MPGANDP MAPPINGNAME,SOURCENAMES,TARGET_NAMES,INSTANCE_NAME,LOOKUPTABLENAME,CASE WHEN OBJECTTYPE='Lookup ' THEN CONNECTION ELSE CNX_NAME END CONNECTIONNAME,USER_NAME
      FROM 
        ( SELECT * FROM
            (SELECT SUBJECT_AREA,SESSION_ID,MPGANDP, MPNGID,OBJECTTYPE,INSTANCE_NAME,MAX(LOOKUPTABLE) LOOKUPTABLENAME, MAX(CONNECTION) CONNECTION 
          --,LISTAGG(SQLQUERY, '' ) WITHIN GROUP (ORDER BY SQLQUERY) SQLOVERRIRDE
             FROM
                ( 
                SELECT CASE WHEN MAPPING_NAME=PARENT_MAPPING_NAME THEN MAPPING_NAME ELSE MAPPING_NAME||','||PARENT_MAPPING_NAME END MPGANDP, B.MAPPING_ID MPNGID,
                    SUBSTR(WIDGET_TYPE_NAME,1,INSTR(WIDGET_TYPE_NAME,' ')) OBJECTTYPE, INSTANCE_NAME, CASE WHEN UPPER(ATTR_NAME) ='CONNECTION INFORMATION' THEN ATTR_VALUE ELSE NULL END CONNECTION,
                    ATTR_NAME, ATTR_VALUE,SUBJECT_AREA, --A.*,B.*,C.* 
                    --CASE WHEN ATTR_NAME='Sql Query' OR ATTR_NAME='Lookup Sql Override' THEN ATTR_VALUE END SQLQUERY,
                    CASE WHEN ATTR_NAME='Lookup table name' THEN ATTR_VALUE END LOOKUPTABLE,
                    CASE WHEN ATTR_NAME='Sql Query' OR ATTR_NAME='Lookup Sql Override' THEN SUBSTR(ATTR_VALUE,INSTR(UPPER(ATTR_VALUE),'FROM'),15) END SQLQUERYV
                FROM  REP_WIDGET_INST A 
                    INNER JOIN REP_ALL_MAPPINGS B 
                        ON A.MAPPING_ID = B.MAPPING_ID 
                    INNER JOIN REP_WIDGET_ATTR C 
                        ON A.WIDGET_ID = C.WIDGET_ID
                WHERE A.WIDGET_TYPE IN (2, 11,3)
                    --AND  MAPPING_NAME<>PARENT_MAPPING_NAME  
                    --AND B.MAPPING_ID=515
                    --AND PARENT_SUBJECT_AREA='EDW'
                    AND ATTR_NAME IN ( 'Connection Information','Lookup Sql Override','Lookup table name','Sql Query')
                ) , OPB_SESSION
             WHERE MPNGID=MAPPING_ID
             GROUP BY SUBJECT_AREA,MPGANDP, MPNGID,OBJECTTYPE,INSTANCE_NAME,SESSION_ID 
            ) T1
        INNER JOIN 
            (SELECT OPB_TASK_INST.WORKFLOW_ID,OPB_TASK_INST.TASK_ID ,OPB_TASK_INST.INSTANCE_NAME SESSIONNAME
             FROM OPB_TASK_INST 
             WHERE OPB_TASK_INST.TASK_TYPE IN (68) --,70)
             START WITH WORKFLOW_ID IN          (SELECT TASK_ID FROM OPB_TASK WHERE TASK_TYPE = 71 AND           /* **************SPECIFY WORKFLOW NAME HERE*********/                   TASK_NAME='wf_TEST')
             CONNECT BY PRIOR OPB_TASK_INST.TASK_ID = OPB_TASK_INST.WORKFLOW_ID ) WFSESSCONN
        ON TASK_ID=SESSION_ID
        INNER JOIN 
            ( SELECT MAPPING_ID MAPID,LISTAGG(SOURCE_NAME,',') WITHIN GROUP (ORDER BY SOURCE_NAME) SOURCENAMES
             FROM REP_SRC_MAPPING E
             GROUP BY  SUBJECT_AREA,MAPPING_NAME,MAPPING_ID  ) SOURCENAMES
        ON MAPID=MPNGID

        LEFT JOIN 
          (SELECT DISTINCT SUBJECT_AREA SA,TASK_NAME,INSTANCE_NAME INSNAME,CNX_NAME,SESSION_ID SSID
            FROM
            REP_ALL_TASKS A,
            REP_SESS_WIDGET_CNXS B
            WHERE
              A.TASK_ID = B.SESSION_ID 
          ) T2
        ON SESSION_ID=SSID
        AND INSNAME=INSTANCE_NAME
        AND SUBJECT_AREA=SA 

        LEFT JOIN 
            ( SELECT SUBJECT_AREA SAT, SESSION_NAME SESSNT, SESSION_ID SSIDT, LISTAGG(WIDGET_NAME,',') WITHIN GROUP (ORDER BY WIDGET_NAME) AS TARGET_NAMES
              FROM (SELECT distinct SUBJECT_AREA,SESSION_NAME,SESSION_ID,WIDGET_NAME
                      FROM  REP_SESS_TBL_LOG
                      WHERE TYPE_NAME='Target Definition' )  
             GROUP BY SUBJECT_AREA,SESSION_NAME,SESSION_ID 
            )
        ON SESSION_ID=SSIDT
        )
    LEFT JOIN OPB_CNX
      ON TRIM(OBJECT_NAME)=TRIM(CASE WHEN OBJECTTYPE='Lookup ' THEN CONNECTION ELSE CNX_NAME END)
    ORDER BY SUBJECT_AREA,SESSIONNAME,MPGANDP,INSTANCE_NAME