Hi All,
I have one requirement like I want to see the failed step name and package name and date and error message using repository tables in ODI 11G.
For this I used below query which I found in google,but it is not working because I dont have data in the table "SNP_EXP_TXT".
SELECT ' The '||CASE
WHEN SSS.STEP_TYPE='F' THEN 'INTERFACE'
WHEN SSS.STEP_TYPE='VD' THEN 'VARAIBLE DECLARATION'
WHEN SSS.STEP_TYPE='VS' THEN 'SET/INCREMENT VARAIBLE'
WHEN SSS.STEP_TYPE='VE' THEN 'EVALUATE VARIABLE'
WHEN SSS.STEP_TYPE='V' THEN 'REFRESH VARIABLE'
WHEN SSS.STEP_TYPE='T' THEN 'PROCEDURE'
WHEN SSS.STEP_TYPE='OE' THEN 'OS COMMAND'
WHEN SSS.STEP_TYPE='SE' THEN 'ODI TOOL'
WHEN SSS.STEP_TYPE='RM' THEN 'REVERSE ENGINEERING MODEL'
WHEN SSS.STEP_TYPE='CM' THEN 'CHECK MODEL'
WHEN SSS.STEP_TYPE='CS' THEN 'CHECK SUB MODEL'
WHEN SSS.STEP_TYPE='CD' THEN 'CHECK DATA STORE'
WHEN SSS.STEP_TYPE='JM' THEN 'JOURNALIZE MODEL'
WHEN SSS.STEP_TYPE='JD' THEN 'JOURNALIZE DATA STORE'
END||' '||SSS.STEP_NAME||
' , has failed due to - '||
SNET.TXT
FROM "SNP_SESSION" SS,
"SNP_SESS_STEP" SSS,
"SNP_EXP_TXT" SNET
WHERE SSS.SESS_NO=SS.SESS_NO
AND SNET.I_TXT=SS.I_TXT_SESS_MESS
and SNET.TXT_ORD=0
--AND SS.PARENT_SESS_NO ="SESS_NO"
I found one tabel has the data but dont have the package name.
select SCEN_NO,SCEN_RUN_NO,STEP_NAME,STEP_BEG,STEP_END,
STEP_STATUS,LSCHEMA_NAME,MOD_CODE,TABLE_NAME,SUBSTR(ERROR_MESSAGE,0,400)
from SNP_STEP_REPORT
where STEP_BEG>='28-JUL-16'
AND STEP_STATUS='E'.
Can some one please let me know how to get the required details.
Thanks,
Narasimha