8.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
List save session log count
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
List stop on errors count
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
List hard coded 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
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
 |
| Informatica |
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
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
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
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
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
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
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
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
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
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
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
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
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
List
load type
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
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
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 '\')
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
Comments
Post a Comment