Informatica 9.6.1 : MX Views Queries

Query to get Run timings for Workflow Instances

SELECT
WORKFLOW_NAME,
INSTANCE_NAME,
START_TIME,
END_TIME,

ROUND(( END_TIME - START_TIME) *1440 ) AS RUN_MINUTES
FROM
INFA96_PROD_REP.REP_TASK_INST_RUN
WHERE
WORKFLOW_NAME = 'wf_LOAD_DW_DIMENSIONS'
AND ROUND(( END_TIME - START_TIME) *1440 ) > 10 -- Running for more than 10 minutes --
AND START_TIME BETWEEN TO_DATE('20150927 170000', 'YYYYMMDD HH24MISS')
AND  TO_DATE('20150928 170000', 'YYYYMMDD HH24MISS')
ORDER BY START_TIME;

Query to get Disabled Tasks on Workflows

    SELECT DISTINCT
      OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,
      OPB_SUBJECT.SUBJ_NAME            AS FOLDER_NAME,
      OPB_TASK.TASK_NAME               AS WORKFLOW_OR_WORKLET,
      DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT'
      ,62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,
      'WORKLET',91,'CONTROL', NULL) TASK_TYPE,
      OPB_TASK_INST.INSTANCE_NAME                               AS WORKLET_OR_SESSION,
      DECODE (OPB_TASK_INST.IS_ENABLED,1, 'ENABLED','DISABLED') AS
      IS_TASK_ENABLED
    FROM
      OPB_TASK_INST,
      OPB_OBJECT_TYPE,
      OPB_TASK,
      OPB_SUBJECT,
      OPB_REPOSIT_INFO
    WHERE
        OPB_TASK_INST.TASK_TYPE      = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
    AND OPB_TASK_INST.WORKFLOW_ID    = OPB_TASK.TASK_ID
    AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER
    AND OPB_TASK.SUBJECT_ID          = OPB_SUBJECT.SUBJ_ID
    AND OPB_TASK_INST.IS_ENABLED <> 1
    AND OPB_SUBJECT.SUBJ_NAME ='ANALYTICS' --- Filter based on Folder ---
    --- Filter common running workflows if needed --
    AND OPB_TASK.TASK_NAME IN ( SELECT DISTINCT WORKFLOW_NAME NAME FROM opb_wflow_run WHERE START_TIME > SYSDATE - 15 );