Problem:
While running ODI scenarios in Production environemnt, it is not always possible to get the running scenarios names along with duration due to not having the ODI client installed on the monitoring machine.
Solution
As per the below SQL query, the snp_session table, located under work repository schema, has the required information :
Select
sess_no as SESSION_NO,
sess_name as SCENARIO_NAME,
TO_CHAR(sess_beg, 'MM/DD/YY HH:MI:SS') as START_TIME,
TO_CHAR(sess_end, 'MM/DD/YY HH:MI:SS') as END_TIME,
CASE sess_status
When 'D' then 'Done'
When 'R' then 'Running'
When 'W' then 'waiting'
When 'E' then 'Error'
When 'M' then 'Waring'
END AS SESSION_STATUS,
sess_dur as DURATION_SECS,
trunc(sess_dur/60) as DURATION_MINS,
nb_row as NUM_ROWS
From prod_odi_repo.snp_session
Note :Sess_status column shows the current scenarios run status:
D –> Done
R–> Running
W–>waiting
E–> Error
M –> Waring and it will indicate that, job executed successfully with error out records (error out records will store into E$ table)
We have the same scenario run twice , fow which one of them is having a Warning stsatus ‘note that the run duration is not shown in this case:
