Informatica Repository Query to Find All the Source and Target connections in Your Respository

The below Informatica repository query can be used to get the Source Connections, Target connections of all the instances used in the informatica.

Apart from that we have details of the Folder, mapping name ,session name , and the connection name as shown in Informatica.

SELECT DISTINCT 

C.SUBJECT_AREA FOLDER_NAME, 
C.MAPPING_NAME MAPPING_NAME, 
C.SESSION_NAME SESSION_NAME, 
E.READER_WRITER_TYPE, 
E.INSTANCE_NAME, 
E.CNX_NAME, 

CASE 
WHEN E.WIDGET_TYPE = 2 
THEN 'TARGET CONNECTION' 
ELSE 
CASE 
WHEN E.WIDGET_TYPE IN (1, 3, 56, 45, 55, 84) 
THEN 
'SOURCE CONNECTION' 
ELSE 
NULL 
END 
END CNX_TYPE 

FROM INFA_REPO.REP_VERSION_PROPS A, 
     INFA_REPO.REP_USERS B, 
     INFA_REPO.REP_LOAD_SESSIONS C, 
     INFA_REPO.REP_REPOSIT_INFO D, 
     INFA_REPO.REP_SESS_WIDGET_CNXS E 

WHERE ( A.USER_ID = B.USER_ID 
AND C.SESSION_ID <> D.REPOSITORY_ID 
AND C.SESSION_ID = A.OBJECT_ID 
AND C.SUBJECT_ID = A.SUBJECT_ID 
AND A.OBJECT_TYPE = 68 
AND C.SESSION_ID = E.SESSION_ID 
AND C.SESSION_VERSION_NUMBER = 
E.SESSION_VERSION_NUMBER)

Tweet