Oracle Business Intelligence Application Architect
☎ +91 9994883085
Don't miss

Sunday, 6 September 2015

Query related to connections in informatica


By on 02:26:00


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

0 comments:

Post a Comment

Labels

1z0-482 Dumps (2) 1Z0-525 Dumps (1) BI Apps Installation (1) BI Publisher Interview Questions (1) BICS (2) Business Intelligence (1) DATA WAREHOUSE ADMINISTRATOR CONSOLE (18) Data Warehousing (48) E-BIZ R12 (1) E-BIZ R12 INSTALLATION (1) Essbase (1) hmailserver (1) Hyperion (1) Hyperion Essbase (2) Hyperion Essbase Interview Questions (1) Hyperion financial management (1) Informatica (163) Informatica Installation 9.6.1 (2) Informatica Interview Question (46) Informatica Online Training (1) Informatica Training (1) Informatica Training Chennai (1) JAVA (1) linux (1) Normalization in Oracle (1) OBIA 11.1.1.7.1 (1) OBIA 7.9.6.3 (3) OBIA Installation (33) OBIEE (1) OBIEE 10G (8) OBIEE 11G (137) OBIEE 11g dumps (2) OBIEE 11g Interview Questions (1) OBIEE 12c (20) OBIEE 12c Architecture (1) OBIEE 12C TRAINING (1) OBIEE certification (2) OBIEE Interview Question (1) OBIEE Publisher Interview Questions (1) OBIEE TRAINING CHENNAI (1) OBIEE TRAINING ONLINE (1) ODI (Oracle data integrator) (2) odi 11g certification (1) ODI dumps (2) Oracle Administrator (20) Oracle Business Intelligence Application (3) Oracle Data Integrator (2) Oracle Data Integrator Interview questions for experience (1) Oracle Database (3) Oracle Database 12c Installation steps (3) Oracle Database Installation in Linux 6 (2) Oracle E-Business R 12.3 (1) Oracle Hyperion Planning and Budgeting Questionnaire (2) oracle Joins (1) RCU INSTALLATION (1) scorecard in obiee 11g (1) SQL (3) SQL Interview Questions (1) sql server 2008 (2) Sql server installation (1) WinSCP (1)