Informatica Monitoring using SQL Scripts

Informatica offers the Monitor and the Log Files to check how long certain Workflows have run. This is very nice, but sometimes one wants to have the runtime Statistics in a better format. This can be achived using the following SQL that querys the internal Informatica Table Repository (The Informatica Database Schema):

The following Contains the Workflow runtime, but also how long the Source Qualifier took and how long the Target load Took:

 
SELECT OPB_SWIDGINST_LOG.INSTANCE_NAME det,
to_char(OPB_SWIDGINST_LOG.START_TIME, 'yyyy-mm-dd hh24:mi:ss') start_det,
to_char(OPB_SWIDGINST_LOG.END_TIME, 'yyyy-mm-dd hh24:mi:ss') ende_det,
round((OPB_SWIDGINST_LOG.END_TIME - OPB_SWIDGINST_LOG.START_TIME) *24*60*60) as det_laufzeit,
sum(round((OPB_SWIDGINST_LOG.END_TIME - OPB_SWIDGINST_LOG.START_TIME) *24*60*60)) over (partition by OPB_WFLOW_RUN.workflow_run_id) det_sum_lfz,
round((OPB_TASK_INST_RUN.END_TIME - OPB_TASK_INST_RUN.START_TIME) *24*60*60) as wf_laufzeit,
OPB_WFLOW_RUN.WORKFLOW_NAME,
to_char(OPB_TASK_INST_RUN.START_TIME, 'yyyy-mm-dd hh24:mi:ss') start_wf,
to_char(OPB_TASK_INST_RUN.END_TIME, 'yyyy-mm-dd hh24:mi:ss') ende_wf,
OPB_WFLOW_RUN.workflow_run_id
FROM OPB_TASK_INST_RUN, OPB_WFLOW_RUN, OPB_SUBJECT, OPB_SWIDGINST_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_SWIDGINST_LOG.WORKFLOW_RUN_ID = OPB_TASK_INST_RUN.WORKFLOW_RUN_ID
AND OPB_SWIDGINST_LOG.TASK_INSTANCE_ID = OPB_TASK_INST_RUN.INSTANCE_ID
AND OPB_SWIDGINST_LOG.WIDGET_TYPE IN (3, 2)
--AND workflow_NAME like '%WORFLOW%'
and OPB_TASK_INST_RUN.start_time >'01-01-14'
order by OPB_WFLOW_RUN.workflow_run_id desc, OPB_TASK_INST_RUN.start_time desc,OPB_SWIDGINST_LOG.START_TIME asc;

With the Result-set in SQL Developer looking like:

Informatica Laufzeiten

The following is a subset of the above and only shows the Workflow runtimes:

 SELECT distinct OPB_WFLOW_RUN.WORKFLOW_NAME,
 to_char(OPB_TASK_INST_RUN.START_TIME, 'yyyy-mm-dd hh24:mi:ss') start_wf,
 to_char(OPB_TASK_INST_RUN.END_TIME, 'yyyy-mm-dd hh24:mi:ss') ende_wf,
 round((OPB_TASK_INST_RUN.END_TIME - OPB_TASK_INST_RUN.START_TIME) *24*60*60) as wf_laufzeit,
 OPB_WFLOW_RUN.workflow_run_id
 FROM
 OPB_TASK_INST_RUN, OPB_WFLOW_RUN, OPB_SUBJECT, OPB_SWIDGINST_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_SWIDGINST_LOG.WORKFLOW_RUN_ID = OPB_TASK_INST_RUN.WORKFLOW_RUN_ID
 AND OPB_SWIDGINST_LOG.TASK_INSTANCE_ID = OPB_TASK_INST_RUN.INSTANCE_ID
 AND OPB_SWIDGINST_LOG.WIDGET_TYPE IN (3, 2)
 --AND workflow_NAME like '%WORFLOW%'
 and OPB_TASK_INST_RUN.start_time >'01-01-14'
 order by OPB_WFLOW_RUN.workflow_run_id desc;

With the Result-set in SQL Developer looking like:

Informatica Laufzeiten WF

This shows how the Informatica Internal Repository can be used to easily query for Runtime Information.

3 thoughts on “Informatica Monitoring using SQL Scripts

  1. Hi therе superb blog! Dоes running a blog liқe tһіs tаke a
    lot of work? I’ve virtually no knowledge οf coding but I haԁ
    been hoping to start my own blog soon. Ꭺnyhow, if yοu have any suggestions or tips for new blog owners please
    share. І knoѡ thiѕ iѕ оff subject but I simply needed tо ask.
    Thɑnks ɑ ⅼot!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.