Spring Batch 모니터링 SQL

Spring Batch를 적용하여 배치 프로그램을 수행하면 Spring Batch Framework에서 관리하는 테이블에 배치의 수행이력을 저장하고 있다.

이를 이용하여 배치 모니터링하는 SQL은 다음과 같다.

■ Job List

SELECT      A.JOB_INSTANCE_ID,      A.JOB_NAME,      B.JOB_EXECUTION_ID,      B.START_TIME JOB_START_TIME,      B.END_TIME JOB_END_TIME,      B.STATUS JOB_STATUS,     C.JOB_PARAM FROM BATCH_JOB_INSTANCE A,      BATCH_JOB_EXECUTION B,      (SELECT JOB_EXECUTION_ID,              LISTAGG(KEY_NAME || '-' || STRING_VAL, ',')WITHIN GROUP (ORDER BY JOB_EXECUTION_ID) JOB_PARAM       FROM BATCH_JOB_EXECUTION_PARAMS       GROUP BY JOB_EXECUTION_ID) C WHERE 1 = 1 AND   A.JOB_INSTANCE_ID = B.JOB_INSTANCE_ID AND   B.JOB_EXECUTION_ID = C.JOB_EXECUTION_ID   ORDER BY B.START_TIME DESC;

■ Step List

SELECT B.STEP_NAME,        B.START_TIME STEP_START_TIME,        B.END_TIME STEP_END_TIME,        B.STATUS,        B.READ_COUNT,        B.WRITE_COUNT,        B.EXIT_CODE,        B.EXIT_MESSAGE FROM   BATCH_JOB_EXECUTION A,        BATCH_STEP_EXECUTION B   WHERE  1 = 1 AND    A.JOB_EXECUTION_ID = B.JOB_EXECUTION_ID AND    A.JOB_EXECUTION_ID = 37 ORDER BY B.START_TIME;

■일별 통계

SELECT TO_CHAR(A.START_TIME, 'YYYY-MM-DD') START_DAY       ,B.JOB_NAME       ,COUNT(*) CNT FROM   BATCH_JOB_EXECUTION A       ,BATCH_JOB_INSTANCE B WHERE A.JOB_INSTANCE_ID = B.JOB_INSTANCE_ID AND   START_TIME >= '2018-01-01' GROUP BY TO_CHAR(A.START_TIME, 'YYYY-MM-DD'), B.JOB_NAME

■ 월별통계

SELECT TO_CHAR(A.START_TIME, 'YYYY-MM') START_DAY       ,B.JOB_NAME       ,COUNT(*) CNT FROM   BATCH_JOB_EXECUTION A       ,BATCH_JOB_INSTANCE B WHERE A.JOB_INSTANCE_ID = B.JOB_INSTANCE_ID AND   START_TIME >= '2018-01-01' GROUP BY TO_CHAR(A.START_TIME, 'YYYY-MM'), B.JOB_NAME