12.1 List session names
SELECT SUBJECT_AREA, TASK_TYPE_NAME, TASK_NAME
FROM REP_ALL_TASKS WHERE TASK_TYPE IN (68)
--AND
SUBJECT_AREA= 'ABC'
ORDER BY 1, 2, 3
12.2 List “save session log” runs
select distinct cc.subject_area,cc.task_name as session_name,bb.attr_value as Savesessionlog
from
(select a.session_id,min(a.config_id) as config_id,a.attr_id from rep_sess_config_parm a
where a.attr_id = '103' group by a.session_id,a.attr_id) aa,
(select session_id,config_id,attr_value from rep_sess_config_parm
where attr_id = '103') bb,
(select subject_area,task_name,task_id from rep_all_tasks ) cc
where aa.session_id = bb.session_id
and aa.config_id=bb.config_id
and bb.session_id = cc.task_id
and bb.attr_value not in (8,4)
order by 1,2,3
12.3 List “stop on errors” count value
SELECT DISTINCT
A.SUBJECT_AREA,
A.TASK_NAME AS
SESSION_NAME,
B.ATTR_VALUE AS
STOPONERRORS
FROM
REP_ALL_TASKS
A ,
REP_SESS_CONFIG_PARM B
WHERE
A.TASK_ID = B.SESSION_ID
AND TASK_TYPE_NAME = 'Session' AND B.ATTR_ID = '202'
--AND B.ATTR_VALUE NOT IN (1) --AND A.SUBJECT_AREA in ('ABC')
ORDER BY 1,2
12.4 List hardcoded paths
SELECT DISTINCT
A.SUBJECT_AREA,
A.TASK_NAME AS
SESSION_NAME,
B.FILE_NAME,
DIR_NAME
FROM
REP_ALL_TASKS
A ,
OPB_SESS_FILE_VALS B
WHERE
A.TASK_TYPE_NAME = 'Session'
AND A.TASK_ID
= B.SESSION_ID
--AND A.SUBJECT_AREA IN ('ABC')
ORDER BY 1,2
12.5 List parameter file paths
SELECT DISTINCT
B.SUBJECT_AREA,
B.TASK_NAME AS
SES_WF_NAME,
A.ATTR_VALUE AS
PRM_FILE_PATH
FROM
OPB_TASK_ATTR A,
REP_ALL_TASKS B
WHERE
A.ATTR_ID IN (1,4)
AND A.TASK_ID = B.TASK_ID
AND A.ATTR_VALUE LIKE '%.prm%'
ORDER BY 1,2 ASC
12.6 List session log names
SELECT DISTINCT
A.SUBJECT_AREA,
A.WORKFLOW_NAME,
A.SESSION_NAME,
A.SESSION_INSTANCE_NAME,
SUBSTR(A.SESSION_LOG_FILE,25,300) AS
EXISTING_SESSLOGNAME
FROM
REP_SESS_LOG A
WHERE
SUBSTR(A.SESSION_LOG_FILE,25,300) != CONCAT(LOWER(A.SESSION_INSTANCE_NAME),'.log')
ORDER BY 1,2,3
12.7 List commit intervals
SELECT
B.SUBJECT_AREA,
B.TASK_NAME AS
SESS_NAME,
A.ATTR_VALUE AS
COMMITINTERVEL
FROM
OPB_TASK_ATTR A ,
REP_ALL_TASKS B
WHERE
A.ATTR_ID IN (14)
AND A.ATTR_VALUE <> 10000
AND A.TASK_ID = B.TASK_ID
AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC
12.8 List total source partitions
SELECT
B.SUBJECT_AREA,
B.TASK_NAME AS
SESS_NAME,
A.ATTR_VALUE AS
TOTAL_SOURCE_PARTITIONS
FROM
OPB_TASK_ATTR A ,
REP_ALL_TASKS B
WHERE
A.ATTR_ID IN (12)
AND A.TASK_ID = B.TASK_ID
AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC
12.9 List total target partitions
SELECT
B.SUBJECT_AREA,
B.TASK_NAME AS
SESS_NAME,
A.ATTR_VALUE AS
TOTAL_TARGET_PARTITIONS
FROM
OPB_TASK_ATTR A ,
REP_ALL_TASKS B
WHERE
A.ATTR_ID IN (11)
AND A.TASK_ID = B.TASK_ID
AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC
12.10 List DTM Buffer Size
SELECT
B.SUBJECT_AREA,
B.TASK_NAME AS
SESS_NAME,
A.ATTR_VALUE AS
DTM_BUFFER_SIZE
FROM
OPB_TASK_ATTR A ,
REP_ALL_TASKS B
WHERE
A.ATTR_ID IN (101)
AND A.TASK_ID = B.TASK_ID
AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC
12.11 List collect performance data
SELECT
B.SUBJECT_AREA,
B.TASK_NAME AS
SESS_NAME,
A.ATTR_VALUE AS COLLECT_PERFORMANCE_DATA
FROM
OPB_TASK_ATTR A ,
REP_ALL_TASKS B
WHERE
A.ATTR_ID IN (102)
AND A.TASK_ID = B.TASK_ID
AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC
12.12 List Incremental Aggregation
SELECT
B.SUBJECT_AREA,
B.TASK_NAME AS
SESS_NAME,
A.ATTR_VALUE AS
INCREMENTAL_AGGREGATION
FROM
OPB_TASK_ATTR A ,
REP_ALL_TASKS B
WHERE
A.ATTR_ID IN (103)
AND A.TASK_ID = B.TASK_ID
AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC
12.13 List Reinitialize Aggregate Cache
SELECT
B.SUBJECT_AREA,
B.TASK_NAME AS SESS_NAME,
A.ATTR_VALUE AS REINITIALIZE_AGGREGATE_CACHE
FROM
OPB_TASK_ATTR A
,
REP_ALL_TASKS
B
WHERE
A.ATTR_ID IN (104)
AND A.TASK_ID = B.TASK_ID
AND
TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC
12.14 List Enable high precision
SELECT
B.SUBJECT_AREA,
B.TASK_NAME AS
SESS_NAME,
A.ATTR_VALUE AS
ENABLE_HIGH_PRECISION
FROM
OPB_TASK_ATTR A ,
REP_ALL_TASKS B
WHERE
A.ATTR_ID IN (105)
AND A.TASK_ID = B.TASK_ID
AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC
12.15 List Session retry on deadlock
SELECT
B.SUBJECT_AREA,
B.TASK_NAME AS
SESS_NAME,
A.ATTR_VALUE AS SESSION_RETRYON_DEADLOCK
FROM
OPB_TASK_ATTR A ,
REP_ALL_TASKS B
WHERE
A.ATTR_ID IN (106)
AND A.TASK_ID = B.TASK_ID
AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC
12.16 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 68
AND B.ATTR_ID = 17
--AND B.ATTR_VALUE <> 1
ORDER BY 1,2,3
12.17 List over ride tracing
SELECT REP_REPOSIT_INFO.REPOSITORY_NAME,
REP_ALL_TASKS.SUBJECT_AREA AS FOLDER_NAME,
REP_ALL_TASKS.TASK_NAME AS SESSION_NAME,CASE
WHEN REP_SESS_CONFIG_PARM.ATTR_VALUE = 0 THEN 'NONE'
WHEN REP_SESS_CONFIG_PARM.ATTR_VALUE = 1 THEN 'TERSE'
WHEN REP_SESS_CONFIG_PARM.ATTR_VALUE = 2 THEN 'NORMAL'
WHEN REP_SESS_CONFIG_PARM.ATTR_VALUE = 3 THEN 'VERBOSE INITIALIZATION'
WHEN REP_SESS_CONFIG_PARM.ATTR_VALUE = 4 THEN 'VERBOSE DATA'
END AS OVERRIDE_TRACING
FROM
REP_REPOSIT_INFO,
REP_ALL_TASKS,
REP_SESS_CONFIG_PARM
WHERE
REP_ALL_TASKS.TASK_ID = REP_SESS_CONFIG_PARM.SESSION_ID
AND REP_SESS_CONFIG_PARM.ATTR_ID = 204
--AND REP_SESS_CONFIG_PARM.ATTR_VALUE NOT IN (0,2)
ORDER BY 1,2,3
12.18 List save session log by
SELECT
A.SUBJECT_AREA,
A.TASK_NAME AS SESSION_NAME,
B.ATTR_NAME,
DECODE(B.ATTR_VALUE,1,'TIME STAMP','BY RUNS') AS SAVE_SESSION_LOG_BY
FROM
REP_ALL_TASKS A ,
REP_SESS_CONFIG_PARM B
WHERE
A.TASK_ID = B.SESSION_ID
AND TASK_TYPE_NAME = 'Session'
AND B.ATTR_ID IN ('102')
--AND B.ATTR_VALUE <> 0
ORDER BY 1,2
12.19 List load type Normal or bulk
SELECT DISTINCT
REP_LOAD_SESSIONS.SUBJECT_AREA AS FOLDER, REP_LOAD_SESSIONS.SESSION_NAME,
--REP_SESS_WIDGET_CNXS.CNX_NAME AS CONNECTION_NAME,
CASE WHEN OPB_EXTN_ATTR.ATTR_VALUE ='0' THEN 'NORMAL'
WHEN OPB_EXTN_ATTR.ATTR_VALUE ='1' THEN 'BULK'
END AS TARGET_LOAD_TYPE
FROM
REP_LOAD_SESSIONS,
REP_SESS_WIDGET_CNXS,
OPB_EXTN_ATTR
WHERE REP_LOAD_SESSIONS.SESSION_ID=REP_SESS_WIDGET_CNXS.SESSION_ID
AND REP_LOAD_SESSIONS.SESSION_ID=OPB_EXTN_ATTR.SESSION_ID
AND OPB_EXTN_ATTR.ATTR_ID=3
AND OPB_EXTN_ATTR.ATTR_VALUE BETWEEN '0' AND '1'
AND REP_SESS_WIDGET_CNXS.READER_WRITER_TYPE='Relational Writer'
--AND OPB_EXTN_ATTR.ATTR_VALUE ='1'
ORDER BY 1,2
12.20 List 'post_session_success_command' in session
SELECT DISTINCT C.SUBJ_NAME AS FOLDER ,A.TASK_NAME AS TASK, B.PM_VALUE
AS COMMAND
FROM OPB_TASK A,OPB_TASK_VAL_LIST B, OPB_SUBJECT C
WHERE A.TASK_TYPE=58 AND A.TASK_NAME='post_session_success_command'
AND B.TASK_ID=A.TASK_ID AND B.SUBJECT_ID=C.SUBJ_ID
ORDER BY 1
12.21 List of all the emails with attachment
SELECT DISTINCT
D.SUBJ_NAME AS
FOLDER_NAME, C.WORKFLOW_NAME AS
WORKFLOW_NAME,A.TASK_NAME AS
TASK_NAME, B.ATTR_VALUE AS VALUE
FROM
OPB_TASK A, OPB_TASK_ATTR B, REP_TASK_INST_RUN C, OPB_SUBJECT D
WHERE A.TASK_ID = B.TASK_ID
AND A.TASK_TYPE = B.TASK_TYPE
AND C.SUBJECT_ID = A.SUBJECT_ID
AND A.SUBJECT_ID = D.SUBJ_ID
AND A.TASK_TYPE = 65
AND B.ATTR_ID IN (2,3)
AND
(B.ATTR_VALUE LIKE '%%a%' ESCAPE '' OR B.ATTR_VALUE LIKE '%%g%' ESCAPE '')
12.22 Invalid Sessions and Workflows
select opb_subject.subj_name,
opb_task.task_name
from
opb_task,
opb_subject
where task_type in (68,71)
and is_valid = 0
and opb_subject.subj_id =
opb_task.subject_id
order by 1,2
SELECT SUBJECT_AREA AS FOLDER_NAME,
DECODE(IS_REUSABLE,1,'Reusable',' ') || ' '
||TASK_TYPE_NAME AS TASK_TYPE,
TASK_NAME AS OBJECT_NAME,
DECODE(IS_VALID,0,'INVALID OBJECT','VALID OBJECT') STATUS,
LAST_SAVED
FROM REP_ALL_TASKS
WHERE IS_VALID=0
AND IS_ENABLED=1
--AND CHECKOUT_USER_ID = 0 -- Comment
out for V6
--AND is_visible=1 -- Comment out for
V6
ORDER BY 1,2
12.23 List of session run history – Long running sessions
SELECT
subject_area, workflow_name, instance_name,
TO_CHAR (start_time, 'Dy
MM/DD/YYYY HH:MI:SS AM') start_time,
TO_CHAR (end_time, 'Dy
MM/DD/YYYY HH:MI:SS AM') end_time,
ROUND (((end_time - start_time) * 24) * 60) elapsed
FROM rep_task_inst_run
WHERE task_type = 68
AND start_time > TO_DATE ('01/01/2012', 'mm/dd/yyyy')
-- AND subject_area = 'ABC' AND workflow_name='wfc_dly'
ORDER BY 1, 2
12.24 List Bad file dir name of a session
SELECT subject_area,session_name,session_instance_name,bad_file_location FROM
REP_SESS_TBL_LOG
--where
bad_file_location not like '$PMBadFileDir%' order by 1,2,3
12.25 List Throughput of the session
SELECT subject_area,session_name,session_instance_name,start_time,end_time,throughput
FROM REP_SESS_TBL_LOG
where throughput > 600
order by 1,2,3
12.26 List the current memory settings (buffer block size, etc.)
SELECT a.SUBJECT_AREA AS Folder_Name, a.task_name AS Session_Name, b.ATTR_VALUE AS DTM_BUFFER_SIZE, c.ATTR_VALUE
AS Buffer_Block_Size,
d.ATTR_VALUE AS Line_Sequential_Buffer_Length
FROM REP_ALL_TASKS a, REP_TASK_ATTR b,
REP_SESS_CONFIG_PARM c, REP_SESS_CONFIG_PARM d
WHERE a.TASK_ID = b.TASK_ID
AND a.TASK_ID = c.SESSION_ID
AND a.TASK_ID = d.SESSION_ID
AND b.ATTR_ID = 101
AND c.ATTR_ID = 5
AND d.ATTR_ID = 6
and a.SUBJECT_AREA like '%EBI_NACO_%'
ORDER BY 1, 2
12.27 Pushdown Optimization
SELECT T.*,SUBJECT_AREA
B,WORKFLOW_NAME B FROM OPB_TASK T, OPB_TASK_ATTR A,REP_WORKFLOWS B WHERE T.TASK_ID=A.TASK_ID AND
A.ATTR_ID=107 AND
ATTR_VALUE=1 AND T.SUBJECT_ID=B.SUBJECT_ID;
SELECT T.*,SUBJECT_AREA
B,WORKFLOW_NAME B FROM OPB_TASK T, OPB_TASK_ATTR A,REP_WORKFLOWS B WHERE T.TASK_ID=A.TASK_ID AND
A.ATTR_ID=107 AND
ATTR_VALUE=2 AND T.SUBJECT_ID=B.SUBJECT_ID;
SELECT T.*,SUBJECT_AREA
B,WORKFLOW_NAME B FROM OPB_TASK T, OPB_TASK_ATTR A,REP_WORKFLOWS B WHERE T.TASK_ID=A.TASK_ID AND
A.ATTR_ID=107 AND
ATTR_VALUE=3 AND T.SUBJECT_ID=B.SUBJECT_ID;
12.28 Persistent Cache
SELECT DISTINCT c.PARENT_SUBJECT_AREA, c.widget_name, d.attr_name
FROM OPB_WIDGET_ATTR b, rep_all_transforms C, OPB_ATTR d
WHEREd.attr_id = b.attr_id
AND d.attr_type = 5
AND b.attr_id = 12
AND b.widget_type = 11
AND c.widget_id = b.widget_id
AND b.attr_value = 1
-- 1 is enabled and 0 id
disabled
12.29 Sorter Cache Size (Default is Auto)
SELECT DISTINCT
s.SUBJECT_AREA,
s.SESSION_NAME,
s.SESSION_ID,
s.MAPPING_NAME,
s.MAPPING_ID,
w.INSTANCE_NAME,
ATTR_VALUE as "SorterCacheSize"
FROM
REP_LOAD_SESSIONS s,
REP_WIDGET_INST w,
OPB_WIDGET_ATTR wa
WHERE w.WIDGET_ID = wa.WIDGET_ID
AND wa.widget_type IN (80)
AND wa.ATTR_ID = 1
AND w.MAPPING_ID = s.MAPPING_ID
AND ATTR_VALUE <> 'Auto'
ORDER BY SUBJECT_AREA
12.30 Session Task Properties Query
Below query is used to find out the general options and performance attributes and their values on the “Properties” tab of a session task.
SELECT s.subj_name, m.mapping_name as mapping_name, t.task_name,
DECODE (ota.attr_id,
1, 'General Options',
2, 'Session Log File Name',
3, 'Session Log File directory',
4, 'Parameter Filename',
5, 'Enable Test Load',
6, 'Number of rows to test',
7, '$Source connection value',
8, '$Target connection value',
9, 'Treat source rows as',
10, 'Current log count',
11, 'Total target partitions',
12, 'Total source partitions',
13, 'Commit Type',
14, 'Commit Interval',
15, 'Commit On End Of File',
16, 'Rollback Transactions on Errors',
100, 'Performance',
101, 'DTM buffer size',
102, 'Collect performance data',
103, 'Incremental Aggregation',
104, 'Reinitialize aggregate cache',
105, 'Enable high precision',
106, 'Session retry on deadlock'
) AS property,
ota.attr_value
FROM opb_subject s,
opb_mapping m,
opb_session se,
opb_task t,
opb_task_inst oti,
opb_task_attr ota
WHERE s.subj_id = m.subject_id
AND m.is_visible = 1
AND m.ref_widget_id = 0
AND m.mapping_id = se.mapping_id
AND se.session_id = t.task_id
AND t.is_visible = 1
AND se.version_number = t.version_number
AND t.task_id = oti.task_id
AND t.version_number = oti.version_number
AND oti.task_id = ota.task_id
AND oti.version_number = ota.version_number
AND s.subj_id = 38
AND t.task_name = 's_m_LOAD_DIFPRS_P_REG_CENTER'
ORDER BY 1, 2, 3, ota.attr_id
12.31 Session Level SQL Override
Below query is used to find sql override at session level.
SELECT src.subj_name, src.workflow_name, src.worklet_name, src.session_name, a.attr_value AS sql_override
FROM (SELECT s.subj_name, wf.task_name AS workflow_name, NULL AS worklet_name, t.instance_name AS session_name, t.task_id,
t.version_number
FROM opb_subject s, opb_task wf, opb_task_inst t
WHERE wf.subject_id = s.subj_id
AND wf.is_visible = 1
AND wf.task_type = 71
AND wf.task_id = t.workflow_id
AND t.task_type = 68
AND wf.version_number = t.version_number
UNION
SELECT s.subj_name AS subject_name, wf.task_name AS workflow_name, wl.instance_name AS worklet_name,
c.instance_name AS session_name, c.task_id, c.version_number
FROM opb_subject s,
(SELECT *
FROM opb_task
WHERE task_type = 71 AND is_visible = 1) wf,
(SELECT *
FROM opb_task_inst
WHERE task_type = 70) wl,
(select workflow_id,task_id,instance_name,max(version_number) as version_number from opb_task_inst
where task_type=68
group by workflow_id,task_id,instance_name) c
WHERE s.subj_id = wf.subject_id
AND wf.task_id = wl.workflow_id
AND c.workflow_id = wl.task_id
AND wf.version_number = wl.version_number
) src,
opb_swidget_attr a
WHERE src.task_id = a.session_id AND src.version_number = a.version_number
AND a.attr_id = 1 AND a.attr_value IS NOT NULL
ORDER BY 1, 2, 3 DESC, 4
12.32 Truncate and Reload
Below query is used to Lists the Truncate and Reload option status at session level.
SELECT src.subj_name, src.workflow_name, src.worklet_name, src.session_name,
DECODE (oea.attr_value, 1, 'ENABLED', 'DISABLE') AS truncate_table_option
FROM (SELECT s.subj_name, wf.task_name AS workflow_name, NULL AS worklet_name, t.instance_name AS session_name, t.task_id
FROM opb_subject s, opb_task wf, opb_task_inst t
WHERE wf.subject_id = s.subj_id
AND wf.is_visible = 1
AND wf.task_type = 71
AND wf.task_id = t.workflow_id
AND t.task_type = 68
AND wf.version_number = t.version_number
UNION
SELECT s.subj_name AS subject_name, wf.task_name AS workflow_name, wl.instance_name AS worklet_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) wf,
(SELECT *
FROM opb_task_inst
WHERE task_type = 70) wl,
(select workflow_id,task_id,instance_name,max(version_number) as version_number from opb_task_inst
where task_type=68
group by workflow_id,task_id,instance_name) c
WHERE s.subj_id = wf.subject_id
AND wf.task_id = wl.workflow_id
AND c.workflow_id = wl.task_id
AND wf.version_number = wl.version_number
) src,
(SELECT DISTINCT session_id, attr_value
FROM opb_extn_attr
WHERE attr_id = 9) oea
WHERE src.task_id = oea.session_id
ORDER BY 1, 2, 3 DESC, 4
12.33 Session Enable Check
Below query identifies whether the session is disabled or enabled in a particular workflow/worklet
SELECT s.subj_name, t.task_name AS workflow_worklet_name, oti.instance_name AS session_name,
DECODE (oti.is_enabled, 1, 'ENABLED', 'DISABLED') session_status
FROM opb_subject s, opb_task t, opb_task_inst oti
WHERE s.subj_id = t.subject_id
AND t.task_type IN (70, 71)
AND t.is_visible = 1
AND oti.workflow_id = t.task_id
AND oti.version_number = t.version_number
AND oti.task_type = 68
/* Input your workflow/worklet Name */
-- and t.task_name ='wklt_Hierarchy_Stage'
ORDER BY 1, 2, 3