Tech Monger

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_CNX, OPB_CNX_ATTR, OPB_OBJECT_TYPE, OPB_MMD_CNX and OPB_MMD_CNX_ATTR. Below are the some of the important columns from these tables.

OPB_CNX

OBJECT_ID

OBJECT_NAME

OBJECT_TYPE

OBJECT_SUBTYPE

USER_NAME

CONNECT_STRING

CODE_PAGE

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_OBJECT_TYPE and OPB_MMD_CNX for connection type and connection subtype respectively.

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 OPB_MMD_CNX_ATTR.

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 odbc.ini.

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.

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';

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='127.0.0.1';

Previously we have discussed about how informatica stores user details inside OPB_USERS and REP_USERS.
We have also learned that details about which user have access over which object can be found inside table OPB_OBJECT_ACCESS.
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.

Tagged Under : Informatica Informatica Queries