INFORMATICA's meta Data Management II: Running status information query

The project of two buddies to do about the running status metadata of the two job is cool, the recent period of good health, their reference to learn at the same time to do log records, hehe, in order to save ink, related metadata table structure and mapping design do not introduce (if you have questions welcome message discussion), Just do the thinking to comb the following:

1. The corresponding wkf,tast is associated with the WKF ID and the task ID, thus forming a report on the session run status as follows:

SELECT Opb_subject. Subj_name,

Opb_wflow_run. Workflow_name,

Opb_task_inst_run. instance_name,

To_char (Opb_task_inst_run. Start_time, ' Yyyy-mm-dd HH24:mi:ss ') start_time,

To_char (Opb_task_inst_run. End_time, ' Yyyy-mm-dd HH24:mi:ss ') End_time,

Opb_task_inst_run. Run_err_msg,

Opb_task_inst_run. Task_name,

Opb_swidginst_log. Instance_name Instance_name_log,

Opb_swidginst_log. Widget_type,

Applied_rows Successful_rows,

Affected_rows Successful_affected_rows,

Rejected_rows Failed_rows,

REPLACE (LAST_ERR_MSG,CHR ('), Chr (') ') last_error

From INFREP_V8. Opb_task_inst_run Opb_task_inst_run, Infrep_v8. Opb_wflow_run Opb_wflow_run, Infrep_v8. Opb_subject Opb_subject, Infrep_v8. Opb_swidginst_log 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 Trunc (Opb_task_inst_run. start_time) = trunc (SYSDATE-1, ' DD ')

The query displays the session health report from the previous day, which allows the system to monitor the health of the job.

2. The following query is used to record the health monitoring report for WKF/

SELECT Rep_workflows.server_name, Rep_workflows.subject_area,

Rep_workflows.workflow_name, Rep_workflows.scheduler_name,

Case

When opb_schedule_logic.user_logic_type = 1

and OPB_SCHEDULE_LOGIC.FREQUENCY_INTERVL = 1

Then ' Daily Job '

When (Opb_schedule_logic.user_logic_type = 1

and OPB_SCHEDULE_LOGIC.FREQUENCY_INTERVL > 1

and OPB_SCHEDULE_LOGIC.FREQUENCY_INTERVL < 8

)

Then ' Weekly Job '

ELSE ' Monthly Job '

END Job_type,

REPLACE (SUBSTR (Rep_workflows.start_time,

-5,

5

),

‘/‘,

‘:‘

) Schedule_time,

Case

When Opb_wflow_run.run_err_code = 0 and opb_wflow_run.end_time are NOT null

Then ' completed '

When Opb_wflow_run.run_err_code = 0 and opb_wflow_run.end_time are null

Then ' Running '

When Opb_wflow_run.run_err_code >= 1

Then ' Failed '

ELSE ' Not Start '

END status,

Opb_wflow_run.start_time, Opb_wflow_run.end_time,

--rep_workflows.start_time,

Opb_wflow_run.run_err_code

From Rep_workflows left JOIN Opb_wflow_run

On opb_wflow_run.workflow_id = rep_workflows.workflow_id

and TRUNC (opb_wflow_run.start_time) = TRUNC (sysdate, ' DD ')

JOIN Opb_schedule_logic

On opb_schedule_logic.scheduler_id = rep_workflows.scheduler_id

and Opb_schedule_logic.version_number in (

SELECT MAX (L.version_number)

From Opb_schedule_logic l

WHERE opb_schedule_logic.scheduler_id =l.scheduler_id)

WHERE rep_workflows.run_options > 1

ORDER by 1, 2, 3

The above is only the session and WKF level of monitoring metadata using query, if you need to set to automatic daily/weekly generate reports need to add source metadata table and Sqlqualifer component (generally also filter out the successful state, with the filer component) into the target table

Then add a mailtast to send the run Exception Status report form to the mail every day.

INFORMATICA's meta Data Management II: Running status information query