16.1 List Connection Name and Connect Type
SELECT DISTINCT
CONNECTION_TYPE,
CONNECTION_NAME,
CONNECTION_SUBTYPE,
HOST_NAME,
USER_NAME,
SCHEMA_NAME,
ADDRESS
FROM V_PC8X_CONNECTION
ORDER BY 1,2
16.2 List of all Connections
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,
case when b.ATTR_ID = 1 then 'TPTID'
when b.ATTR_ID = 2 then 'DatabaseName'
when b.ATTR_ID = 3 then 'Tenacity'
when b.ATTR_ID = 4 then 'MaxSessions'
when b.ATTR_ID = 15 then 'MinSessions'
when b.ATTR_ID = 5 then 'Sleep'
when b.ATTR_ID = 6 then 'System Operator'
when b.ATTR_ID = 7 then 'BlockSize'
when b.ATTR_ID = 8 then 'Data Encryption'
end as CONNECTION_Attribute,
b.ATTR_VALUE,
a.LAST_SAVED,
case when
a.CONNECT_STRING like '%prd%' then 'PRD CNX'
when a.CONNECT_STRING
like '%PRD%' then 'PRD CNX'
when a.CONNECT_STRING
like '%tst%' then 'TST CNX'
when a.CONNECT_STRING
like '%TST%' then 'TST CNX'
when a.CONNECT_STRING
like '%dev%' then 'DEV CNX'
when a.CONNECT_STRING
like '%DEV%' then 'DEV CNX'
end as CONNECTING_TO
from opb_cnx a,
OPB_CNX_ATTR b
where
a.OBJECT_ID = b.OBJECT_ID
order by1,2,3,4
16.3 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
16.4 List of cnxs used in session levels
SELECT DISTINCT
A.SUBJECT_AREA,
A.TASK_NAME,
B.READER_WRITER_TYPE,
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
16.5 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,
CASEWHEN 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 + 9THEN 'R'
WHEN permissions = d.user_id + 7THEN 'WX'
WHEN permissions = d.user_id + 5THEN 'W'
WHEN permissions = d.user_id + 3THEN 'X'
ELSE 'NULL' END
WHEN user_type = 2 THEN
CASE WHEN permissions = d.user_id + 29THEN 'RWX'
WHEN permissions = d.user_id + 25THEN 'RW'
WHEN permissions = d.user_id + 21THEN 'RX'
WHEN permissions = d.user_id + 17THEN 'R'
WHEN permissions = d.user_id + 13THEN 'WX'
WHEN permissions = d.user_id + 9THEN 'W'
WHEN permissions = d.user_id + 5THEN '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 + 9THEN '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
16.6 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 + 9THEN 'R'
WHEN permissions = d.user_id + 7THEN 'WX'
WHEN permissions = d.user_id + 5THEN 'W'
WHEN permissions = d.user_id + 3THEN 'X'
ELSE 'NULL'
END
WHEN user_type = 2 THEN
CASE
WHEN permissions = d.user_id + 29THEN 'RWX'
WHEN permissions = d.user_id + 25THEN 'RW'
WHEN permissions = d.user_id + 21THEN 'RX'
WHEN permissions = d.user_id + 17THEN 'R'
WHEN permissions = d.user_id + 13THEN 'WX'
WHEN permissions = d.user_id + 9THEN 'W'
WHEN permissions = d.user_id + 5THEN '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 + 9THEN '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
16.7 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
select * from rep_session_cnxs where connection_name in ('Source_aaaa','Target_bbbb','Source_ccccc')
order by 1
16.8 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 + 13THEN 'RW'WHEN permissions = d.user_id + 11THEN 'RX'WHEN permissions = d.user_id + 9THEN 'R'WHEN permissions = d.user_id + 7 THEN 'WX'WHEN permissions = d.user_id + 5THEN 'W'
WHEN permissions = d.user_id + 3THEN 'X'
ELSE 'NULL'
END
WHEN user_type = 2
THEN CASE
WHEN permissions = d.user_id + 29 THEN 'RWX'
WHEN permissions = d.user_id + 25THEN 'RW'
WHEN permissions = d.user_id + 21THEN 'RX'
WHEN permissions = d.user_id + 17THEN 'R'
WHEN permissions = d.user_id + 13THEN 'WX'
WHEN permissions = d.user_id + 9THEN 'W'
WHEN permissions = d.user_id + 5THEN 'X'
ELSE 'NULL'
END
WHEN user_type = 3
THEN CASE
WHEN permissions = d.user_id + 57THEN 'RWX'
WHEN permissions = d.user_id + 39 THEN 'RW'
WHEN permissions = d.user_id + 41THEN 'RX'
WHEN permissions = d.user_id + 33THEN 'R'
WHEN permissions = d.user_id + 25THEN 'WX'
WHEN permissions = d.user_id + 17THEN 'W'
WHEN permissions = d.user_id + 9THEN '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
16.9 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;
16.10 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)
-- AND ALL_SESSIONS.SUBJECT_AREA = 'CDM'
16.11 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 by1,2,3,4
16.12 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 ('cnx)
ORDER BY 1,2
16.13 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
(
'_GCOS',
'ABC',
'ADM_ODS_Src',
'AMSDM'
)
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
SELECT DISTINCT
(select repository_name from OPB_REPOSIT_INFO) as REPO,
B.SUBJECT_AREA,
D.task_name as workflow_name,
B.SESSION_NAME,
B.CONNECTION_NAME,
C.last_run
FROM OPB_CNX A, REP_SESSION_CNXS B,
(
select DISTINCT
TASK_ID,
INSTANCE_NAME,
workflow_id,
MAX(START_TIME) last_run
from OPB_TASK_INST_RUN
where task_type =68
--and task_ID = 25990
GROUP BY
TASK_ID,
INSTANCE_NAME,
workflow_id
) C
,
(select task_id,task_name from opb_task where task_type =71) D
WHERE A.OBJECT_ID = B.CONNECTION_ID
AND b.SESSION_ID = C.TASK_ID
AND C.Workflow_id=D.TASK_ID
--AND C.task_type in('68','71')
--AND C.task_ID = 25990
AND B.CONNECTION_NAME IN
('KAM_DM')
16.14 How to update permissions of mass connections
Incase if we have
to update permission for multiple connections please use the below pmrep
command, Adjust the parameters as needed and make it as a script under
$INFA_HOME/server/bin.
AssignPermission
-o [-t ] -n {-u
| -g } [-s ] -p
Example:
pmrep connect -r
Repository_EDW9Prd -d Domain_Dev -n Admin -s LDAP -x xxxx
pmrep
assignPermission -o Connection -t Relational -n ADM_ODS_Src_d -g
Informatica_TD_Upgrade -p RWX (use updatepermission for modifying the
currently assigned permission)
16.15 Session level Connection Details
Below query is used to identify source connections, target connections and lookup connections in a session task.
SELECT src.subject_name, src.workflow_name, src.session_name,
(CASE
WHEN cnx.object_type = 78
THEN 'SOURCE CONNECTION'
WHEN cnx.object_type = 79
THEN 'TARGET CONNECTION'
WHEN cnx.object_type = 103
THEN 'LOOKUP CONNECTION'
END
) AS connection_type,
cnx.connection_name, cnx.database_name, cnx.schema_name
FROM (SELECT s.subj_name AS subject_name, a.task_name AS workflow_name,
b.instance_name AS session_name, b.task_id
FROM opb_subject s,
(SELECT *
FROM opb_task
WHERE task_type = 71 AND is_visible = 1) a,
(SELECT *
FROM opb_task_inst
WHERE task_type = 68) b
WHERE s.subj_id = a.subject_id
AND a.task_id = b.workflow_id
AND a.version_number = b.version_number
UNION ALL
SELECT s.subj_name AS subject_name, a.task_name AS workflow_name,
c.instance_name AS session_name, c.task_id
FROM opb_subject s,
(SELECT *
FROM opb_task
WHERE task_type = 71 AND is_visible = 1) a,
(SELECT *
FROM opb_task_inst
WHERE task_type IN (70, 68)) b,
(SELECT *
FROM opb_task_inst
WHERE is_valid = 1 AND task_type = 68) c
WHERE s.subj_id = a.subject_id
AND a.task_id = b.workflow_id
AND c.workflow_id(+) = b.task_id
AND a.version_number = b.version_number) src,
(SELECT DISTINCT d.session_id, c.object_name connection_name,
c.connect_string database_name,
c.user_name schema_name, e.object_type
FROM opb_cnx c, opb_sess_cnx_refs d, opb_sess_extns e
WHERE d.workflow_id = 0
AND d.session_inst_id = 0
AND d.session_id = e.session_id
AND d.sess_widg_inst_id = e.sess_widg_inst_id
AND c.object_id = d.ref_object_id) cnx
WHERE cnx.session_id = src.task_id
AND src.session_name = 's_m_LOAD_MDRIODS_OC_DM_DCI_PHYS_BOOK_PAGES_DEF'
ORDER BY 1, 2, 3, 4 DESC
select SUBJECT_AREA,
WIDGET_TYPE,
READER_WRITER_TYPE,
REPOSITORY_NAME,
SESSION_NAME,
WIDGET_INSTANCE_ID,
SESSION_INSTANCE_NAME,
CNX_NAME,
WORKFLOW_NAME
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME,
SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_ALL_CNXS.CNX_NAME, SESSION_ALL_CNXS.READER_WRITER_TYPE,
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 AS WIDGET_TYPE, (COUNT(SESSION_ALL_CNXS.WIDGET_INSTANCE_ID)) AS
WIDGET_INSTANCE_ID FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_WIDGET_CNXS SESSION_ALL_CNXS, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_SESS_LOG SESSION_LOG
WHERE (ALL_SESSIONS.SESSION_ID = SESSION_ALL_CNXS.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_ALL_CNXS. SESSION_VERSION_NUMBER
AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID
AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER)
GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME,
SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_ALL_CNXS.CNX_NAME, SESSION_ALL_CNXS.READER_WRITER_TYPE,
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) query
order by REPOSITORY_NAME, SUBJECT_AREA