Useful SQL queries for spring-batch metadata(MySql)

main.sql

-- select job-time of the last 4  COMPLETED jobs 
SELECT TIMESTAMPDIFF(SECOND,START_TIME,END_TIME),batch_job_execution.JOB_INSTANCE_ID from batch_job_execution
	where(STATUS='COMPLETED')
	order by JOB_EXECUTION_ID DESC LIMIT 4;
	

--select job-time from  COMPLETED jobs named 'csvtoxml' limit:last 4
SELECT TIMESTAMPDIFF(SECOND,START_TIME,END_TIME),
batch_job_execution.JOB_INSTANCE_ID, batch_job_execution.END_TIME
from batch_job_execution
INNER JOIN (SELECT JOB_INSTANCE_ID FROM batch_job_instance WHERE JOB_NAME='csvtoxml') batch_job_instance ON 
batch_job_execution.JOB_INSTANCE_ID = batch_job_instance.JOB_INSTANCE_ID
where(batch_job_execution.STATUS='COMPLETED')
		order by JOB_EXECUTION_ID DESC LIMIT 4;
		
		
--select step-time from COMPLETED 'csvtoxml' jobs with name 'step1' , order by time
SELECT TIMESTAMPDIFF(SECOND,batch_step_execution.START_TIME,batch_step_execution.END_TIME), batch_step_execution.STEP_NAME, batch_step_execution.JOB_EXECUTION_ID, batch_job_execution.END_TIME
FROM batch_step_execution
INNER JOIN batch_job_execution ON batch_step_execution.JOB_EXECUTION_ID = batch_job_execution.JOB_EXECUTION_ID AND 
batch_job_execution.STATUS='COMPLETED'
INNER JOIN (SELECT batch_job_instance.JOB_INSTANCE_ID FROM batch_job_instance WHERE JOB_NAME='csvtoxml') batch_job_instance ON 
batch_job_execution.JOB_INSTANCE_ID = batch_job_instance.JOB_INSTANCE_ID
WHERE batch_step_execution.STEP_NAME = 'step1'
ORDER BY batch_job_execution.END_TIME desc;


-- step analytics for specific step of specific job name, order by date
SELECT	batch_step_execution.STEP_NAME,
	batch_step_execution.READ_COUNT, 
	batch_step_execution.READ_SKIP_COUNT, 
	batch_step_execution.FILTER_COUNT, 
	batch_step_execution.COMMIT_COUNT, 
	batch_step_execution.PROCESS_SKIP_COUNT,
	batch_step_execution.WRITE_COUNT,
	batch_step_execution.WRITE_SKIP_COUNT,
	batch_step_execution.JOB_EXECUTION_ID,
	batch_job_execution.END_TIME
FROM batch_step_execution
INNER JOIN batch_job_execution ON batch_step_execution.JOB_EXECUTION_ID = batch_job_execution.JOB_EXECUTION_ID AND 
batch_job_execution.STATUS='COMPLETED'
INNER JOIN (SELECT batch_job_instance.JOB_INSTANCE_ID FROM batch_job_instance WHERE JOB_NAME='csvtoxml') batch_job_instance ON 
batch_job_execution.JOB_INSTANCE_ID = batch_job_instance.JOB_INSTANCE_ID
WHERE batch_step_execution.STEP_NAME = 'step1'
ORDER BY batch_job_execution.END_TIME desc;

schema-drop-mysql.sql

-- Autogenerated: do not edit this file

DROP TABLE IF EXISTS BATCH_STEP_EXECUTION_CONTEXT ;
DROP TABLE IF EXISTS BATCH_JOB_EXECUTION_CONTEXT ;
DROP TABLE IF EXISTS BATCH_STEP_EXECUTION ;
DROP TABLE IF EXISTS BATCH_JOB_EXECUTION_PARAMS ;
DROP TABLE IF EXISTS BATCH_JOB_EXECUTION ;
DROP TABLE IF EXISTS BATCH_JOB_INSTANCE ;

DROP TABLE IF EXISTS BATCH_STEP_EXECUTION_SEQ ;
DROP TABLE IF EXISTS BATCH_JOB_EXECUTION_SEQ ;
DROP TABLE IF EXISTS BATCH_JOB_SEQ ;

schema-mysql.sql

-- Autogenerated: do not edit this file

CREATE TABLE BATCH_JOB_INSTANCE  (
	JOB_INSTANCE_ID BIGINT  NOT NULL PRIMARY KEY ,
	VERSION BIGINT ,
	JOB_NAME VARCHAR(100) NOT NULL,
	JOB_KEY VARCHAR(32) NOT NULL,
	constraint JOB_INST_UN unique (JOB_NAME, JOB_KEY)
) ENGINE=InnoDB;

CREATE TABLE BATCH_JOB_EXECUTION  (
	JOB_EXECUTION_ID BIGINT  NOT NULL PRIMARY KEY ,
	VERSION BIGINT  ,
	JOB_INSTANCE_ID BIGINT NOT NULL,
	CREATE_TIME DATETIME NOT NULL,
	START_TIME DATETIME DEFAULT NULL ,
	END_TIME DATETIME DEFAULT NULL ,
	STATUS VARCHAR(10) ,
	EXIT_CODE VARCHAR(100) ,
	EXIT_MESSAGE VARCHAR(2500) ,
	LAST_UPDATED DATETIME,
	constraint JOB_INST_EXEC_FK foreign key (JOB_INSTANCE_ID)
	references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) ENGINE=InnoDB;

CREATE TABLE BATCH_JOB_EXECUTION_PARAMS  (
	JOB_EXECUTION_ID BIGINT NOT NULL ,
	TYPE_CD VARCHAR(6) NOT NULL ,
	KEY_NAME VARCHAR(100) NOT NULL ,
	STRING_VAL VARCHAR(250) ,
	DATE_VAL DATETIME DEFAULT NULL ,
	LONG_VAL BIGINT ,
	DOUBLE_VAL DOUBLE PRECISION ,
	IDENTIFYING CHAR(1) NOT NULL ,
	constraint JOB_EXEC_PARAMS_FK foreign key (JOB_EXECUTION_ID)
	references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ENGINE=InnoDB;

CREATE TABLE BATCH_STEP_EXECUTION  (
	STEP_EXECUTION_ID BIGINT  NOT NULL PRIMARY KEY ,
	VERSION BIGINT NOT NULL,
	STEP_NAME VARCHAR(100) NOT NULL,
	JOB_EXECUTION_ID BIGINT NOT NULL,
	START_TIME DATETIME NOT NULL ,
	END_TIME DATETIME DEFAULT NULL ,
	STATUS VARCHAR(10) ,
	COMMIT_COUNT BIGINT ,
	READ_COUNT BIGINT ,
	FILTER_COUNT BIGINT ,
	WRITE_COUNT BIGINT ,
	READ_SKIP_COUNT BIGINT ,
	WRITE_SKIP_COUNT BIGINT ,
	PROCESS_SKIP_COUNT BIGINT ,
	ROLLBACK_COUNT BIGINT ,
	EXIT_CODE VARCHAR(100) ,
	EXIT_MESSAGE VARCHAR(2500) ,
	LAST_UPDATED DATETIME,
	constraint JOB_EXEC_STEP_FK foreign key (JOB_EXECUTION_ID)
	references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ENGINE=InnoDB;

CREATE TABLE BATCH_STEP_EXECUTION_CONTEXT  (
	STEP_EXECUTION_ID BIGINT NOT NULL PRIMARY KEY,
	SHORT_CONTEXT VARCHAR(2500) NOT NULL,
	SERIALIZED_CONTEXT TEXT ,
	constraint STEP_EXEC_CTX_FK foreign key (STEP_EXECUTION_ID)
	references BATCH_STEP_EXECUTION(STEP_EXECUTION_ID)
) ENGINE=InnoDB;

CREATE TABLE BATCH_JOB_EXECUTION_CONTEXT  (
	JOB_EXECUTION_ID BIGINT NOT NULL PRIMARY KEY,
	SHORT_CONTEXT VARCHAR(2500) NOT NULL,
	SERIALIZED_CONTEXT TEXT ,
	constraint JOB_EXEC_CTX_FK foreign key (JOB_EXECUTION_ID)
	references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ENGINE=InnoDB;

CREATE TABLE BATCH_STEP_EXECUTION_SEQ (ID BIGINT NOT NULL) ENGINE=MYISAM;
INSERT INTO BATCH_STEP_EXECUTION_SEQ values(0);
CREATE TABLE BATCH_JOB_EXECUTION_SEQ (ID BIGINT NOT NULL) ENGINE=MYISAM;
INSERT INTO BATCH_JOB_EXECUTION_SEQ values(0);
CREATE TABLE BATCH_JOB_SEQ (ID BIGINT NOT NULL) ENGINE=MYISAM;
INSERT INTO BATCH_JOB_SEQ values(0);