Some useful SQL below, to quickly find status and any warnings/error messages from a running OBIEE Agent
select i.job_id,
i.instance_id,
J.name AS Agent_Name,
i.status status_code,
to_char(i.begin_ts,’DD/MM/YYYY HH24:MI:SS’) begin_ts,
i.status as STATUS,
case i.status
when 0 then ‘Completed’
when 1 then ‘Running’
when 2 then ‘Failed’
when 3 then ‘Cancelled’
when 4 then ‘Timed-out’
when 5 then ‘Warning’
when 6 then ‘Try Again’
else ‘Unknown’
end status_description,
error_message.ERROR_MSG
from bi12c_biplatform.s_nq_job j
left join bi12c_biplatform.s_nq_instance i on j.job_id = i.job_id
left join (select
JOB_ID ,
INSTANCE_ID,
replace(
replace(
replace(
replace(
max( decode(val_number, 1, ERROR_MSG_TEXT, null) ) ||
max( decode(val_number, 2, ERROR_MSG_TEXT, null) ) ||
max( decode(val_number, 3, ERROR_MSG_TEXT, null) ) ||
max( decode(val_number, 4, ERROR_MSG_TEXT, null) ) ||
max( decode(val_number, 5, ERROR_MSG_TEXT, null) ) ||
max( decode(val_number, 6, ERROR_MSG_TEXT, null) ) ||
max( decode(val_number, 7, ERROR_MSG_TEXT, null) ) ||
max( decode(val_number, 8, ERROR_MSG_TEXT, null) ) ||
max( decode(val_number, 9, ERROR_MSG_TEXT, null) ) ||
max( decode(val_number, 10, ERROR_MSG_TEXT, null) ),
‘$_\s_$’,’ ‘),
‘$_\r_$’,’ ‘),
‘$_\n_$’,’ ‘),
‘$_\,$’,’,’) AS ERROR_MSG
from
( select
JOB_ID,
INSTANCE_ID,
row_number() over ( partition by JOB_ID, INSTANCE_ID order by
RELATIVE_ORDER ) as val_number ,
ERROR_MSG_TEXT
from bi12c_biplatform.S_NQ_ERR_MSG
where delete_flg = 0
) ta
GROUP BY JOB_ID, INSTANCE_ID ) error_message on i.job_id = error_message.job_id and i.instance_id = error_message.instance_id
where 1=1
–and i.status in (2,4)
and (i.begin_ts – 8/24) > trunc(sysdate);