Information OPB Metadata tables to track session performance

Informatica metadata repository stores and maintains information about all the objects in Informatica. They contain details of connection information, users, folders, mappings, sources, targets etc. These information can serve many purposes while accessed through external SQL query.

Scenerio 1:

Want to know the average time a workflow has taken to complete.

Code syantax :

SELECT workflow_name, server_name,TO_CHAR (start_time, 'MM/DD/YY HH24:MI:SS'),TO_CHAR (end_time, 'MM/DD/YY HH24:MI:SS'),(end_time - start_time) * 24 AS diff_time_hrs
FROM pm_repo_r.opb_wflow_run
WHERE workflow_name = 'wkfl_cm_job_dly_new' -- enter the name of the workflow ORDER BY start_time DESC;














Scenerio 2:

Want to know in a worflow the tasks that run and the time taken by each task to complete, if it is a session we can know the start time and the end time of the session, source rows read, target rows written, any failed rows, transformation errors and the time taken for each of them. This gives us a good insight on session performance. We have build a report on top of this data to give us a graphical represenatation of our production jobs.

We first need to find the folder we are intrested for which we would like to pull performance data.

select * from pm_repo_r.opb_subject folder; -- get the folder name from SUBJ_NAME col and SUBJ_ID which is the folder id which will be paramater for the next query

SELECT DISTINCT folder.subj_name "FLDR_NAME",workflow.workflow_name "WRFL_NM",timing.instance_name "SESS/WKLT_NAME",timing.start_time "START_TIME_S",timing.end_time "END_TIME_S", LOG.src_success_rows "SRC_ROWS",LOG.targ_success_rows "TGT_ROWS",LOG.targ_failed_rows "FAIL_TGT_ROWS",total_trans_errs "T/R_ERRORS",FLOOR ((timing.end_time - timing.start_time) * 60 * 60 * 24

) "DUR_SEC",FLOOR ((timing.end_time - timing.start_time) * 60 * 24

) "DUR_MIN"

FROM

pm_repo_r.opb_task_inst_run timing,

pm_repo_r.opb_sess_task_log LOG,

pm_repo_r.opb_wflow_run workflow,

pm_repo_r.opb_subject folder

WHERE folder.subj_id IN (132) -- --'folder ID'

AND timing.start_time >=TO_DATE ('06/15/13 16:30:00', 'MM/DD/YY HH24:MI:SS')

AND timing.end_time <=TO_DATE ('06/20/13 07:21:00', 'MM/DD/YY HH24:MI:SS')

AND UPPER (timing.instance_name) NOT LIKE '%START%'

AND timing.workflow_run_id = workflow.workflow_run_id(+)

AND timing.workflow_run_id = LOG.workflow_run_id(+)

AND timing.instance_id = LOG.instance_id(+)

AND workflow.subject_id = folder.subj_id(+)

AND workflow.workflow_name = '<WORKFLOWNAME>'--'workflowname

ORDER BY 4 DESC;