Spring Batch 모니터링 SQL

Spring Framrwork

Spring Batch 모니터링 SQL

gregorio 2018. 2. 3. 09:52

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