9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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
9.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 '\')
9.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
9.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
9.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
9.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
9.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
9.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;
9.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
WHERE d.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
No comments:
Post a Comment