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: