-- it is sql for monitoring all ODI Sessions status for 5 days fromODI Repository tables based SNP_SESSION and SNP_PLAN_AGENT.
-- if you need, get it.
-- it contains the session info not loadplan
-- it is based of ODI 11.1.1.7.0 repository
-- written by cheon on 2015/06/12
CREATE OR REPLACE VIEW V_ETL_MNT AS
WITH A AS (
SELECT SCEN_NAME, SCEN_VERSION, MAX(SESS_BEG) SESS_BEG
, MAX(DECODE(RN,1,SESS_STATUS)) R1, MAX(DECODE(RN,2,SESS_STATUS)) R2, MAX(DECODE(RN,3,SESS_STATUS)) R3, MAX(DECODE(RN,4,SESS_STATUS)) R4, MAX(DECODE(RN,5,SESS_STATUS)) R5
, MAX(DECODE(RN,1,GAPPM)) G1, MAX(DECODE(RN,1,DURPM)) D1, MAX(DECODE(RN,1,NB_ROW)) C1
, MAX(DECODE(RN,1,MSG)) MSG, MAX(DECODE(RN,1,CONTEXT_CD)) CONTEXT_CD, MAX(DECODE(RN,1,M_AGT_NM)) M_AGT_NM, MAX(DECODE(RN,1,AGT_NM)) AGT_NM
, MAX(SESS_NO) SESS_NO
, MAX(DECODE(SESS_STATUS,'R',SESS_NO)) SESS_NO_R
FROM (SELECT SESS_NO, SCEN_NAME, SCEN_VERSION, SESS_BEG, SESS_END
, DURPM, GAPPM, SESS_STATUS, SESS_RC, NB_ROW, M_AGT_NM, AGT_NM, CONTEXT_CD, MSG, SS_CNT, RN
FROM (SELECT SESS_NO, SCEN_NAME, SCEN_VERSION, SESS_BEG, SESS_END
, ROUND(SESS_DUR/60,1) DURPM, ROUND((SESS_BEG - LAG(SESS_BEG) OVER (PARTITION BY SCEN_NAME, SCEN_VERSION ORDER BY SESS_NO))*1440) GAPPM
, SESS_STATUS, SESS_RC, NB_ROW, MASTER_AGENT_NAME M_AGT_NM, AGENT_NAME AGT_NM, CONTEXT_CODE CONTEXT_CD, DBMS_LOB.SUBSTR(ERROR_MESSAGE,800,1) MSG
, COUNT(*) OVER (PARTITION BY SCEN_NAME, SCEN_VERSION) SS_CNT, ROW_NUMBER() OVER (PARTITION BY SCEN_NAME, SCEN_VERSION ORDER BY SESS_NO DESC) RN
FROM SNP_SESSION
WHERE SCEN_VERSION IS NOT NULL
AND LAST_DATE > SYSDATE - 5)
WHERE RN <= 5)
GROUP BY SCEN_NAME, SCEN_VERSION
), B AS (
SELECT SCEN_FOLDER_NAME, SCEN_NO, A.SCEN_NAME, A.SCEN_VERSION, LAGENT_NAME, CONTEXT_CODE, STAT_PLAN, S_TYPE
,REPLACE(MAX(DECODE(S_TYPE,'H',TO_CHAR(S_MINUTE,'00'),'D',TO_CHAR(S_HOUR,'00')||':'||TO_CHAR(S_MINUTE,'00')
,'M',TO_CHAR(S_DAY,'00')||'^'||TO_CHAR(S_HOUR,'00')||':'||TO_CHAR(S_MINUTE,'00'),'W',S_WEEK_DAY)),' ','') B_DESC
FROM SNP_PLAN_AGENT A ,
SNP_SCEN S ,
SNP_SCEN_FOLDER F
WHERE 1=1
AND A.SCEN_NAME = S.SCEN_NAME
AND A.SCEN_VERSION = S.SCEN_VERSION
AND S.I_SCEN_FOLDER = F.I_SCEN_FOLDER
GROUP BY SCEN_FOLDER_NAME, SCEN_NO, A.SCEN_NAME, A.SCEN_VERSION, LAGENT_NAME, CONTEXT_CODE, STAT_PLAN, S_TYPE
)
SELECT SCEN_FOLDER_NAME 프로젝트,
B.SCEN_NAME SCEN_NAME,
B.SCEN_VERSION VER,
NVL(B.SCEN_NAME, A.SCEN_NAME) SESS_NAME,
SESS_BEG 수행일시,
STAT_PLAN 활성화,
S_TYPE 배치,
B_DESC 일정,
R1 결과, R2, R3, R4, R5,
G1 최근주기,
D1 수행시간,
C1 적재건수, SUBSTR(MSG,INSTR(MSG,'Exception:')+LENGTH('Exception:'),INSTR(SUBSTR(MSG,INSTR(MSG,'Exception:')+LENGTH('Exception:')),CHR(10))-1) 오류메세지,
A.CONTEXT_CD 컨텍스트명,
M_AGT_NM 일정에이젼트,
AGT_NM 실행에이젼트,
SESS_NO_R 세션번호_R
FROM A FULL OUTER JOIN B
ON (A.SCEN_NAME = B.SCEN_NAME AND B.SCEN_VERSION = B.SCEN_VERSION)
ORDER BY 프로젝트 DESC NULLS LAST, 배치, SESS_NAME, 활성화, 결과;