ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Scenario Report

This below query provide details about each scenario and its associated session  run details in the operator . This enables to get details about each run whether it was successful , record processed , inserted and updated and duration in hours and other statistical information about each scenario.

SELECT
SS.SCEN_NAME AS SCENARIO_NAME ,
SS.SCEN_VERSION AS SCEN_VERSION ,
SSR.SCEN_RUN_NO AS SESS_NO,
SSR.SESS_DUR AS DUR,
CASE
WHEN SSR.SESS_DUR <61 THEN SSR.SESS_DUR||' SEC'
WHEN SSR.SESS_DUR BETWEEN 61 AND 3600 THEN (SSR.SESS_DUR/60)||' MIN'
WHEN SSR.SESS_DUR > 3600 THEN (TRUNC(SSR.SESS_DUR/3600)||' HR '||TRUNC(MOD(SSR.SESS_DUR,3600)/60)||' MIN')
END AS DURATION,
-- The above CASE statement is to show the duraion in hrs , min and seconds
-- rather than manual calculating when the session run for multiple hours
DECODE(SSR.SESS_STATUS,'D','SUCCESSFUL'
,'E','FAILED')AS STATUS,
SSR.NB_ROW AS ROW_PROCESSED,
SSR.NB_INS AS ROWS_INSERTED,
SSR.NB_UPD AS ROWS_UPDATED,
SSR.NB_DEL AS ROWS_DELETED,
SSR.NB_ERR AS ERROR_RECORD,
CASE
WHEN TO_CHAR(SSR.SESS_END,'MM-DD-YYYY')=TO_CHAR(SSR.SESS_BEG,'MM-DD-YYYY')
THEN TO_CHAR(SSR.SESS_END,'MM-DD-YYYY')
ELSE TO_CHAR(SSR.SESS_BEG,'MM-DD-YYYY')||'-TO-'||TO_CHAR(SSR.SESS_END,'MM-DD-YYYY')
END AS SESSION_RAN_DATE
FROM SNP_SCEN SS
LEFT OUTER JOIN SNP_SCEN_REPORT SSR ON SS.SCEN_NO=SSR.SCEN_NO
WHERE SSR.SCEN_RUN_NO IS NOT NULL
ORDER BY SCENARIO_NAME,SSR.SESS_END

image

Leave a Reply

Required fields are marked *.