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