4 queries found for "Batch Thread Instance"

Retrieve Statistics for Batch Threads

SELECT
  A.BATCH_CD
  ,A.BATCH_NBR
  ,A.BATCH_RERUN_NBR
  ,A.BATCH_THREAD_NBR
  ,A.THREAD_STATUS
  ,B.SCHEDULER_ID
  ,B.REC_PROC_CNT
  ,B.REC_ERR_CNT
  ,C.RUN_STATUS
FROM
  CI_BATCH_THD A,
  CI_BATCH_INST B,
  CI_BATCH_RUN C
WHERE
  A.BATCH_CD = 'INSERT_BATCH_CD'
  AND A.BATCH_NBR = 'INSERT_BATCH_NBR'
  AND A.BATCH_RERUN_NBR = 'INSERT_BATCH_RERUN_NBR'
  AND B.BATCH_CD = A.BATCH_CD
  AND B.BATCH_NBR = A.BATCH_NBR
  AND B.BATCH_RERUN_NBR = A.BATCH_RERUN_NBR
  AND B.BATCH_THREAD_NBR = A.BATCH_THREAD_NBR
  AND C.BATCH_CD = B.BATCH_CD
  AND C.BATCH_NBR = B.BATCH_NBR
  AND C.BATCH_RERUN_NBR = B.BATCH_RERUN_NBR

uploaded by Nuno Rodrigues

Retrieve Statistics for Batch Run

SELECT
  A.BATCH_CD, A.BATCH_NBR, A.BATCH_RERUN_NBR, B.RUN_STATUS
  ,SUM(A.REC_PROC_CNT) AS TOT_REC
  ,SUM(A.REC_ERR_CNT) AS TOT_ERR
FROM
  CI_BATCH_INST A,
  CI_BATCH_RUN B
WHERE
  A.BATCH_CD = 'INSERT_BATCH_CD'
  AND A.BATCH_NBR = 'INSERT_BATCH_NUMBER'
  AND A.BATCH_RERUN_NBR = 'INSERT_BATCH_RERUN_NUMBER'
  AND B.BATCH_CD = A.BATCH_CD
  AND B.BATCH_NBR = A.BATCH_NBR
  AND B.BATCH_RERUN_NBR = A.BATCH_RERUN_NBR
GROUP BY
  A.BATCH_CD, A.BATCH_NBR, A.BATCH_RERUN_NBR, B.RUN_STATUS

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

SQL for BATCH record Count:

select sum(b.rec_proc_cnt), b.batch_nbr from ci_batch_run a , ci_batch_inst b 
where a.batch_cd ='A2-GENTR'and a.batch_cd = b.batch_cd 
and a.run_status ='40' 
and a.batch_bus_dt = '13-MAR-14'   
and a.batch_nbr =b.batch_nbr 
group by b.batch_nbr order by 2

uploaded by Deepak MJ