Whether you are a lone developer building an ETL application or a lead whose team working on for a next big release, here are few simple scenarios,which will help you to build need for capturing workflow status.
Scenario to capture informatica workflow status
1: You are developing a module or data mart, and you are in pre- production phase where data is being validated by clients. you have around 400+ daily loads which happens through 300+ workflows to push the data. One consolidated report being a lead can help you diagnose the overall situation before reporting back to client about successful load.
2: You have developed code which loads around 1000+ same structured files. you are in system integration testing where your code is being tested for robustness. with this you can see overall status before anything goes wrong badly.
3: One of your significant upstream system is being sunset, decision has been taken that new system will process data in parallel (to Current Production system) and apple to apple comparison will be done to plan cut-off of earlier upstream system. Here you can predict data deviation with this report well in advance and can start investigation.
Repository metadat view REP_WFLOW_RUN or OPB_WFLOW_RUN:
This is informatica metadata view which keeps the run statistics for all workflows by folder. Few elements which can be used.
Informatica workflow status SQL Query
SELECT WORKFLOW_NAME, SERVER_NAME, RUN_ERR_CODE, RUN_ERR_MSG, SUBJECT_AREA, decode (RUN_STATUS_CODE,1 , 'Succeeded', 2,'Disabled', 3,'Failed', 4,'Stopped', 5,'Aborted', 6,'Running', 15,'Terminated') Status from infa_metadata_schema.REP_WFLOW_RUN where START_TIME>= sysdate-2 and END_TIME<=sysdate order by SUBJECT_AREA,WORKFLOW_NAME,RUN_STATUS_CODE;
You need to change the schema name accordingly in above query.
Further,create one workflow which runs after every four hours and send an email to you with below report.