Skip to main content

Query related to connections in informatica

By


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
Labels:

Comments

Post a Comment

Popular posts from this blog

Contact Me

By
Do You have any queries ?                   If you are having any query or wishing to get any type of help related Datawarehouse, OBIEE, OBIA, OAC then please e-email on below. I will reply to your email within 24 hrs. If I didn’t reply to you within 24 Hrs., Please be patience, I must be busy in some work. kashif7222@gmail.com
3 comments
Read more

Top 130 SQL Interview Questions And Answers

By
1. Display the dept information from department table.   Select   *   from   dept; 2. Display the details of all employees   Select * from emp; 3. Display the name and job for all employees    Select ename ,job from emp; 4. Display name and salary for all employees.   Select ename   , sal   from emp;   5. Display employee number and total salary   for each employee. Select empno, sal+comm from emp; 6. Display employee name and annual salary for all employees.   Select empno,empname,12*sal+nvl(comm,0) annualsal from emp; 7. Display the names of all employees who are working in department number 10   Select ename from emp where deptno=10; 8. Display the names of all employees working as   clerks and drawing a salary more than 3000   Select ename from emp where job=’clerk’and sal>3000; 9. Display employee number and names for employees who earn commission   Select empno,ename from emp where comm is not null and comm>0. 10
32 comments
Read more

Informatica sample project

By
Informatica sample project - 1 CareFirst – Blue Cross Blue Shield, Maryland (April 2009 – Current) Senior ETL Developer/Lead Model Office DWH Implementation (April 2009 – Current) CareFirst Blue Cross Blue Shield is one of the leading health care insurance provided in Atlantic region of United States covering Maryland, Delaware and Washington DC. Model Office project was built to create data warehouse for multiple subject areas including Members, Claims, and Revenue etc. The project was to provide data into EDM and to third party vendor (Verisk) to develop cubes based on data provided into EDM. I was responsible for analyzing source systems data, designing and developing ETL mappings. I was also responsible for coordinating testing with analysts and users. Responsibilities: ·          Interacted with Data Modelers and Business Analysts to understand the requirements and the impact of the ETL on the business. ·          Understood the requirement and develope
Post a Comment
Read more