✍ Kashif M
Trends

Sunday, 6 September 2015


Query related to connections in informatica


By on September 06, 2015


List of connection 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

 List of connection used in session levels

 SELECT DISTINCT
A.SUBJECT_AREA,
A.TASK_NAME,
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
 

  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

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 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           
                                            and a.object_subtype = 106
            ORDER BY object_name

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

 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

Kashif
mkashu: Query related to connections in informatica
Review : Kashif | Kashif
Update: September 06, 2015 | Rating: 4.5

Comment for "Query related to connections in informatica"

0 comments

Post a Comment

Blog Archive