informatica how-tos

INFORMATICA HOW-TO's

1.0.0 How-to check the workflow session runs

Use the following sql agains the repo

--LIST THE TABLES FROM YESTERDAY WHICH HAVE BEEN LOADED

SELECT

   REP_SESS_TBL_LOG.START_TIME

 , REP_SESS_TBL_LOG.END_TIME

 , REP_SESS_TBL_LOG.SUBJECT_AREA                                                AS "IMATICA_FOLDER"

 , OPB_TASK.TASK_NAME                                                           as "WORKFLOW_NAME"

 , REP_SESS_TBL_LOG.SESSION_NAME

 , REP_SESS_TBL_LOG.TABLE_NAME                                                  AS "TGT_TABLE_NAME"

 , REP_SESS_TBL_LOG.SUCCESSFUL_ROWS

 , REP_SESS_TBL_LOG.SUCCESSFUL_AFFECTED_ROWS

 , REP_SESS_TBL_LOG.FAILED_ROWS

 , REP_SESS_TBL_LOG.LAST_ERROR

 , REP_SESS_TBL_LOG.LAST_ERROR_CODE

 , REP_SESS_TBL_LOG.GROUP_NAME

 , REP_SESS_TBL_LOG.THROUGHPUT

 , REP_SESS_TBL_LOG.TYPE_ID

 FROM REP_SESS_TBL_LOG

 INNER JOIN OPB_TASK ON REP_SESS_TBL_LOG.WORKFLOW_ID = OPB_TASK.TASK_ID

 WHERE 1=1

 AND REP_SESS_TBL_LOG.START_TIME > SYSDATE - 2

 AND SUCCESSFUL_ROWS > 0

 and OPB_TASK.TASK_NAME = 'wflw_T24_Exchange_Rates_OFSML_extract'

ORDER BY REP_SESS_TBL_LOG.START_TIME DESC

  ; 2.0.0 How-to list the informatica objects per folder

Use the following sql agains the repo

SELECT

F.SUBJ_NAME AS FOLDER_NAME,

WF.TASK_ID AS WORKFLOW_ID ,

WF.TASK_NAME AS WORKFLOW_NAME,

SE.INSTANCE_NAME AS SESSION_NAME,

M.MAPPING_NAME AS MAPPING_NAME,

SRC.INSTANCE_NAME AS SOURCE_NAME,

TGT.INSTANCE_NAME AS TARGET_NAME

FROM

OPB_SUBJECT F,

OPB_TASK WF,

(SELECT

WORKFLOW_ID,

INSTANCE_ID,

TASK_ID,

TASK_TYPE,

INSTANCE_NAME,

MAX(VERSION_NUMBER)

FROM OPB_TASK_INST SESS

WHERE

SESS.TASK_TYPE=68

GROUP BY

WORKFLOW_ID,INSTANCE_ID,TASK_ID,TASK_TYPE,INSTANCE_NAME) SE,

(SELECT SESSION_ID,MAPPING_ID,MAX(VERSION_NUMBER) FROM OPB_SESSION GROUP BY SESSION_ID,MAPPING_ID) S,

opb_mapping m,

(SELECT MAPPING_ID,INSTANCE_NAME ,WIDGET_TYPE,MAX(VERSION_NUMBER) from opb_widget_inst GROUP BY MAPPING_ID,INSTANCE_NAME,WIDGET_TYPE) SRC,

(SELECT MAPPING_ID,INSTANCE_NAME ,WIDGET_TYPE,MAX(VERSION_NUMBER) from opb_widget_inst GROUP BY MAPPING_ID,INSTANCE_NAME,WIDGET_TYPE) TGT

where WF.IS_VISIBLE = 1

AND WF.SUBJECT_ID = F.SUBJ_ID

AND SE.WORKFLOW_ID = WF.TASK_ID

AND WF.TASK_TYPE = 71

AND se.task_id = s.session_id

AND s.mapping_id = m.mapping_id

AND M.IS_VISIBLE = 1

AND SRC.MAPPING_ID=m.mapping_id

AND SRC.WIDGET_TYPE=1

AND TGT.MAPPING_ID=m.mapping_id

AND TGT.WIDGET_TYPE=2

-- AND WF.SUBJECT_ID = '28'

-- AND WF.TASK_NAME = 'wf_FACT_PLN_KOHD_HY_APPLICATION_LE'

ORDER BY FOLDER_NAME , WORKFLOW_NAME , SESSION_NAME

; 3.0.0 How-to list all the failed sessions for workflows

Use the following sql agains the repo

 SELECT

    OPB_TASK_INST_RUN.SUBJECT_ID

  , OPB_SUBJECT.SUBJ_NAME

  , OPB_TASK_INST_RUN.WORKFLOW_ID

  , OPB_TASK.TASK_NAME

  , OPB_TASK_INST_RUN.WORKFLOW_RUN_ID

  , OPB_TASK_INST_RUN.START_TIME

  , OPB_TASK_INST_RUN.END_TIME

 , OPB_TASK_INST_RUN.INSTANCE_NAME

 ,  decode (RUN_STATUS_CODE

 ,  1  ,  'Succeeded'

 ,  2  ,   'Disabled'

 ,  3  ,   'Failed'

 ,  4  ,   'Stopped'

 ,  5  ,   'Aborted'

 ,  6  ,   'Running'

 ,  15 ,  'Terminated')  Status

 , OPB_TASK_INST_RUN.TASK_ID

 , OPB_TASK_INST_RUN.TASK_TYPE

 , OPB_TASK_INST_RUN.RUN_ERR_CODE

 , OPB_TASK_INST_RUN.RUN_ERR_MSG

 , OPB_TASK_INST_RUN.RUN_STATUS_CODE

 , OPB_TASK_INST_RUN.RUN_MODE

 , OPB_TASK_INST_RUN.VERSION_NUMBER

 , OPB_TASK_INST_RUN.SERVER_ID

 , OPB_TASK_INST_RUN.SERVER_NAME

 , OPB_TASK_INST_RUN.FRAGMENT_ID

 , OPB_TASK_INST_RUN.SERVER_NODE_ID

 , OPB_TASK_INST_RUN.SERVER_NODE_NAME

from OPB_TASK_INST_RUN

INNER JOIN OPB_SUBJECT ON OPB_SUBJECT.SUBJ_ID = OPB_TASK_INST_RUN.SUBJECT_ID

INNER JOIN OPB_TASK ON OPB_TASK_INST_RUN.WORKFLOW_ID = OPB_TASK.TASK_ID

WHERE 1=1

AND RUN_STATUS_CODE NOT IN ( '1' , '2' , '6' )

AND START_TIME >= (sysdate -1) 4.0.0 How-to list who saved what objects in the informatica repository

Use the following sql agains the repo

-- get who has saved what from the informatica repository

   SELECT

      REP_VERSION_PROPS.user_id

    , REP_USERS.user_name

    , REP_VERSION_PROPS.OBJECT_ID

      -- , REP_VERSION_PROPS.object_type

    , OPB_SUBJECT.subj_name

    , REP_VERSION_PROPS.object_name

    , OPB_OBJECT_TYPE.object_type_name

    , to_date(REP_VERSION_PROPS.last_saved, 'MM/DD/YYYY HH24:MI:SS') AS LAST_UPDATE_TIME

   FROM REP_VERSION_PROPS JOIN REP_USERS

   ON ( REP_VERSION_PROPS.USER_ID = REP_USERS.USER_ID )

   JOIN OPB_SUBJECT

   ON ( REP_VERSION_PROPS.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID )

   JOIN OPB_OBJECT_TYPE

   ON ( REP_VERSION_PROPS.OBJECT_TYPE = OPB_OBJECT_TYPE.object_type_id )

   WHERE 1=1

   -- define the types of objects to track

   AND REP_VERSION_PROPS.object_type IN (1,2,21,65,68,70,71)

   -- filter by the time saved

   AND to_date(REP_VERSION_PROPS.last_saved, 'MM/DD/YYYY HH24:MI:SS') > (sysdate -12)

   -- filter by informatica folder

   and SUBJ_NAME = '3RD_PARTY'

   -- filter by

   -- AND OBJECT_NAME = 's_m_INTIME_CTL_CHECK'

   ORDER BY LAST_UPDATE_TIME DESC

   ;