Friday, April 13, 2012

INFORMATICA REPOSITORY QUERIES - PART II

TABLE OF CONTENTS


INFORMATICA REPOSITORY QUERIES - PART I

 

11           WORKFLOW

11.1           List workflow names

 SELECT   SUBJECT_AREA, TASK_TYPE_NAME, TASK_NAME
    FROM REP_ALL_TASKS
   WHERE TASK_TYPE IN (71) 
         --AND SUBJECT_AREA= 'ABC'
ORDER BY 1, 2, 3
  
11.2           List save workflow log count

SELECT DISTINCT
A.SUBJECT_AREA,
A.TASK_NAME AS WORKFLOW_NAME,
B.ATTR_VALUE AS SAVEWFLOG
FROM
REP_ALL_TASKS A ,
REP_TASK_ATTR  B
WHERE
A.TASK_ID = B.TASK_ID
AND B.ATTR_ID = '4'
AND B.TASK_TYPE = 71
--AND B.ATTR_VALUE NOT IN (8,4) 
-- AND A.SUBJECT_AREA = 'ABC'
ORDER BY 1,2,3

11.3          List workflow log names

 SELECT DISTINCT
      SUBJ_NAME,
      WORKFLOW_NAME,
      SUBSTR(LOG_FILE,23,300) AS EXISTING_WFLOGNAME
FROM
      OPB_WFLOW_RUN,
      OPB_SUBJECT
WHERE
       OPB_WFLOW_RUN.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID
       AND SUBSTR(LOG_FILE,23,300) != CONCAT(LOWER(WORKFLOW_NAME),'.log')
ORDER BY 1,2

11.4           List write backward compatible check

 SELECT DISTINCT
A.SUBJECT_AREA,A.TASK_NAME,
DECODE (B.ATTR_VALUE,0,'TO BE CHECKED',1,'CHECKED') WRITEBACKWARDCOMPATIBLE
FROM
REP_ALL_TASKS A,
OPB_TASK_ATTR B
WHERE
A.TASK_ID = B.TASK_ID
AND B.TASK_TYPE IN 71
AND B.ATTR_ID = 12
AND B.ATTR_VALUE <> 1

11.5           List fail_parent_if_task_fails objects

 SELECT
   REPOSITORY,
   FOLDER_NAME,
   WORKFLOW_OR_WORKLET,
   TASK_TYPE,
   WORKLET_OR_SESSION,
   FAIL_PARENT_IF_TASK_FAILS
FROM
(SELECT DISTINCT   
OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,
OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME,
OPB_TASK.TASK_NAME AS WORKFLOW_OR_WORKLET,
DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL',NULL) TASK_TYPE,
OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION,
DECODE (BITAND (OPB_TASK_INST.BIT_OPTIONS, 17),17,'SELECTED','NOT SELECTED') AS FAIL_PARENT_IF_TASK_FAILS
FROM OPB_TASK_INST,OPB_OBJECT_TYPE,OPB_TASK,
   OPB_SUBJECT, OPB_REPOSIT_INFO
WHERE OPB_TASK_INST.TASK_TYPE != 62
AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID
AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER
AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_TASK.UTC_CHECKIN <> 0
--AND OPB_SUBJECT.SUBJ_NAME NOT LIKE 'WA%'
 )
WHERE FAIL_PARENT_IF_TASK_FAILS <> 'SELECTED'

11.6           List fail_parent_if_task_dont_run objects


SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,WORKLET_OR_SESSION,FAIL_PARENT_IF_TASK_DONT_RUN
FROM
            (SELECT DISTINCT         
         OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,
OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME, OPB_TASK.TASK_NAME AS WORKFLOW_OR_WORKLET,
DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL',NULL) TASK_TYPE, OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION, DECODE (BITAND (OPB_TASK_INST.BIT_OPTIONS, 49),49,'SELECTED','NOT SELECTED') AS FAIL_PARENT_IF_TASK_DONT_RUN
            FROM OPB_TASK_INST, OPB_OBJECT_TYPE,OPB_TASK,OPB_SUBJECT,OPB_REPOSIT_INFO
            WHERE OPB_TASK_INST.TASK_TYPE != 62
       AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
       AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID
       AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER
       AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID
       AND OPB_TASK.UTC_CHECKIN <> 0  )
WHERE FAIL_PARENT_IF_TASK_DONT_RUN <> 'SELECTED'
ORDER BY 2

11.7           List is_task_enabled objects


SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,WORKLET_OR_SESSION,IS_TASK_ENABLED
FROM
            (SELECT DISTINCT         
          OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,
OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME, OPB_TASK.TASK_NAME AS WORKFLOW_OR_WORKLET,
DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL', NULL) TASK_TYPE, OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION,
          DECODE (OPB_TASK_INST.IS_ENABLED,1, 'ENABLED','DISABLED') AS IS_TASK_ENABLED
            FROM OPB_TASK_INST,OPB_OBJECT_TYPE,OPB_TASK,OPB_SUBJECT,OPB_REPOSIT_INFO
            WHERE OPB_TASK_INST.TASK_TYPE != 62
                     AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
                    AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID
                    AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER
                   AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID
                  AND OPB_TASK.UTC_CHECKIN <> 0 )
WHERE IS_TASK_ENABLED = 'DISABLED'
ORDER BY 2,3

11.8           List treat_input_links_as objects


SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,
WORKLET_OR_SESSION,TREAT_INPUT_LINKS_AS
FROM
(SELECT DISTINCT         
OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,
OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME,
OPB_TASK.TASK_NAME AS WORKFLOW_OR_WORKLET,
DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL',NULL) TASK_TYPE,
OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION,
DECODE (BITAND (OPB_TASK_INST.BIT_OPTIONS, 3),1,'AND',2, 'OR') AS TREAT_INPUT_LINKS_AS
FROM OPB_TASK_INST,OPB_OBJECT_TYPE,OPB_TASK,OPB_SUBJECT,OPB_REPOSIT_INFO WHERE OPB_TASK_INST.TASK_TYPE != 62
AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID
AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER
AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_TASK.UTC_CHECKIN <> 0
)
--WHERE TREAT_INPUT_LINKS_AS = 'OR'
ORDER BY 2,3

11.9           List all workflows whose server is not assigned


SELECT SUBJECT_AREA,WORKFLOW_NAME,SERVER_NAME
FROM REP_WORKFLOWS
WHERE SERVER_NAME IS NULL

11.10        List of workflow run details


SELECT DISTINCT OPB_SUBJECT.SUBJ_NAME,OPB_WFLOW_RUN.WORKFLOW_NAME, 
OPB_TASK_INST_RUN.INSTANCE_NAME,
TRUNC(OPB_TASK_INST_RUN.START_TIME) LOAD_DATE,
TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24:MI.SS') SESS_START_TIME,
TO_CHAR(OPB_TASK_INST_RUN.END_TIME,'HH24:MI.SS') SESS_END_TIME,
TRUNC((OPB_TASK_INST_RUN.END_TIME - OPB_TASK_INST_RUN.START_TIME) * 1440,2) DURATION_IN_MINS
FROM OPB_SUBJECT,OPB_TASK_INST_RUN,OPB_WFLOW_RUN,OPB_OBJECT_TYPE, OPB_SESS_TASK_LOG   
WHERE
  
  OPB_SUBJECT.SUBJ_ID = OPB_TASK_INST_RUN.SUBJECT_ID
     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_TASK_INST_RUN.WORKFLOW_ID
     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_TASK_INST_RUN.WORKFLOW_RUN_ID
     AND OPB_TASK_INST_RUN.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
     AND OPB_OBJECT_TYPE.OBJECT_TYPE = 68
     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_SESS_TASK_LOG.WORKFLOW_ID
     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID
     AND OPB_TASK_INST_RUN.WORKLET_RUN_ID = OPB_SESS_TASK_LOG.WORKLET_RUN_ID
     AND OPB_TASK_INST_RUN.INSTANCE_ID = OPB_SESS_TASK_LOG.INSTANCE_ID
ORDER BY 1,2,4


SELECT DISTINCT OPB_SUBJECT.SUBJ_NAME,OPB_WFLOW_RUN.WORKFLOW_NAME, 
OPB_TASK_INST_RUN.INSTANCE_NAME,
OPB_TASK_INST_RUN.START_TIME LOAD_DATE,
TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24') HOUR,
OPB_SESS_TASK_LOG.SRC_SUCCESS_ROWS,
OPB_SESS_TASK_LOG.SRC_FAILED_ROWS,
OPB_SESS_TASK_LOG.TARG_SUCCESS_ROWS,
OPB_SESS_TASK_LOG.TARG_FAILED_ROWS,
TO_CHAR (OPB_TASK_INST_RUN.START_TIME, 'Day') weekday,
TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24:MI.SS') SESS_START_TIME,
TO_CHAR(OPB_TASK_INST_RUN.END_TIME,'HH24:MI.SS') SESS_END_TIME,
TRUNC((OPB_TASK_INST_RUN.END_TIME - OPB_TASK_INST_RUN.START_TIME) * 1440,2) DURATION_IN_MINS
FROM OPB_SUBJECT,OPB_TASK_INST_RUN,OPB_WFLOW_RUN,OPB_OBJECT_TYPE, OPB_SESS_TASK_LOG   
WHERE
  OPB_SUBJECT.SUBJ_ID = OPB_TASK_INST_RUN.SUBJECT_ID
     --AND  OPB_SUBJECT.SUBJ_NAME LIKE '%ICOCRM_%'
     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_TASK_INST_RUN.WORKFLOW_ID
     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_TASK_INST_RUN.WORKFLOW_RUN_ID
     AND OPB_TASK_INST_RUN.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
     AND OPB_OBJECT_TYPE.OBJECT_TYPE = 68
     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_SESS_TASK_LOG.WORKFLOW_ID
     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID
     AND OPB_TASK_INST_RUN.WORKLET_RUN_ID = OPB_SESS_TASK_LOG.WORKLET_RUN_ID
     AND OPB_TASK_INST_RUN.INSTANCE_ID = OPB_SESS_TASK_LOG.INSTANCE_ID
     --AND TRUNC(OPB_TASK_INST_RUN.START_TIME) > TRUNC(SYSDATE -2)
     and OPB_TASK_INST_RUN.INSTANCE_NAME in    (
      )

ORDER BY 4,1,2,3

 

SELECT DISTINCT subject_area,  workflow_name, server_name,
                start_time, end_time, TO_CHAR (start_time, 'Day') weekday,
                TRUNC (TO_CHAR ((end_time - start_time) * 1440)
                      ) elapsedminutes,
                CASE
                   WHEN run_status_code = '1'  THEN 'Succeeded'
                   WHEN run_status_code = '2'  THEN 'Disabled'
                   WHEN run_status_code = '3'  THEN 'Failed'
                   WHEN run_status_code = '4'   THEN 'Stopped'
                   WHEN run_status_code = '5'   THEN 'Aborted'
                   WHEN run_status_code = '6'   THEN 'Running'
                   WHEN run_status_code = '15'  THEN 'Terminated'
                   else 'dummy'
                END AS load_status
           FROM rep_wflow_run
          --WHERE run_status_code not in ( '1')
          where
           TRUNC(start_time) > TRUNC(SYSDATE -8)
         --   TRUNC(start_time) BETWEEN TRUNC(SYSDATE -7) AND TRUNC(SYSDATE)
       ORDER BY 5

11.11           List of sessions last run details

 

SELECT DISTINCT
(Select reposit_name from opb_reposit where recid =1) as repo,
OPB_SUBJECT.SUBJ_NAME,
OPB_WFLOW_RUN.SERVER_NAME,
OPB_WFLOW_RUN.WORKFLOW_NAME, 
OPB_TASK_INST_RUN.INSTANCE_NAME,
TRUNC(OPB_TASK_INST_RUN.START_TIME) LOAD_DATE,
TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24:MI.SS') SESS_START_TIME,
TO_CHAR(OPB_TASK_INST_RUN.END_TIME,'HH24:MI.SS') SESS_END_TIME,
TRUNC((OPB_TASK_INST_RUN.END_TIME - OPB_TASK_INST_RUN.START_TIME) * 1440,2) DURATION_IN_MINS
FROM OPB_SUBJECT,OPB_TASK_INST_RUN,OPB_WFLOW_RUN,OPB_OBJECT_TYPE, OPB_SESS_TASK_LOG   
WHERE
  OPB_SUBJECT.SUBJ_ID = OPB_TASK_INST_RUN.SUBJECT_ID
     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_TASK_INST_RUN.WORKFLOW_ID
     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_TASK_INST_RUN.WORKFLOW_RUN_ID
     AND OPB_TASK_INST_RUN.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
     AND OPB_OBJECT_TYPE.OBJECT_TYPE = 68
     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_SESS_TASK_LOG.WORKFLOW_ID
     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID
     AND OPB_TASK_INST_RUN.WORKLET_RUN_ID = OPB_SESS_TASK_LOG.WORKLET_RUN_ID
     AND OPB_TASK_INST_RUN.INSTANCE_ID = OPB_SESS_TASK_LOG.INSTANCE_ID
     AND OPB_TASK_INST_RUN.INSTANCE_NAME in
     (         )
    and TRUNC(OPB_TASK_INST_RUN.START_TIME) = (
    select MAX(TRUNC(AA.START_TIME)) from OPB_TASK_INST_RUN AA
where AA.INSTANCE_NAME = OPB_TASK_INST_RUN.INSTANCE_NAME
group by AA.INSTANCE_NAME
)
ORDER BY 1,2,4

11.12           List of workflows currently running


 

SELECT DISTINCT subject_area,  workflow_name, server_name,
                start_time, end_time, TO_CHAR (start_time, 'Day') weekday,
                TRUNC (TO_CHAR ((end_time - start_time) * 1440)
                      ) elapsedminutes,
                CASE
                   WHEN run_status_code = '1'  THEN 'Succeeded'
                   WHEN run_status_code = '2'  THEN 'Disabled'
                   WHEN run_status_code = '3'  THEN 'Failed'
                   WHEN run_status_code = '4'   THEN 'Stopped'
                   WHEN run_status_code = '5'   THEN 'Aborted'
                   WHEN run_status_code = '6'   THEN 'Running'
                   WHEN run_status_code = '15'
                      THEN 'Terminated'
                END AS load_status
           FROM rep_wflow_run
          WHERE run_status_code = '6'
       ORDER BY 1,2,3
 

11.13           Check Stats of wf run (cpu usage)


opb_task_stats 


12           CONNECTIONS

12.1           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

12.2           List of cnxs 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
 

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

12.4           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

12.5           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

12.6           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


12.7           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
 

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


12.9           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)
        

12.10           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


12.11           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 ('abc')
ORDER BY 1,2

 

12.12           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 ()
 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

12.13           Kill User Connection


 

SELECT SUBJ_NAME FOLDER,
       CNX_ID,
       DECODE (LOCK_TYPE,
               1, 'NodeInUseLock',
               2, 'NodeWriteIntentLock',
               4, 'ExecuteLock',
               5, 'SharedLock',
               6, 'ExclusiveLock',
               7, 'SubTreeSharedLoc',
               8, 'SubTreeExclusiveLock',
               9, 'SubTreeISharedLock',
               10, 'SubTreeIExclusiveLock',
               11, 'SubTreeWriteIntentLock',
               12, 'SubTreeIWriteIntentLock')
          LOCK_TYPE
  FROM OPB_OBJECT_LOCKS L