Friday, January 20, 2017

Informatica Metadata Queries - 13 CONNECTIONS




13.1         List Connection Name and Connect Type




SELECT DISTINCT
CONNECTION_TYPE,
CONNECTION_NAME,
CONNECTION_SUBTYPE,
HOST_NAME,
USER_NAME,
SCHEMA_NAME,
ADDRESS
FROM V_PC8X_CONNECTION
ORDER BY 1,2





13.2         List of all Connections




SELECT DISTINCT
(select repository_name from OPB_REPOSIT_INFO) as REPO,
CASE
    when a.OBJECT_SUBTYPE = 0 then 'FTP'
    when a.OBJECT_SUBTYPE = 101 then 'Oracle'
    when a.OBJECT_SUBTYPE = 102 then 'Sybase'
    when a.OBJECT_SUBTYPE = 103 then 'Informix'
    when a.OBJECT_SUBTYPE = 104 then 'Microsoft SQL Server'
    when a.OBJECT_SUBTYPE = 105 then 'DB2'
    when a.OBJECT_SUBTYPE = 106 then 'ODBC'
    when a.OBJECT_SUBTYPE = 107 then 'Teradata'
    when a.OBJECT_SUBTYPE = 100001 then 'Application - SAP BW'
    when a.OBJECT_SUBTYPE = 100101 then 'Application - SAP R3'
    when a.OBJECT_SUBTYPE = 300300 then 'Application - SAP ALE_IDoc_Reader'
    when a.OBJECT_SUBTYPE = 300333 then 'Application - SAP ALE_IDoc_Writer'
    when a.OBJECT_SUBTYPE = 300399 then 'Application - SAP RFC/BAPI Interface'
    when a.OBJECT_SUBTYPE = 304201 then 'Application - Http Transformation'
    when a.OBJECT_SUBTYPE = 300800 then 'Application - JNDI'
    when a.OBJECT_SUBTYPE = 300801 then 'Application - JMS'
    when a.OBJECT_SUBTYPE = 302200 then 'Application - Web Services Consumer'
    when a.OBJECT_SUBTYPE = 304601 then 'Application - PWX Oracle CDC Change'
    when a.OBJECT_SUBTYPE = 305401 then 'Application - Teradata FastExport Connection'
    when a.OBJECT_SUBTYPE = 310600 then 'Application - Salesforce'
    when a.OBJECT_SUBTYPE = 315001 then 'Teradata PT'
    when a.OBJECT_SUBTYPE = 315002 then 'Teradata Dual Load Connection'
    when a.OBJECT_SUBTYPE = 315003 then 'Teradata Dual Load ODBC Connection'
    when a.OBJECT_SUBTYPE = 401000 then 'Essbase Connection'
    when a.OBJECT_SUBTYPE = 404000 then 'Lotus Notes'
    when a.OBJECT_SUBTYPE = 445805 then 'Hadoop HDFS Connection'
else 'NULL'
end as cnx_type,
a.object_name as connection_name,
a.user_name as conn_user_name,
a.CONNECT_STRING as CONNECT_STRING,

case when b.attr_id = 10 then 'Database name'
        when b.attr_id = 11 then 'Data Source Name'
        when b.attr_id = 12 then 'Connection Environment SQL'
        when b.attr_id = 13 then 'Transaction Environment SQL'
        when b.attr_id = 14 then 'Connection Retry Period'
 end as CNX_VALUE,

case when b.ATTR_ID = 1 then 'TPTID'
        when b.ATTR_ID = 2 then 'DatabaseName'
        when b.ATTR_ID = 3 then 'Tenacity'
        when b.ATTR_ID = 4 then 'MaxSessions'
        when b.ATTR_ID = 15 then 'MinSessions'
        when b.ATTR_ID = 5 then 'Sleep'
         when b.ATTR_ID = 6 then 'System Operator'
        when b.ATTR_ID = 7 then 'BlockSize'
        when b.ATTR_ID = 8 then 'Data Encryption'
end as CONNECTION_Attribute,
b.ATTR_VALUE,
a.LAST_SAVED,
       case when a.CONNECT_STRING like '%prd%' then 'PRD CNX'
        when a.CONNECT_STRING like '%PRD%' then 'PRD CNX'
        when a.CONNECT_STRING like '%tst%' then 'TST CNX'
        when a.CONNECT_STRING like '%TST%' then 'TST CNX'
        when a.CONNECT_STRING like '%dev%' then 'DEV CNX'
        when a.CONNECT_STRING like '%DEV%' then 'DEV CNX' 
end as CONNECTING_TO
from opb_cnx a,
OPB_CNX_ATTR b
where
a.OBJECT_ID = b.OBJECT_ID
order by  1,2,3,4




13.3         List of cnxs using alter in env sql


SELECT DISTINCT
D.OBJECT_NAME,
C.DB, C.USERNAME,
C.ATTR_VALUE AS ENVIRONMENT_SQL
FROM OPB_CNX D,
    (SELECT DISTINCT A.OBJECT_NAME CONN, B.OBJECT_ID,
                     A.CONNECT_STRING DB, A.USER_NAME USERNAME,
                     B.ATTR_VALUE FROM OPB_CNX A,
            (SELECT B.OBJECT_ID, B.OBJECT_SUBTYPE,
                    B.OBJECT_TYPE, B.ATTR_VALUE
                    FROM OPB_CNX_ATTR B
                    WHERE B.ATTR_ID = 11) B
      WHERE A.OBJECT_ID = B.OBJECT_ID
   AND B.ATTR_VALUE IS NOT NULL) C
 WHERE D.OBJECT_NAME = C.CONN
 ORDER BY 1

13.4         List of cnxs used in session levels


SELECT DISTINCT
A.SUBJECT_AREA,
A.TASK_NAME,
B.READER_WRITER_TYPE,
B.CNX_NAME
FROM
REP_ALL_TASKS A,
REP_SESS_WIDGET_CNXS B
WHERE
A.TASK_ID = B.SESSION_ID
ORDER BY 1,2,3


13.5         List Lotus connection details


SELECT DISTINCT
a.object_name as connection_name,
case when a.OBJECT_SUBTYPE = 101 then 'Oracle'
when a.OBJECT_SUBTYPE = 104 then 'Microsoft SQL Server'
when a.OBJECT_SUBTYPE = 106 then 'ODBC'
when a.OBJECT_SUBTYPE = 0 then 'FTP'
when a.OBJECT_SUBTYPE = 404000 then 'Lotus Notes' end as type,
a.user_name as conn_user_name,
e.ServerHost,
e.DatabaseFilename,
b.user_name connection_owner,
case when user_type = '1' and d.user_id in (select id from opb_user_group y where y.type = 1)   then (select 'User - '||x.user_name from opb_users x where d.user_id=x.user_id)
when user_type = '2' and d.user_id in (select id from opb_user_group y where y.type = 2) then (select 'Group - '||z.name from opb_user_group z where d.user_id=z.id and z.type = 2)
when user_type = '3' and d.user_id in (select id from opb_user_group) then (select name from opb_user_group )
when d.user_id = '0' then 'Others'
end as CONN_USERS_LIST,
CASE   WHEN user_type = 1 THEN
CASE WHEN permissions = d.user_id + 15 THEN 'RWX'
WHEN permissions = d.user_id + 13 THEN 'RW'
WHEN permissions = d.user_id + 11 THEN 'RX'
WHEN permissions = d.user_id + 9  THEN 'R'
WHEN permissions = d.user_id + 7  THEN 'WX'
WHEN permissions = d.user_id + 5  THEN 'W'
WHEN permissions = d.user_id + 3  THEN 'X'
ELSE 'NULL' END
WHEN user_type = 2 THEN
CASE WHEN permissions = d.user_id + 29  THEN 'RWX'
WHEN permissions = d.user_id + 25  THEN 'RW'
WHEN permissions = d.user_id + 21  THEN 'RX'
WHEN permissions = d.user_id + 17  THEN 'R'
WHEN permissions = d.user_id + 13  THEN 'WX'
WHEN permissions = d.user_id + 9   THEN 'W'
WHEN permissions = d.user_id + 5   THEN 'X'
ELSE 'NULL'
END
WHEN user_type = 3
THEN CASE
WHEN permissions = d.user_id + 57 THEN 'RWX'
WHEN permissions = d.user_id + 39 THEN 'RW'
WHEN permissions = d.user_id + 41 THEN 'RX'
WHEN permissions = d.user_id + 33 THEN 'R'
WHEN permissions = d.user_id + 25 THEN 'WX'
WHEN permissions = d.user_id + 17 THEN 'W'
WHEN permissions = d.user_id + 9  THEN 'X'
ELSE 'NULL'
END
END PREVILIGES
FROM opb_cnx a, opb_users b, opb_groups c, opb_object_access d,
( Select  a.object_id,a.ServerHost,b.DatabaseFilename  from
(select object_id,Attr_value as ServerHost  from opb_cnx_attr where OBJECT_SUBTYPE  = 404000 and attr_id = 1 ) a,
(select object_id,Attr_value as DatabaseFilename  from opb_cnx_attr where OBJECT_SUBTYPE = 404000 and attr_id = 2 ) b
where a.object_id = b.object_id) e
WHERE a.owner_id = b.user_id
AND a.object_id = d.object_id
AND d.object_id = e.object_id
and a.OBJECT_SUBTYPE = 404000
ORDER BY object_name

13.6         ODBC / SQL Server Connection details


SELECT DISTINCT
a.object_name as connection_name,
a.user_name as conn_user_name,
--   a.connect_string,
b.user_name connection_owner,
--  b.user_desc AS conn_owner_desc,
case when user_type = '1' and d.user_id in (select user_id from opb_users) then (select 'User - '||x.user_name from opb_users x where d.user_id=x.user_id)
when user_type = '2' and d.user_id in (select group_id from opb_groups) then (select 'Group - '||x.group_name from opb_groups x where d.user_id=x.group_id)
when d.user_id = '0' then 'World' end as CONN_USERS_LIST,

       CASE                                
                WHEN user_type = 1 THEN
                   CASE WHEN permissions = d.user_id + 15 THEN 'RWX'                                            WHEN permissions = d.user_id + 13 THEN 'RW'                                               
        WHEN permissions = d.user_id + 11 THEN 'RX'
        WHEN permissions = d.user_id + 9  THEN 'R'
        WHEN permissions = d.user_id + 7  THEN 'WX'
        WHEN permissions = d.user_id + 5  THEN 'W'
        WHEN permissions = d.user_id + 3  THEN 'X'
                ELSE 'NULL'
          END
               WHEN user_type = 2 THEN
    CASE
WHEN permissions = d.user_id + 29  THEN 'RWX'
WHEN permissions = d.user_id + 25  THEN 'RW'
WHEN permissions = d.user_id + 21  THEN 'RX'
WHEN permissions = d.user_id + 17  THEN 'R'
WHEN permissions = d.user_id + 13  THEN 'WX'
WHEN permissions = d.user_id + 9   THEN 'W'
WHEN permissions = d.user_id + 5   THEN 'X'
ELSE 'NULL'
          END
                   WHEN user_type = 3
                      THEN CASE
                             WHEN permissions = d.user_id + 57 THEN 'RWX'
                             WHEN permissions = d.user_id + 39 THEN 'RW'
                             WHEN permissions = d.user_id + 41 THEN 'RX'
                             WHEN permissions = d.user_id + 33 THEN 'R'        
                             WHEN permissions = d.user_id + 25 THEN 'WX'    
                             WHEN permissions = d.user_id + 17 THEN 'W'      
                             WHEN permissions = d.user_id + 9  THEN 'X'         
                             ELSE 'NULL'               
                          END              
                END PREVILIGES    
                 FROM opb_cnx a, opb_users b, opb_groups c, opb_object_access
          WHERE a.owner_id = b.user_id  
            AND a.GROUP_ID = c.group_id
            AND a.object_id = d.object_id and d.object_type=73               
                  and a.object_subtype = 106
                ORDER BY object_name

13.7         List of sessions used by a connection


SELECT DISTINCT C.SUBJECT_AREA, B.WORKFLOW_NAME,A.SESSION_INSTANCE_NAME, CONNECTION_NAME, CONNECT_STRING
FROM REP_SESSION_CNXS C , OPB_CNX,REP_SESS_LOG A,REP_WFLOW_RUN B 
WHERE C.CONNECTION_ID=OPB_CNX.OBJECT_ID
AND C.SESSION_ID = A.SESSION_ID
AND A.WORKFLOW_ID=B.WORKFLOW_ID
AND CONNECTION_NAME IN
('CMX_NAME') ORDER BY 1,2

select * from rep_session_cnxs where connection_name  in ('Source_aaaa','Target_bbbb','Source_ccccc')
order by 1

13.8         Query to fetch connection details users list and privileges’


SELECT DISTINCT                                   
                   a.object_name as connection_name,                                
                   a.user_name as conn_user_name,                    
                   a.connect_string,                
                   b.user_name connection_owner,                       
                   b.user_desc AS conn_owner_desc,                    
                   case when user_type = '1' and d.user_id in (select user_id from opb_users) then (select 'User - '||x.user_name from opb_users x where d.user_id=x.user_id)
                        when user_type = '2' and d.user_id in (select group_id from opb_groups) then (select 'Group - '||x.group_name from opb_groups x where d.user_id=x.group_id)
                                                when d.user_id = '0' then 'World' end as CONN_USERS_LIST,
--                d.user_type CONN_USER_TYPE,                        
                --   d.permissions,                  
       CASE                                
                WHEN user_type = 1                                             
                      THEN CASE                                      
                             WHEN permissions = d.user_id + 15  THEN 'RWX'                                                      WHEN permissions = d.user_id + 13  THEN 'RW'                                                                  WHEN permissions = d.user_id + 11  THEN 'RX'                                                                   WHEN permissions = d.user_id + 9    THEN 'R'                                                         WHEN permissions = d.user_id + 7    THEN 'WX'                                                                 WHEN permissions = d.user_id + 5    THEN 'W'
                             WHEN permissions = d.user_id + 3     THEN 'X'
                             ELSE 'NULL'
                          END
                   WHEN user_type = 2
                      THEN CASE
                             WHEN permissions = d.user_id + 29  THEN 'RWX'
                             WHEN permissions = d.user_id + 25  THEN 'RW'
                             WHEN permissions = d.user_id + 21  THEN 'RX'
                             WHEN permissions = d.user_id + 17  THEN 'R'
                             WHEN permissions = d.user_id + 13  THEN 'WX'
                             WHEN permissions = d.user_id + 9    THEN 'W'
                             WHEN permissions = d.user_id + 5    THEN 'X'
                             ELSE 'NULL'
                          END
                   WHEN user_type = 3
                      THEN CASE
                             WHEN permissions = d.user_id + 57   THEN 'RWX'
                             WHEN permissions = d.user_id + 39   THEN 'RW'
                             WHEN permissions = d.user_id + 41   THEN 'RX'
                             WHEN permissions = d.user_id + 33   THEN 'R'      
                             WHEN permissions = d.user_id + 25   THEN 'WX'  
                             WHEN permissions = d.user_id + 17    THEN 'W'   
                             WHEN permissions = d.user_id + 9       THEN 'X'    
                             ELSE 'NULL'               
                          END              
                END PREVILIGES    
         FROM opb_cnx a, opb_users b, opb_groups c, opb_object_access d         
          WHERE a.owner_id = b.user_id  
            AND a.GROUP_ID = c.group_id
            AND a.object_id = d.object_id and d.object_type=73               
                ORDER BY 1

13.9         Connections with Attributes


SELECT
  OPB_CNX.OBJECT_NAME "CONNECTION NAME"
, OPB_CNX.USER_NAME
, OPB_CNX_ATTR.ATTR_ID
, OPB_CNX_ATTR.ATTR_VALUE
FROM
  OPB_CNX
, OPB_CNX_ATTR

WHERE
    OPB_CNX.OBJECT_ID = OPB_CNX_ATTR.OBJECT_ID
AND OPB_CNX.USER_NAME Like '%rac%'
AND (OPB_CNX_ATTR.ATTR_ID=1 Or OPB_CNX_ATTR.ATTR_ID=2)
AND OPB_CNX_ATTR.ATTR_VALUE Is Not Null

ORDER BY 1, 4 DESC;



13.10      List of Source and Target used in Session Level

 SELECT DISTINCT
       ALL_SESSIONS.SUBJECT_AREA FOLDER_NAME,
       ALL_SESSIONS.MAPPING_NAME MAPPING_NAME,
       ALL_SESSIONS.SESSION_NAME SESSION_NAME,
       SESSION_ALL_CNXS.READER_WRITER_TYPE,
       SESSION_ALL_CNXS.INSTANCE_NAME,
       SESSION_ALL_CNXS.CNX_NAME,
       CASE
          WHEN SESSION_ALL_CNXS.WIDGET_TYPE = 2
          THEN
             'TARGET CONNECTION'
          ELSE
             CASE
                WHEN SESSION_ALL_CNXS.WIDGET_TYPE IN (1, 3, 56, 45, 55, 84)
                THEN
                   'SOURCE CONNECTION'
                ELSE
                   NULL
             END
       END,
       SESSION_USERS.USER_NAME
  FROM REP_VERSION_PROPS SESSION_VERSION_PROPS,
       REP_USERS SESSION_USERS,
       REP_LOAD_SESSIONS ALL_SESSIONS,
       REP_REPOSIT_INFO SESSION_REPOSIT_INFO,
       REP_SESS_WIDGET_CNXS SESSION_ALL_CNXS
 WHERE (    SESSION_VERSION_PROPS.USER_ID = SESSION_USERS.USER_ID
        AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID
        AND ALL_SESSIONS.SESSION_ID = SESSION_VERSION_PROPS.OBJECT_ID
        AND ALL_SESSIONS.SUBJECT_ID = SESSION_VERSION_PROPS.SUBJECT_ID
        AND SESSION_VERSION_PROPS.OBJECT_TYPE = 68
        AND ALL_SESSIONS.SESSION_ID = SESSION_ALL_CNXS.SESSION_ID
        AND ALL_SESSIONS.SESSION_VERSION_NUMBER =
               SESSION_ALL_CNXS.SESSION_VERSION_NUMBER)
--       AND ALL_SESSIONS.SUBJECT_AREA = 'CDM'


13.11      List of connection names with Attribute details



SELECT DISTINCT
(select repository_name from OPB_REPOSIT_INFO) as REPO,
CASE
    when a.OBJECT_SUBTYPE = 0 then 'FTP'
    when a.OBJECT_SUBTYPE = 101 then 'Oracle'
    when a.OBJECT_SUBTYPE = 102 then 'Sybase'
    when a.OBJECT_SUBTYPE = 103 then 'Informix'
    when a.OBJECT_SUBTYPE = 104 then 'Microsoft SQL Server'
    when a.OBJECT_SUBTYPE = 105 then 'DB2'
    when a.OBJECT_SUBTYPE = 106 then 'ODBC'
    when a.OBJECT_SUBTYPE = 107 then 'Teradata'
    when a.OBJECT_SUBTYPE = 100001 then 'Application - SAP BW'
    when a.OBJECT_SUBTYPE = 100101 then 'Application - SAP R3'
    when a.OBJECT_SUBTYPE = 300300 then 'Application - SAP ALE_IDoc_Reader'
    when a.OBJECT_SUBTYPE = 300333 then 'Application - SAP ALE_IDoc_Writer'
    when a.OBJECT_SUBTYPE = 300399 then 'Application - SAP RFC/BAPI Interface'
    when a.OBJECT_SUBTYPE = 304201 then 'Application - Http Transformation'
    when a.OBJECT_SUBTYPE = 300800 then 'Application - JNDI'
    when a.OBJECT_SUBTYPE = 300801 then 'Application - JMS'
    when a.OBJECT_SUBTYPE = 302200 then 'Application - Web Services Consumer'
    when a.OBJECT_SUBTYPE = 304601 then 'Application - PWX Oracle CDC Change'
    when a.OBJECT_SUBTYPE = 305401 then 'Application - Teradata FastExport Connection'
    when a.OBJECT_SUBTYPE = 310600 then 'Application - Salesforce'
    when a.OBJECT_SUBTYPE = 315001 then 'Teradata PT'
    when a.OBJECT_SUBTYPE = 315002 then 'Teradata Dual Load Connection'
    when a.OBJECT_SUBTYPE = 315003 then 'Teradata Dual Load ODBC Connection'
    when a.OBJECT_SUBTYPE = 401000 then 'Essbase Connection'
    when a.OBJECT_SUBTYPE = 404000 then 'Lotus Notes'
    when a.OBJECT_SUBTYPE = 445805 then 'Hadoop HDFS Connection'
else 'NULL'
end as cnx_type,
a.object_name as connection_name,
a.user_name as conn_user_name,
a.CONNECT_STRING as CONNECT_STRING,

case when b.attr_id = 10 then 'Database name'
        when b.attr_id = 11 then 'Data Source Name'
        when b.attr_id = 12 then 'Connection Environment SQL'
        when b.attr_id = 13 then 'Transaction Environment SQL'
        when b.attr_id = 14 then 'Connection Retry Period'
 end as CNX_VALUE,
b.ATTR_VALUE,
a.LAST_SAVED,
case when a.CONNECT_STRING like '%pdbx%' then 'PRD CNX'
        when a.CONNECT_STRING like '%PDBX%' then 'PRD CNX'
        when a.CONNECT_STRING like '%tdbx%' then 'TST CNX'
        when a.CONNECT_STRING like '%TDBX%' then 'TST CNX'
        when a.CONNECT_STRING like '%ddbx%' then 'DEV CNX'
        when a.CONNECT_STRING like '%DDBX%' then 'DEV CNX'
end as CONNECTING_TO

from opb_cnx a,
OPB_CNX_ATTR b
where
a.OBJECT_ID = b.OBJECT_ID
order by  1,2,3,4

13.12      Query used for connection cleanup


SELECT DISTINCT
(select repository_name from OPB_REPOSIT_INFO) as REPO,
C.SUBJECT_AREA,
A.WORKFLOW_NAME,
A.SESSION_INSTANCE_NAME SESSION_NAME,D.READER_WRITER_TYPE,CONNECTION_NAME,
A.ACTUAL_START AS LAST_RUN_DATE
FROM REP_SESSION_CNXS C ,OPB_CNX,REP_SESS_LOG A,REP_WFLOW_RUN B ,REP_SESS_WIDGET_CNXS D
WHERE C.CONNECTION_ID=OPB_CNX.OBJECT_ID
AND C.SESSION_ID = A.SESSION_ID
AND c.session_id= d.session_id
AND A.WORKFLOW_ID=B.WORKFLOW_ID
--AND CONNECTION_NAME in ('cnx)
ORDER BY 1,2

13.13      List sessions last run by using a connection


SELECT DISTINCT
(select repository_name from OPB_REPOSIT_INFO) as REPO,
C.SUBJECT_AREA,
A.WORKFLOW_NAME,
A.SESSION_INSTANCE_NAME SESSION_NAME,
--D.READER_WRITER_TYPE,
CONNECTION_NAME,
TRUNC(A.ACTUAL_START) AS LAST_RUN_DATE
FROM REP_SESSION_CNXS C ,OPB_CNX,REP_SESS_LOG A,REP_WFLOW_RUN B ,REP_SESS_WIDGET_CNXS D
WHERE C.CONNECTION_ID=OPB_CNX.OBJECT_ID
AND C.SESSION_ID = A.SESSION_ID
AND c.session_id= d.session_id
AND A.WORKFLOW_ID=B.WORKFLOW_ID
AND CONNECTION_NAME in
(

'_GCOS',
'ABC',
'ADM_ODS_Src',
'AMSDM'
)
AND TRUNC(A.ACTUAL_START) =  (Select MAX(TRUNC(AA.ACTUAL_START)) from REP_SESS_LOG AA
where AA.SESSION_INSTANCE_NAME = A.SESSION_INSTANCE_NAME
group by A.SESSION_INSTANCE_NAME )
ORDER BY 1,2

SELECT DISTINCT
(select repository_name from OPB_REPOSIT_INFO) as REPO,
B.SUBJECT_AREA,
D.task_name as workflow_name,
B.SESSION_NAME,
B.CONNECTION_NAME,
C.last_run
FROM OPB_CNX A, REP_SESSION_CNXS B,
(
select DISTINCT
TASK_ID,
INSTANCE_NAME,
workflow_id,
MAX(START_TIME) last_run
from OPB_TASK_INST_RUN
where task_type =68
--and  task_ID = 25990
GROUP BY
TASK_ID,
INSTANCE_NAME,
workflow_id
) C
,
(select task_id,task_name from opb_task where task_type =71) D
WHERE A.OBJECT_ID = B.CONNECTION_ID
AND b.SESSION_ID = C.TASK_ID
AND C.Workflow_id=D.TASK_ID
--AND  C.task_type in('68','71')
--AND  C.task_ID = 25990
AND B.CONNECTION_NAME IN
('KAM_DM')

13.14      How to update permissions of mass connections


Incase if we have to update permission for multiple connections please use the below pmrep command, Adjust the parameters as needed and make it as a script under $INFA_HOME/server/bin.

AssignPermission -o [-t ] -n {-u | -g } [-s ] -p

Example:

pmrep connect -r Repository_EDW9Prd -d Domain_Dev -n Admin -s LDAP -x xxxx

pmrep assignPermission -o Connection -t Relational -n ADM_ODS_Src_d -g Informatica_TD_Upgrade -p RWX  (use updatepermission for modifying the currently assigned permission)

3 comments: