Often as an informatica power user or administrator you would need informatica connection details using script. Whenever user creates new connection inside informatica either through power center manager or using pmrep command the connection details get stored inside informatica repository database. To get those details using database query you can look inside table
OPB_MMD_CNX_ATTR. Below are the some of the important columns from these tables.
OPB_CNX_ATTR stores details where connection is being referenced like workflow, session etc. However it also stores connection attribute details like database name, database server name, port number, permissions etc. Since we are interested in fetching connection details we would focus on following columns.
Every connection in Informatica belongs to specific type and subtype. For example type could be Relational and subtype could be Oracle. Informatica stores these details inside tables
OPB_MMD_CNX for connection type and connection subtype respectively.
|CNX_OBJECT_TYPE (Type ID)|
|CNX_OBJECT_SUBTYPE (Subtype ID)|
When you create connection in informatica you specify certain attributes for that connection. For instance apart from username and password FTP connection also has an attributes like hostname or the directory of hostname where it would get connected. These attribute details can be found inside
|OBJECT_TYPE (Type ID)|
|OBJECT_SUBTYPE (Subtype ID)|
|ATTR_VALUE (Default Value)|
Informatica Connection Repository Queries
List of all types of connections that have been created in informatica
SELECT DISTINCT OT.OBJECT_TYPE_NAME FROM OPB_OBJECT_TYPE OT, OPB_MMD_CNX CT WHERE CT.CNX_OBJECT_TYPE=OT.OBJECT_TYPE_ID;
List of all subtypes of connections that have been created in informatica
SELECT DISTINCT ST.CNX_SUBTYPE_NAME, OT.OBJECT_TYPE_NAME FROM OPB_MMD_CNX ST, OPB_OBJECT_TYPE OT WHERE ST.CNX_OBJECT_TYPE=OT.OBJECT_TYPE_ID ORDER BY OT.OBJECT_TYPE_NAME;
List of all relational connections from repository. You can change value 'Relational' to other types such as 'External Loader', 'FTP' etc. (Check first query to list all types)
SELECT C.OBJECT_NAME, OT.OBJECT_TYPE_NAME FROM OPB_CNX C, OPB_OBJECT_TYPE OT WHERE C.OBJECT_TYPE=OT.OBJECT_TYPE_ID AND OT.OBJECT_TYPE_NAME='Relational';
Find all connections which is of specific sub-type. For example list all ODBC connections. However you can change subtype from ODBC to some other desired subtype. (Check second query to list all subtypes)
SELECT C.OBJECT_NAME, ST.CNX_SUBTYPE_NAME FROM OPB_CNX C, OPB_MMD_CNX ST WHERE C.OBJECT_SUBTYPE=ST.CNX_OBJECT_SUBTYPE AND C.OBJECT_TYPE=ST.CNX_OBJECT_TYPE AND ST.CNX_SUBTYPE_NAME='ODBC';
Find list of all configurable attributes of specific sub type of connections. For example list all possible attributes that can be configured with connections of sub type Http Transformation (Check second query to list all subtypes).
SELECT A.ATTR_NAME, A.ATTR_COMMENT FROM OPB_MMD_CNX_ATTR A, OPB_MMD_CNX ST WHERE A.OBJECT_SUBTYPE=ST.CNX_OBJECT_SUBTYPE AND A.OBJECT_TYPE=ST.CNX_OBJECT_TYPE AND ST.CNX_SUBTYPE_NAME='Http Transformation';
List of connections owned by particular database user.
SELECT OBJECT_NAME, USER_NAME FROM OPB_CNX WHERE USER_NAME='DB-USER-NAME';
Note that you may need to limit connections with particular sub type (for example Oracle).
SELECT C.OBJECT_NAME, C.USER_NAME, OT.OBJECT_TYPE_NAME, ST.CNX_SUBTYPE_NAME FROM OPB_CNX C, OPB_OBJECT_TYPE OT, OPB_MMD_CNX ST WHERE C.OBJECT_TYPE=OT.OBJECT_TYPE_ID AND C.OBJECT_SUBTYPE=ST.CNX_OBJECT_SUBTYPE AND C.USER_NAME='DB-USER-NAME' AND ST.CNX_SUBTYPE_NAME='Oracle';
Find list of all connections connecting to the specific connect string (database). Note that different sub type of connections store database name in different attribute. For example below query will give you connections connecting to the specific TNS but to get database names from DSN referred inside
ODBC.ini we would need different query. We will discuss related queries below.
SELECT OBJECT_NAME FROM OPB_CNX WHERE CONNECT_STRING='CON-STRING-NAME';
Get list of all connections connecting to the specific DSN (database) as configured in
SELECT C.OBJECT_NAME, C.USER_NAME, AV.ATTR_VALUE, OT.OBJECT_TYPE_NAME, ST.CNX_SUBTYPE_NAME FROM OPB_CNX C, OPB_OBJECT_TYPE OT, OPB_MMD_CNX ST, OPB_MMD_CNX_ATTR A, OPB_CNX_ATTR AV WHERE C.OBJECT_TYPE=OT.OBJECT_TYPE_ID AND C.OBJECT_SUBTYPE=ST.CNX_OBJECT_SUBTYPE AND AV.OBJECT_ID=C.OBJECT_ID AND A.ATTR_NAME='Data Source Name' AND AV.ATTR_VALUE='DATA-SOURCE-NAME-VALUE';
Get list of all connections pointing to specific database Server or IP.
Connections by IP Address
SELECT DISTINCT C.OBJECT_ID, C.OBJECT_NAME, C.USER_NAME, AV.ATTR_VALUE, OT.OBJECT_TYPE_NAME, ST.CNX_SUBTYPE_NAME FROM OPB_CNX C, OPB_OBJECT_TYPE OT, OPB_MMD_CNX ST, OPB_MMD_CNX_ATTR A, OPB_CNX_ATTR AV WHERE C.OBJECT_TYPE=OT.OBJECT_TYPE_ID AND C.OBJECT_SUBTYPE=ST.CNX_OBJECT_SUBTYPE AND AV.OBJECT_ID=C.OBJECT_ID AND A.ATTR_NAME LIKE '%Server name%' AND AV.ATTR_VALUE='techmonger.github.io';
SELECT DISTINCT C.OBJECT_ID, C.OBJECT_NAME, C.USER_NAME, AV.ATTR_VALUE, OT.OBJECT_TYPE_NAME, ST.CNX_SUBTYPE_NAME FROM OPB_CNX C, OPB_OBJECT_TYPE OT, OPB_MMD_CNX ST, OPB_MMD_CNX_ATTR A, OPB_CNX_ATTR AV WHERE C.OBJECT_TYPE=OT.OBJECT_TYPE_ID AND C.OBJECT_SUBTYPE=ST.CNX_OBJECT_SUBTYPE AND AV.OBJECT_ID=C.OBJECT_ID AND A.ATTR_NAME LIKE '%Server name%' AND AV.ATTR_VALUE='127.0.0.1';
Previously we have discussed about how informatica stores user details inside
We have also learned that details about which user have access over which object can be found inside table
Using this foundation we would be able to answer following queries.
List of connections owned by specific Informatica User.
SELECT C.OBJECT_NAME FROM OPB_CNX C, REP_USERS U, OPB_OBJECT_ACCESS A WHERE C.OBJECT_ID=A.OBJECT_ID AND U.USER_ID=A.USER_ID AND U.USER_NAME='INFORMATICA_USER_NAME';
List of Informatica users who have access over specific informatica connection.
SELECT U.USER_NAME FROM OPB_CNX C, REP_USERS U, OPB_OBJECT_ACCESS A WHERE C.OBJECT_ID=A.OBJECT_ID AND U.USER_ID=A.USER_ID AND C.OBJECT_NAME='CONNECTION-NAME';
Above queries does not take Informatica Groups and Users within those groups into consideration.
Informatica stores connection details inside table
OPB_CNX, connection type and subtype inside
OPB_MMD_CNX and connection attribute details inside