Repository SQL

SQL to check if the Verbose Data is specified for any mapping or session

--- Reusable Sessions which have Verbose data enabled.
SELECT S.SUBJ_NAME, T.TASK_ID, T.TASK_NAME, SI.INSTANCE_NAME, SWA.ATTR_VALUE actual_value, T.*
-- SI.WIDGET_ID, SI.WIDGET_TYPE, SI.SESS_WIDG_INST_ID, A.ATTR_NAME, A.ATTR_VALUE default_value,
FROM OPB_SUBJECT S, OPB_TASK T, OPB_SWIDGET_INST SI, OPB_SWIDGET_ATTR SWA, OPB_ATTR A
WHERE S.SUBJ_ID = T.SUBJECT_ID
AND T.IS_REUSABLE=1 AND T.TASK_ID =SI.SESSION_ID
AND SI.SESS_WIDG_INST_ID = SWA.SESS_WIDG_INST_ID
AND SI.SESSION_ID = SWA.SESSION_ID
AND SI.WIDGET_TYPE = A.OBJECT_TYPE_ID AND SWA.ATTR_ID = A.ATTR_ID AND A.ATTR_NAME ='Tracing Level'
AND SUBJ_NAME LIKE '%FIX%' --Specify the informatica folder name


-- Reusable Sessions in Workflow / worklet which have Verbose data enabled.
SELECT S.SUBJ_NAME,W.TASK_NAME WORKFLOW_NAME, T.TASK_ID, T.INSTANCE_NAME, SI.INSTANCE_NAME, SWA.ATTR_VALUE actual_value,
-- SI.WIDGET_ID, SI.WIDGET_TYPE, SI.SESS_WIDG_INST_ID, A.ATTR_NAME, A.ATTR_VALUE default_value,
FROM OPB_SUBJECT S,OPB_TASK W, OPB_TASK_INST T, OPB_SWIDGET_INST SI, OPB_SWIDGET_ATTR SWA, OPB_ATTR A
WHERE S.SUBJ_ID = W.SUBJECT_ID
AND W.TASK_ID = T.WORKFLOW_ID
AND T.TASK_ID =SI.SESSION_ID
AND SI.SESS_WIDG_INST_ID = SWA.SESS_WIDG_INST_ID
AND SI.SESSION_ID = SWA.SESSION_ID
AND SI.WIDGET_TYPE = A.OBJECT_TYPE_ID AND SWA.ATTR_ID = A.ATTR_ID AND A.ATTR_NAME ='Tracing Level'
AND SUBJ_NAME LIKE '%FIX%' --Specify the informatica folder name


--- Mappings which have Verbose data enabled.
SELECT S.SUBJ_NAME, M.MAPPING_NAME, WI.INSTANCE_NAME, WI.WIDGET_TYPE, A.ATTR_NAME, WA.ATTR_VALUE actual_value
FROM OPB_SUBJECT S,OPB_MAPPING M, OPB_WIDGET_INST WI, OPB_WIDGET_ATTR WA, OPB_ATTR A
WHERE S.SUBJ_ID = M.SUBJECT_ID
AND M.MAPPING_ID = WI.MAPPING_ID
AND WI.WIDGET_ID = WA.WIDGET_ID
AND wi.WIDGET_TYPE = A.OBJECT_TYPE_ID
AND wa.ATTR_ID = A.ATTR_ID
AND A.ATTR_NAME ='Tracing Level' AND WA.ATTR_VALUE != '2'
AND SUBJ_NAME LIKE '%FIX%' --Specify the informatica folder name


SQL to get the Recovery Strategy Attribute of a session

SELECT s.subj_name, t.task_name, ta.attr_id, ta.attr_value
FROM opb_subject s, opb_task t, opb_Task_attr ta
WHERE s.subj_id = t.subject_id
AND t.task_id = ta.task_id AND ta.attr_id = 18
AND t.task_name LIKE 's_mFIX507%'
ORDER BY 3

SELECT
s.subj_name,w.task_name worfklow, t.instance_id , ta.instance_id ,t.instance_name, ta.attr_id, ta.attr_value
FROM opb_subject s, opb_Task w, opb_task_inst t, opb_Task_attr ta
WHERE s.subj_id = w.subject_id AND w.task_id = t.workflow_id
AND t.task_id = ta.task_id AND (ta.instance_id = t.instance_id OR ta.instance_id = 0)
AND ta.attr_id = 18 AND ta.attr_value != '2'
--AND t.instance_name LIKE 's_mFIX507%'
ORDER BY 3