3 queries found for "Message Log"

Get Thread Start Date Time

SELECT
  B.MESSAGE_PARM
FROM 
  CI_MSG_LOG A
  ,CI_MSG_LOGPARM B 
WHERE
  A.SCHEDULER_ID = 'INSERT_SCHEDULER_ID'
  AND ((MESSAGE_CAT_NBR = 25 AND MESSAGE_NBR = 11913) OR (MESSAGE_CAT_NBR = 11002 AND     MESSAGE_NBR = 90001))
  AND B.SCHEDULER_ID = A.SCHEDULER_ID
  AND B.MESSAGE_SEQ = A.MESSAGE_SEQ
  AND B.PARM_SEQ=3

uploaded by Nuno Rodrigues

Get Thread End Date Time

SELECT
  B.MESSAGE_PARM
FROM 
  CI_MSG_LOG A
  ,CI_MSG_LOGPARM B 
WHERE
  A.SCHEDULER_ID = 'INSERT_SCHEDULER_ID'
  AND ((MESSAGE_CAT_NBR = 25 AND MESSAGE_NBR = 12114) OR (MESSAGE_CAT_NBR = 11002 AND     MESSAGE_NBR = 90003))
  AND B.SCHEDULER_ID = A.SCHEDULER_ID
  AND B.MESSAGE_SEQ = A.MESSAGE_SEQ
  AND B.PARM_SEQ=2

uploaded by Nuno Rodrigues

Run details of batches for a particular day.

SELECT X.BATCH_CD,
       X.BATCH_NBR,
       COUNT(DISTINCT X.BATCH_THREAD_NBR) THREADS,
       X.STATUS,
       SUM(X.PROC_CNT) PROC_CNT,
       SUM(X.ERR_CNT) ERR_CNT,
       STARTTIME.DATETIME START_TM,
       ENDTIME.DATETIME END_TM,
       ROUND(( TO_DATE (ENDTIME.DATETIME, 'YYYY-MM-DD-HH24.MI.SS') - TO_DATE (STARTTIME.DATETIME, 'YYYY-MM-DD-HH24.MI.SS')) * 1440,2) DURATION_MIN,
       ENDTIME.TIME_CD
  FROM 
  (
  SELECT BR.BATCH_CD, BR.BATCH_NBR, BI.BATCH_THREAD_NBR, SUBSTR(L.DESCR,0,1) STATUS, BI.REC_PROC_CNT PROC_CNT, BI.REC_ERR_CNT ERR_CNT
    FROM CISADM.CI_BATCH_RUN BR,
         CISADM.CI_BATCH_THD BT,
         CISADM.CI_BATCH_INST BI,
         CISADM.CI_LOOKUP L,
         CISADM.CI_MSG_LOG ML,
         CISADM.CI_MSG_LOGPARM MLP 
   WHERE BR.BATCH_BUS_DT = to_date('01-07-15','MM-DD-YY') --Search By This Date
     AND BT.BATCH_CD = BR.BATCH_CD
     AND BT.BATCH_NBR = BR.BATCH_NBR
     AND BI.BATCH_CD = BR.BATCH_CD
     AND BI.BATCH_NBR = BT.BATCH_NBR
     AND L.FIELD_NAME = 'RUN_STATUS'
     AND L.FIELD_VALUE = BR.RUN_STATUS
     AND BI.SCHEDULER_ID = ML.SCHEDULER_ID
     AND ML.MESSAGE_NBR IN (11913, 12114, 12115, 90001, 90003, 90004)
     AND BI.SCHEDULER_ID = MLP.SCHEDULER_ID 
     AND MLP.MESSAGE_SEQ = ML.MESSAGE_SEQ 
     AND MLP.MESSAGE_PARM LIKE '2___-__-__-__.__.__' 
  GROUP BY BR.BATCH_CD, BR.BATCH_NBR, BI.BATCH_THREAD_NBR, L.DESCR, BI.REC_PROC_CNT, BI.REC_ERR_CNT 
  ORDER BY BR.BATCH_CD
  ) X,
  (
  SELECT I.BATCH_CD, I.BATCH_NBR, MIN(to_date(M.MESSAGE_PARM,'YYYY-MM-DD-HH24.MI.SS')) DATETIME, DECODE (L.MESSAGE_NBR, 11913,'Start' ,12114,'Ended Normally' ,12115,'Ended Abnormally', 90001,'Start' ,90003,'Ended Normally' ,90004,'Ended Abnormally', 'Unknown') TIME_CD 
    FROM CISADM.CI_BATCH_INST I, 
         CISADM.CI_MSG_LOGPARM M, 
         CISADM.CI_MSG_LOG L, 
         CISADM.CI_BATCH_RUN R 
   WHERE I.SCHEDULER_ID = M.SCHEDULER_ID 
     AND I.SCHEDULER_ID = L.SCHEDULER_ID 
     AND I.BATCH_CD = R.BATCH_CD 
     AND I.BATCH_NBR = R.BATCH_NBR 
     AND M.MESSAGE_SEQ = L.MESSAGE_SEQ 
     AND L.MESSAGE_NBR IN (11913, 90001) 
     AND M.MESSAGE_PARM LIKE '2___-__-__-__.__.__' 
   GROUP BY I.BATCH_CD, I.BATCH_NBR, DECODE (L.MESSAGE_NBR, 11913,'Start' ,12114,'Ended Normally' ,12115,'Ended Abnormally', 90001,'Start' ,90003,'Ended Normally' ,90004,'Ended Abnormally', 'Unknown')
  )STARTTIME,
  (
  SELECT I.BATCH_CD, I.BATCH_NBR, MAX(to_date(M.MESSAGE_PARM,'YYYY-MM-DD-HH24.MI.SS')) DATETIME, DECODE (L.MESSAGE_NBR, 11913,'Start' ,12114,'Ended Normally' ,12115,'Ended Abnormally', 90001,'Start' ,90003,'Ended Normally' ,90004,'Ended Abnormally', 'Unknown') TIME_CD 
    FROM CISADM.CI_BATCH_INST I, 
         CISADM.CI_MSG_LOGPARM M, 
         CISADM.CI_MSG_LOG L, 
         CISADM.CI_BATCH_RUN R 
   WHERE I.SCHEDULER_ID = M.SCHEDULER_ID 
     AND I.SCHEDULER_ID = L.SCHEDULER_ID 
     AND I.BATCH_CD = R.BATCH_CD 
     AND I.BATCH_NBR = R.BATCH_NBR 
     AND M.MESSAGE_SEQ = L.MESSAGE_SEQ 
     AND L.MESSAGE_NBR IN (12114, 12115, 90003, 90004) 
     AND M.MESSAGE_PARM LIKE '2___-__-__-__.__.__' 
   GROUP BY I.BATCH_CD, I.BATCH_NBR, DECODE (L.MESSAGE_NBR, 11913,'Start' ,12114,'Ended Normally' ,12115,'Ended Abnormally', 90001,'Start' ,90003,'Ended Normally' ,90004,'Ended Abnormally', 'Unknown')
  )ENDTIME
WHERE STARTTIME.BATCH_CD = X.BATCH_CD
  AND STARTTIME.BATCH_NBR = X.BATCH_NBR
  AND ENDTIME.BATCH_CD = X.BATCH_CD
  AND ENDTIME.BATCH_NBR = X.BATCH_NBR
GROUP BY X.BATCH_CD, 
  X.BATCH_NBR, 
  X.STATUS, 
  ROUND ( ( TO_DATE (ENDTIME.DATETIME, 'YYYY-MM-DD-HH24.MI.SS') - TO_DATE (STARTTIME.DATETIME, 'YYYY-MM-DD-HH24.MI.SS')) * 1440,2), 
  ENDTIME.TIME_CD, 
  STARTTIME.DATETIME, 
  ENDTIME.DATETIME
ORDER BY X.BATCH_CD
;

uploaded by Vinayak Gadgil