Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

Last Error Code and Error Message are empty in the body of the Completed Sessions Alerts
Problem Description

Last Error Code and Error message are blank in the body of the Completed Sessions Alerts. The following is the text in the alert body:

Last Error Code: :

Cause

The Last Error Code and Error Message data is not obtained as part of the SQL query for the "PowerCenter Completed Sessions" PMPC SQL Source in both Oracle and MS SQL, which results in no Error Code and Error Message data in the alert bodies. This does not happen for the DB2 SQL query.

Solution

To have the Last Error Code and Error Message data to appear in the alerts body, use the following replacement SQL queries in the "PowerCenter Completed Sessions" PMPC SQL Source Service.

For Oracle

SELECT s.subj_name folder, t.workflow_id, w.workflow_name, t.workflow_run_id, t.task_id session_id, t.instance_name session_name,
t.run_status_code session_run_status_code, decode(t.run_status_code, 1, 'Succeeded', 2, 'Disabled', 3, 'Failed', 4, 'Stopped', 5,
'Aborted', 6, 'Running', 15,'Terminated') session_run_status, l.targ_success_rows successful_target_rows, l.src_success_rows
successful_source_rows, l.targ_failed_rows failed_target_rows, l.src_failed_rows failed_source_rows,
to_char(t. start_time,'yyyy-mm-dd hh24:mi:ss') session_start_time, to_char(t. end_time,'yyyy-mm-dd hh24:mi:ss')
session_end_time, to_char( t.end_time,'yyyy-mm-dd hh24:mi:ss') tstamp , round(24*60*60*( t.end_time-t.start_time))
session_elapsed_secs, round(l.targ_success_rows/(24*60*60* (t.end_time-t.start_time)),2) throughput_rows_per_sec ,
l.first_error_code, CASE WHEN t.run_err_msg is null THEN 'N/A' ELSE t.run_err_msg END last_error, t.run_err_code last_error_code,
to_char(sysdate, 'yyyy-mm-dd HH24:MI:SS') curr_tstamp , w.server_name is_name FROM opb_sess_task_log l, opb_subject s,
opb_task_inst_run t, opb_wflow_run w WHERE t.instance_id = l.instance_id AND t.workflow_id = l.workflow_id AND
t.workflow_run_id = l.workflow_run_id AND t.subject_id = s.subj_id AND w.workflow_run_id = l.workflow_run_id AND
t.task_type = 68 AND t.end_time > t.start_time AND t.end_time > to_date(<<tstamp>>,'yyyy-mm-dd hh24:mi:ss') ORDER BY tstamp

For MS SQL Server

SELECT s.subj_name folder, t.workflow_id, w.workflow_name, t.workflow_run_id, t.task_id session_id, t.instance_name
session_name, t.run_status_code session_run_status_code, CASE WHEN (t.run_status_code = 1) THEN 'Succeeded' WHEN
(t.run_status_code = 2) THEN 'Disabled' WHEN (t.run_status_code = 3) THEN 'Failed' WHEN (t.run_status_code = 4)
THEN 'Stopped' WHEN (t.run_status_code = 5) THEN 'Aborted' WHEN (t.run_status_code = 6) THEN 'Running'
WHEN (t.run_status_code = 15) THEN 'Terminated' END session_run_status, l.targ_success_rows successful_target_rows,
l.src_success_rows successful_source_rows, l.targ_failed_rows failed_target_rows, l.src_failed_rows failed_source_rows, CONVERT(varchar(30),t.start_time,120) session_start_time, CONVERT(varchar(30),t.end_time,120) session_end_time, CONVERT(varchar(30),t.end_time,120) tstamp, DATEDIFF(ss, t.start_time,t.end_time) session_elapsed_secs, CAST (CONVERT(DECIMAL(28,2),l.targ_success_rows)/CONVERT(DECIMAL(28,2), DATEDIFF(ss, t.start_time, t.end_time)) as DECIMAL(5, 2)) throughput_rows_per_sec , l.first_error_code, CASE WHEN t.run_err_msg is null THEN 'N/A' ELSE t.run_err_msg END last_error, t.run_err_code last_error_code, w.server_name is_name, CONVERT(varchar, sysdatetime() , 120) curr_tstamp FROM opb_sess_task_log l, opb_subject s, opb_task_inst_run t, opb_wflow_run w WHERE t.instance_id = l.instance_id AND t.workflow_id = l.workflow_id AND t.workflow_run_id = l.workflow_run_id AND t.subject_id = s.subj_id AND w.workflow_run_id = l.workflow_run_id AND t.task_type = 68 AND t.end_time > t.start_time AND t.end_time > CONVERT(datetime, <<tstamp>> ,120) ORDER BY tstamp

More Information
Applies To
Product: Complex Event Processing
Problem Type:
User Type:
Project Phase:
Product Version: Proactive Monitoring for PowerCenter Operations 2.5
Database:
Operating System:
Other Software:

Reference
Attachments
Last Modified Date:6/7/2013 2:41 AMID:149634
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)