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,
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
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)
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)
