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 = ''
|
Thanks for your posting which made me to join informatica online training @ www.monstercourses.com
ReplyDeleteGreat Job ! very helpful for Admins and developers
ReplyDeletethis is amazing. Thank you very much for sharing.
ReplyDeletethis 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?
ReplyDeleteIn 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....
ReplyDeletehi,
ReplyDeletehow can i get the script because i cant get it with copy.
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
ReplyDeleteHow 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.
ReplyDeleteyou 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/
ReplyDeleteIf you are looking for more information about flat rate locksmith Las Vegas check that right away. Click Here
ReplyDeleteThis 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
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
ReplyDeleteI 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
ReplyDeleteThis 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
ReplyDeleteI'm happy to see the considerable subtle element here!. techesign.com
ReplyDeleteI 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
ReplyDeletewow, 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
ReplyDeleteLondon Locksmith
I should say only that its awesome! The blog is informational and always produce amazing things. zalo web
ReplyDeleteWe 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
ReplyDeleteAlways be on the lookout for phishing frauds. Phishing attacks certainly are a favorite among hackers and scammers. fake bitcoin sender
ReplyDeleteGood to know that. Thank you! Dairy Wastewater Treatment Plants
ReplyDeleteComo se puede descargar los script? Estos nos no se pueden copiar
ReplyDelete