Friday, January 20, 2017

Informatica Metadata Queries - 12 WORKFLOW



12.1         List of workflow run statistics



SELECT DISTINCT OPB_SUBJECT.SUBJ_NAME,OPB_WFLOW_RUN.WORKFLOW_NAME, 
OPB_TASK_INST_RUN.INSTANCE_NAME,
TRUNC(OPB_TASK_INST_RUN.START_TIME) LOAD_DATE,
TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24:MI.SS') SESS_START_TIME,
TO_CHAR(OPB_TASK_INST_RUN.END_TIME,'HH24:MI.SS') SESS_END_TIME,
TRUNC((OPB_TASK_INST_RUN.END_TIME - OPB_TASK_INST_RUN.START_TIME) * 1440,2) DURATION_IN_MINS
FROM OPB_SUBJECT,OPB_TASK_INST_RUN,OPB_WFLOW_RUN,OPB_OBJECT_TYPE, OPB_SESS_TASK_LOG   
WHERE
  
  OPB_SUBJECT.SUBJ_ID = OPB_TASK_INST_RUN.SUBJECT_ID
     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_TASK_INST_RUN.WORKFLOW_ID
     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_TASK_INST_RUN.WORKFLOW_RUN_ID
     AND OPB_TASK_INST_RUN.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
     AND OPB_OBJECT_TYPE.OBJECT_TYPE = 68
     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_SESS_TASK_LOG.WORKFLOW_ID
     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID
     AND OPB_TASK_INST_RUN.WORKLET_RUN_ID = OPB_SESS_TASK_LOG.WORKLET_RUN_ID
     AND OPB_TASK_INST_RUN.INSTANCE_ID = OPB_SESS_TASK_LOG.INSTANCE_ID
ORDER BY 1,2,4


SELECT DISTINCT OPB_SUBJECT.SUBJ_NAME,OPB_WFLOW_RUN.WORKFLOW_NAME, 
OPB_TASK_INST_RUN.INSTANCE_NAME,
OPB_TASK_INST_RUN.START_TIME LOAD_DATE,
TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24') HOUR,
OPB_SESS_TASK_LOG.SRC_SUCCESS_ROWS,
OPB_SESS_TASK_LOG.SRC_FAILED_ROWS,
OPB_SESS_TASK_LOG.TARG_SUCCESS_ROWS,
OPB_SESS_TASK_LOG.TARG_FAILED_ROWS,
TO_CHAR (OPB_TASK_INST_RUN.START_TIME, 'Day') weekday,
TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24:MI.SS') SESS_START_TIME,
TO_CHAR(OPB_TASK_INST_RUN.END_TIME,'HH24:MI.SS') SESS_END_TIME,
TRUNC((OPB_TASK_INST_RUN.END_TIME - OPB_TASK_INST_RUN.START_TIME) * 1440,2) DURATION_IN_MINS
FROM OPB_SUBJECT,OPB_TASK_INST_RUN,OPB_WFLOW_RUN,OPB_OBJECT_TYPE, OPB_SESS_TASK_LOG   
WHERE
  
  OPB_SUBJECT.SUBJ_ID = OPB_TASK_INST_RUN.SUBJECT_ID
     --AND  OPB_SUBJECT.SUBJ_NAME LIKE '%ICOCRM_%'
     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_TASK_INST_RUN.WORKFLOW_ID
     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_TASK_INST_RUN.WORKFLOW_RUN_ID
     AND OPB_TASK_INST_RUN.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
     AND OPB_OBJECT_TYPE.OBJECT_TYPE = 68
     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_SESS_TASK_LOG.WORKFLOW_ID
     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID
     AND OPB_TASK_INST_RUN.WORKLET_RUN_ID = OPB_SESS_TASK_LOG.WORKLET_RUN_ID
     AND OPB_TASK_INST_RUN.INSTANCE_ID = OPB_SESS_TASK_LOG.INSTANCE_ID
     --AND TRUNC(OPB_TASK_INST_RUN.START_TIME) > TRUNC(SYSDATE -2)
     and OPB_TASK_INST_RUN.INSTANCE_NAME in
   
      (
     
       
      )
ORDER BY 4,1,2,3

12.2         List of workflow run status 




SELECT DISTINCT subject_area,  workflow_name, server_name,
                start_time, end_time, TO_CHAR (start_time, 'Day') weekday,
                TRUNC (TO_CHAR ((end_time - start_time) * 1440)
                      ) elapsedminutes,
                CASE
                   WHEN run_status_code = '1'  THEN 'Succeeded'
                   WHEN run_status_code = '2'  THEN 'Disabled'
                   WHEN run_status_code = '3'  THEN 'Failed'
                   WHEN run_status_code = '4'   THEN 'Stopped'
                   WHEN run_status_code = '5'   THEN 'Aborted'
                   WHEN run_status_code = '6'   THEN 'Running'
                   WHEN run_status_code = '15'  THEN 'Terminated'
                   else 'dummy'
                END AS load_status
           FROM rep_wflow_run
          --WHERE run_status_code not in ( '1')
          where
           TRUNC(start_time) > TRUNC(SYSDATE -8)
         --   TRUNC(start_time) BETWEEN TRUNC(SYSDATE -7) AND TRUNC(SYSDATE)
       ORDER BY 5


12.3         List workflow names


SELECT   SUBJECT_AREA, TASK_TYPE_NAME, TASK_NAME
    FROM REP_ALL_TASKS
   WHERE TASK_TYPE IN (71) 
         --AND SUBJECT_AREA= 'ABC'
ORDER BY 1, 2, 3

12.4         List save workflow log count


SELECT DISTINCT
A.SUBJECT_AREA,
A.TASK_NAME AS WORKFLOW_NAME,
B.ATTR_VALUE AS SAVEWFLOG
FROM
REP_ALL_TASKS A ,
REP_TASK_ATTR  B
WHERE
A.TASK_ID = B.TASK_ID
AND B.ATTR_ID = '4'
AND B.TASK_TYPE = 71
--AND B.ATTR_VALUE NOT IN (8,4) 
-- AND A.SUBJECT_AREA = 'ABC'
ORDER BY 1,2,3


12.5         List workflow log names


SELECT DISTINCT
                SUBJ_NAME,
                WORKFLOW_NAME,
                SUBSTR(LOG_FILE,23,300) AS EXISTING_WFLOGNAME
FROM
                OPB_WFLOW_RUN,
                OPB_SUBJECT
WHERE
                 OPB_WFLOW_RUN.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID
                 -- AND SUBSTR(LOG_FILE,23,300) != CONCAT(LOWER(WORKFLOW_NAME),'.log')
ORDER BY 1,2

12.6         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 71
AND B.ATTR_ID = 12
AND B.ATTR_VALUE <> 1

12.7         List fail_parent_if_task_fails objects


SELECT
   REPOSITORY,
   FOLDER_NAME,
   WORKFLOW_OR_WORKLET,
   TASK_TYPE,
   WORKLET_OR_SESSION,
   FAIL_PARENT_IF_TASK_FAILS
FROM
(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 (BITAND (OPB_TASK_INST.BIT_OPTIONS, 17),17,'SELECTED','NOT SELECTED') AS FAIL_PARENT_IF_TASK_FAILS
FROM OPB_TASK_INST,OPB_OBJECT_TYPE,OPB_TASK,
   OPB_SUBJECT, OPB_REPOSIT_INFO
WHERE OPB_TASK_INST.TASK_TYPE != 62
AND 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.UTC_CHECKIN <> 0
--AND OPB_SUBJECT.SUBJ_NAME NOT LIKE 'WA%'
 )
WHERE FAIL_PARENT_IF_TASK_FAILS <> 'SELECTED'

12.8         List fail_parent_if_task_dont_run objects


SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,WORKLET_OR_SESSION,FAIL_PARENT_IF_TASK_DONT_RUN
FROM
                (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 (BITAND (OPB_TASK_INST.BIT_OPTIONS, 49),49,'SELECTED','NOT SELECTED') AS FAIL_PARENT_IF_TASK_DONT_RUN
                FROM OPB_TASK_INST, OPB_OBJECT_TYPE,OPB_TASK,OPB_SUBJECT,OPB_REPOSIT_INFO
                WHERE OPB_TASK_INST.TASK_TYPE != 62
       AND 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.UTC_CHECKIN <> 0  )
WHERE FAIL_PARENT_IF_TASK_DONT_RUN <> 'SELECTED'
ORDER BY 2

12.9         List is_task_enabled objects


SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,WORKLET_OR_SESSION,IS_TASK_ENABLED
FROM
                (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 != 62
                         AND 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.UTC_CHECKIN <> 0 )
WHERE IS_TASK_ENABLED = 'DISABLED'
ORDER BY 2,3



12.10      List treat_input_links_as objects


SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,
WORKLET_OR_SESSION,TREAT_INPUT_LINKS_AS
FROM
(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 (BITAND (OPB_TASK_INST.BIT_OPTIONS, 3),1,'AND',2, 'OR') AS TREAT_INPUT_LINKS_AS
FROM OPB_TASK_INST,OPB_OBJECT_TYPE,OPB_TASK,OPB_SUBJECT,OPB_REPOSIT_INFO WHERE OPB_TASK_INST.TASK_TYPE != 62
AND 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.UTC_CHECKIN <> 0
)
--WHERE TREAT_INPUT_LINKS_AS = 'OR'
ORDER BY 2,3



12.11      List all workflows whose server is not assigned


SELECT SUBJECT_AREA,WORKFLOW_NAME,SERVER_NAME
FROM REP_WORKFLOWS
WHERE SERVER_NAME IS NULL








12.12      List of sessions last run details



SELECT DISTINCT
(Select reposit_name from opb_reposit where recid =1) as repo,
OPB_SUBJECT.SUBJ_NAME,
OPB_WFLOW_RUN.SERVER_NAME,
OPB_WFLOW_RUN.WORKFLOW_NAME, 
OPB_TASK_INST_RUN.INSTANCE_NAME,
TRUNC(OPB_TASK_INST_RUN.START_TIME) LOAD_DATE,
TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24:MI.SS') SESS_START_TIME,
TO_CHAR(OPB_TASK_INST_RUN.END_TIME,'HH24:MI.SS') SESS_END_TIME,
TRUNC((OPB_TASK_INST_RUN.END_TIME - OPB_TASK_INST_RUN.START_TIME) * 1440,2) DURATION_IN_MINS
FROM OPB_SUBJECT,OPB_TASK_INST_RUN,OPB_WFLOW_RUN,OPB_OBJECT_TYPE, OPB_SESS_TASK_LOG   
WHERE
  
  OPB_SUBJECT.SUBJ_ID = OPB_TASK_INST_RUN.SUBJECT_ID
     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_TASK_INST_RUN.WORKFLOW_ID
     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_TASK_INST_RUN.WORKFLOW_RUN_ID
     AND OPB_TASK_INST_RUN.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
     AND OPB_OBJECT_TYPE.OBJECT_TYPE = 68
     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_SESS_TASK_LOG.WORKFLOW_ID
     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID
     AND OPB_TASK_INST_RUN.WORKLET_RUN_ID = OPB_SESS_TASK_LOG.WORKLET_RUN_ID
     AND OPB_TASK_INST_RUN.INSTANCE_ID = OPB_SESS_TASK_LOG.INSTANCE_ID
     AND OPB_TASK_INST_RUN.INSTANCE_NAME in
     (
's_Load_STG_GCO_BUS_PARTY_CLASSIF_1_00'         )
    and TRUNC(OPB_TASK_INST_RUN.START_TIME) = (
    select MAX(TRUNC(AA.START_TIME)) from OPB_TASK_INST_RUN AA
where AA.INSTANCE_NAME = OPB_TASK_INST_RUN.INSTANCE_NAME
group by AA.INSTANCE_NAME
)
 
ORDER BY 1,2,4



SELECT DISTINCT
       C.SUBJECT_AREA AS FOLDER_NAME,
       A.WORKFLOW_NAME,
       A.START_TIME,
       A.END_TIME,
       A.USER_NAME,B.SESSION_NAME,B.MAPPING_NAME,   
       B.SUCCESSFUL_SOURCE_ROWS AS SOURCE_SUCCESFUL_READ_ROWS,
       B.FAILED_SOURCE_ROWS AS SOURCE_SUCCESFUL_FAILED_ROWS,
       B.SUCCESSFUL_ROWS AS TARGET_SUCCESSFUL_ROWS,
       B.FAILED_ROWS AS TARGET_FAILED_ROWS,
      -- B.FIRST_ERROR_CODE,B.FIRST_ERROR_MSG,
       B.ACTUAL_START
FROM REP_WFLOW_RUN A, REP_SESS_LOG B, REP_SUBJECT C
WHERE A.SUBJECT_ID = B.SUBJECT_ID AND B.SUBJECT_ID = C.SUBJECT_ID
AND   A.WORKFLOW_ID = B.WORKFLOW_ID
AND   A.WORKFLOW_RUN_ID = B.WORKFLOW_RUN_ID
AND   A.START_TIME >= SYSDATE-1
AND   ( B.FAILED_ROWS > 0 OR FIRST_ERROR_CODE <> 0 )
ORDER BY 1,3



12.13      List of workflows currently running


SELECT DISTINCT subject_area,  workflow_name, server_name,
                start_time, end_time, TO_CHAR (start_time, 'Day') weekday,
                TRUNC (TO_CHAR ((end_time - start_time) * 1440)
                      ) elapsedminutes,
                CASE
                   WHEN run_status_code = '1'  THEN 'Succeeded'
                   WHEN run_status_code = '2'  THEN 'Disabled'
                   WHEN run_status_code = '3'  THEN 'Failed'
                   WHEN run_status_code = '4'   THEN 'Stopped'
                   WHEN run_status_code = '5'   THEN 'Aborted'
                   WHEN run_status_code = '6'   THEN 'Running'
                   WHEN run_status_code = '15'
                      THEN 'Terminated'
                END AS load_status
           FROM rep_wflow_run
          WHERE run_status_code = '6'
       ORDER BY 1,2,3




12.14      Check Session with Failed Records and Error Code/Message


SELECT C.SUBJECT_AREA,A.WORKFLOW_NAME,A.START_TIME,A.END_TIME,
       A.USER_NAME,B.SESSION_NAME,B.MAPPING_NAME,          
       B.SUCCESSFUL_ROWS,B.FAILED_ROWS,B.SUCCESSFUL_SOURCE_ROWS,
       B.FAILED_SOURCE_ROWS, B.FIRST_ERROR_CODE,B.FIRST_ERROR_MSG,
       B.ACTUAL_START
FROM REP_WFLOW_RUN A, REP_SESS_LOG B, REP_SUBJECT C
WHERE A.SUBJECT_ID = B.SUBJECT_ID AND B.SUBJECT_ID = C.SUBJECT_ID
AND   A.WORKFLOW_ID = B.WORKFLOW_ID
AND   A.WORKFLOW_RUN_ID = B.WORKFLOW_RUN_ID
AND   A.START_TIME >= SYSDATE-10
AND   ( B.FAILED_ROWS > 0 OR FIRST_ERROR_CODE <> 0 )
ORDER BY 1,3