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_IDAND 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’
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 CASEWHEN 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
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 bwhere
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 ()
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 = ''
|
23 comments: