OWB Monitoring

24 05 2007

Ever have had to look at the result of jobs in an OWB environment? The most complete way to do that is to first start the OWB Browser Listener, which incidently takes about 1 minute on my 1GB XP VMWare virtual machine. Then start the OWB repository browser, another 30 seconds and login (typing 10 seconds, waiting for the first screen to appear; 15 seconds). Then you have to click on the Execution Schedule Report, wich the first time takes about 10 seconds as well. From there on the Repository browser is by far the best way to determine what transpired during the jobs that have run.

But if you want to quickly have a look how everything is running, and not wait 2’05”,  it’s much easier to login to the database and run a query against the runtime repository (i.e. if you have a user that has the privileges to select from the runtime repository). I know a lot of people will have made something along the same lines, but I have made my own. Anybody that is interested is invited to copy the query below and improve upon it.

SELECT    e.execution_name, e.return_result, e.creation_date,
e.last_update_date, e.elapse,
TO_CHAR (TRUNC (SYSDATE, 'DD') + e.elapse / (24 * 3600), 'HH24:MI:SS') AS elapse_format,
wb_rt_constants.to_string (e.audit_status) AS audit_status_symbol, e.audit_status,
DECODE (x.err, NULL, 0, x.err) AS error#,
DECODE (x.sel, NULL, 0, x.sel) AS selected,
DECODE (x.ins, NULL, 0, x.ins) AS inserted,
DECODE (x.upd, NULL, 0, x.upd) AS updated,
DECODE (x.del, NULL, 0, x.del) AS deleted,
DECODE (x.dis, NULL, 0, x.dis) AS discarded,
DECODE (x.mer, NULL, 0, x.mer) AS merged,
DECODE (x.cor, NULL, 0, x.cor) AS corrected,
wb_rt_constants.to_string (m.severity) AS severity_symbol, m.severity, l.plain_text,
p.parameter_name, p.value,
m.creation_date, e.task_object_name, e.task_object_store_name,
e.task_name, e.audit_status,
e.top_level_audit_execution_id, e.audit_execution_id,
FROM      wb_rtv_audit_executions e
LEFT JOIN wb_rtv_audit_messages m
ON        e.audit_execution_id = m.audit_execution_id
LEFT JOIN wb_rtv_audit_message_lines l
ON        l.audit_message_id = m.audit_message_id
(SELECT   e.audit_execution_id, SUM (a.rta_errors) AS err,
SUM (a.rta_select) AS sel, SUM (a.rta_insert) AS ins,
          SUM (a.rta_update) AS upd, SUM (a.rta_delete) AS del,
          SUM (a.rta_discarded) AS dis, SUM (a.rta_merge) AS mer,
          SUM (a.rta_corrections) AS cor
        FROM      wb_rt_audit_executions e
        LEFT JOIN wb_rt_audit a
        ON        e.audit_execution_id = a.rte_id(+)
        GROUP BY  e.audit_execution_id) x
ON        e.audit_execution_id = x.audit_execution_id
ORDER BY  last_update_date DESC;




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

%d bloggers like this: