How to get ODI running scenarios Information using SQL query

While running ODI scenarios in Production environment, 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 , for which one of them is having a Warning status ‘note that the run duration is not shown in this case’

#odi

#odi_scenario

#snp_session