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, OPB_SUBJECT S
WHERE L.SUBJECT_ID = SUBJ_ID
and lock_type = 4
and SUBJ_NAME  = ''

 

21 comments:

  1. Thanks for your posting which made me to join informatica online training @ www.monstercourses.com

    ReplyDelete
  2. Great Job ! very helpful for Admins and developers

    ReplyDelete
  3. this is amazing. Thank you very much for sharing.

    ReplyDelete
  4. this is very helpful! I am just wondering if "The list of source and target used in session level" could be enhanced to at worklet level?

    ReplyDelete
  5. In addtion, all the source and target tables used in mapplet associated with the mappings. the return results would have listed folder name, wklt name, mapping name, mapplet name, table name, source or target indicater....

    ReplyDelete
  6. hi,

    how can i get the script because i cant get it with copy.

    ReplyDelete
  7. Thanks for a very interesting blog. What else may I get that kind of info written in such a perfect approach? I’ve a undertaking that I am simply now operating on, and I have been at the look out for such info. tech release

    ReplyDelete
  8. How to hack someones phone without touching it The versatile market empowers us to remain associated in a hurry, shop while driving and find a fundamental bit of data surprisingly fast.

    ReplyDelete
  9. you don't need to get in touch with the cellphone before getting the text messages, find out on how to https://ultimatephonespy.com/intercept-text-messages-without-target-phone-for-free-online/

    ReplyDelete
  10. If you are looking for more information about flat rate locksmith Las Vegas check that right away. Click Here
    This particular papers fabulous, and My spouse and i enjoy each of the perform that you have placed into this. I’m sure that you will be making a really useful place. I has been additionally pleased. Good perform! Read more
    I found so many interesting stuff in your blog especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the enjoyment here! keep up the good work... learn more
    Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also entertained! Check Website
    What a fantabulous post this has been. Never seen this kind of useful post. I am grateful to you and expect more number of posts like these. Thank you very much. phenix-network-forum

    ReplyDelete
  11. This is my first time visit to your blog and I am very interested in the articles that you serve. Provide enough knowledge for me. Thank you for sharing useful and don't forget, keep sharing useful info: link building

    ReplyDelete
  12. I felt very happy while reading this site. This was really very informative site for me. I really liked it. This was really a cordial post. Thanks a lot!. seo service

    ReplyDelete
  13. This is my first time i visit here. I found so many entertaining stuff in your blog, especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the leisure here! Keep up the good work. I have been meaning to write something like this on my website and you have given me an idea. PORTFOLIO

    ReplyDelete
  14. I'm happy to see the considerable subtle element here!. techesign.com

    ReplyDelete
  15. I visit your blog regularly and recommend it to all of those who wanted to enhance their knowledge with ease. The style of writing is excellent and also the content is top-notch. Thanks for that shrewdness you provide the readers! Buy Facebook Business Manager

    ReplyDelete
  16. wow, great, I was wondering how to cure acne naturally. and found your site by google, learned a lot, now i’m a bit clear. I’ve bookmark your site and also add rss. keep us updated
    London Locksmith

    ReplyDelete
  17. I should say only that its awesome! The blog is informational and always produce amazing things. zalo web

    ReplyDelete
  18. We have sell some products of different custom boxes.it is very useful and very low price please visits this site thanks and please share this post with your friends. High DA PA Blog Comment

    ReplyDelete
  19. Always be on the lookout for phishing frauds. Phishing attacks certainly are a favorite among hackers and scammers. fake bitcoin sender

    ReplyDelete
  20. Como se puede descargar los script? Estos nos no se pueden copiar

    ReplyDelete