Meta-Data Scripts for Oracle

[venkatesh.s]

Hi,

I am using Spring Batch 1.1.2.RELEASE. I wanted to configure the meta data scripts for oracle database. I was going through the spring batch documentation.

But i observed that the meta data schema mentioned has some issues. I modified the scripts as shown below and found that its working fine. Can anyone please verify if the changed I have done is valid?

CREATE SEQUENCE BATCH_STEP_EXECUTION_SEQ;
CREATE SEQUENCE BATCH_JOB_EXECUTION_SEQ;
CREATE SEQUENCE BATCH_JOB_SEQ;

CREATE TABLE BATCH_JOB_INSTANCE  (
  JOB_INSTANCE_ID INTEGER  PRIMARY KEY ,  
  VERSION INTEGER,  
  JOB_NAME VARCHAR(100) NOT NULL , 
  JOB_KEY VARCHAR(2500)
);

CREATE TABLE BATCH_JOB_PARAMS  (
  JOB_INSTANCE_ID INTEGER NOT NULL ,
  TYPE_CD VARCHAR(6) NOT NULL ,
  KEY_NAME VARCHAR(100) NOT NULL , 
  STRING_VAL VARCHAR(250) , 
  DATE_VAL TIMESTAMP DEFAULT NULL,
  LONG_VAL INTEGER ,
  DOUBLE_VAL DOUBLE PRECISION,
  constraint JOB_INSTANCE_PARAMS_FK foreign key (JOB_INSTANCE_ID)
  references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
);

CREATE TABLE BATCH_JOB_EXECUTION  (
  JOB_EXECUTION_ID INTEGER  PRIMARY KEY ,
  VERSION INTEGER,  
  JOB_INSTANCE_ID INTEGER NOT NULL,
  START_TIME TIMESTAMP DEFAULT NULL, 
  END_TIME TIMESTAMP DEFAULT NULL,
  STATUS VARCHAR(10),
  CONTINUABLE CHAR(1),
  EXIT_CODE VARCHAR(20),
  EXIT_MESSAGE VARCHAR(2500),
  CREATE_TIME TIMESTAMP DEFAULT NULL,
  constraint JOB_INSTANCE_EXECUTION_FK foreign key (JOB_INSTANCE_ID)
  references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
);

CREATE TABLE BATCH_STEP_EXECUTION  (
  STEP_EXECUTION_ID INTEGER  PRIMARY KEY ,
  VERSION INTEGER NOT NULL,  
  STEP_NAME VARCHAR(100) NOT NULL,
  JOB_EXECUTION_ID INTEGER NOT NULL,
  START_TIME TIMESTAMP NOT NULL , 
  END_TIME TIMESTAMP DEFAULT NULL,  
  STATUS VARCHAR(10),
  COMMIT_COUNT INTEGER , 
  ITEM_COUNT INTEGER , 
  CONTINUABLE CHAR(1),
  EXIT_CODE VARCHAR(20),
  EXIT_MESSAGE VARCHAR(2500),
  ROLLBACK_COUNT INTEGER , 
  WRITE_SKIP_COUNT INTEGER,
  READ_SKIP_COUNT INTEGER,
  constraint JOB_EXECUTION_STEP_FK foreign key (JOB_EXECUTION_ID)
  references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
);

CREATE TABLE BATCH_STEP_EXECUTION_CONTEXT  (
  EXECUTION_ID INTEGER NOT NULL ,
  DISCRIMINATOR VARCHAR2(1) NOT NULL,
  TYPE_CD VARCHAR(6) NOT NULL ,
  KEY_NAME VARCHAR(1000) NOT NULL , 
  STRING_VAL VARCHAR(1000) , 
  DATE_VAL TIMESTAMP DEFAULT NULL ,
  LONG_VAL VARCHAR(10) ,
  DOUBLE_VAL DOUBLE PRECISION ,
  OBJECT_VAL BLOB
);

Thanks
Venkatesh

[trisberg]

What were the issues you encountered? I wouldn't modify the scripts unless there are specific issues that we have identified a solution for.

I am considering changing the NUMBER(38) to a NUMBER(19) for storing the Long values since that documents the intent better.

[venkatesh.s]

Hi,

These were the changes I made to the script to make it run on Oracle schema. Also i found that a few columns are missing which i have added to the scripts.

1) Changed all BIGINT to INTEGER.
2) Added these columns for BATCH_JOB_EXECUTION.
ROLLBACK_COUNT INTEGER
WRITE_SKIP_COUNT INTEGER
READ_SKIP_COUNT INTEGER

I found that without these changes, the batch fails.

I am attaching the actual script i have used. I see that everything is working fine now.

CREATE SEQUENCE BATCH_STEP_EXECUTION_SEQ;
CREATE SEQUENCE BATCH_JOB_EXECUTION_SEQ;
CREATE SEQUENCE BATCH_JOB_SEQ;

CREATE TABLE BATCH_JOB_INSTANCE  (
  JOB_INSTANCE_ID INTEGER  PRIMARY KEY ,  
  VERSION INTEGER,  
  JOB_NAME VARCHAR(100) NOT NULL , 
  JOB_KEY VARCHAR(2500)
);

CREATE TABLE BATCH_JOB_PARAMS  (
  JOB_INSTANCE_ID INTEGER NOT NULL ,
  TYPE_CD VARCHAR(6) NOT NULL ,
  KEY_NAME VARCHAR(100) NOT NULL , 
  STRING_VAL VARCHAR(250) , 
  DATE_VAL TIMESTAMP DEFAULT NULL,
  LONG_VAL INTEGER ,
  DOUBLE_VAL DOUBLE PRECISION,
  constraint JOB_INSTANCE_PARAMS_FK foreign key (JOB_INSTANCE_ID)
  references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
);

CREATE TABLE BATCH_JOB_EXECUTION  (
  JOB_EXECUTION_ID INTEGER  PRIMARY KEY ,
  VERSION INTEGER,  
  JOB_INSTANCE_ID INTEGER NOT NULL,
  START_TIME TIMESTAMP DEFAULT NULL, 
  END_TIME TIMESTAMP DEFAULT NULL,
  STATUS VARCHAR(10),
  CONTINUABLE CHAR(1),
  EXIT_CODE VARCHAR(20),
  EXIT_MESSAGE VARCHAR(2500),
  CREATE_TIME TIMESTAMP DEFAULT NULL,
  constraint JOB_INSTANCE_EXECUTION_FK foreign key (JOB_INSTANCE_ID)
  references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
);

CREATE TABLE BATCH_STEP_EXECUTION  (
  STEP_EXECUTION_ID INTEGER  PRIMARY KEY ,
  VERSION INTEGER NOT NULL,  
  STEP_NAME VARCHAR(100) NOT NULL,
  JOB_EXECUTION_ID INTEGER NOT NULL,
  START_TIME TIMESTAMP NOT NULL , 
  END_TIME TIMESTAMP DEFAULT NULL,  
  STATUS VARCHAR(10),
  COMMIT_COUNT INTEGER , 
  ITEM_COUNT INTEGER , 
  CONTINUABLE CHAR(1),
  EXIT_CODE VARCHAR(20),
  EXIT_MESSAGE VARCHAR(2500),
  ROLLBACK_COUNT INTEGER , 
  WRITE_SKIP_COUNT INTEGER,
  READ_SKIP_COUNT INTEGER,
  constraint JOB_EXECUTION_STEP_FK foreign key (JOB_EXECUTION_ID)
  references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
);

CREATE TABLE BATCH_STEP_EXECUTION_CONTEXT  (
  EXECUTION_ID INTEGER NOT NULL ,
  DISCRIMINATOR VARCHAR2(1) NOT NULL,
  TYPE_CD VARCHAR(6) NOT NULL ,
  KEY_NAME VARCHAR(1000) NOT NULL , 
  STRING_VAL VARCHAR(1000) , 
  DATE_VAL TIMESTAMP DEFAULT NULL ,
  LONG_VAL VARCHAR(10) ,
  DOUBLE_VAL DOUBLE PRECISION ,
  OBJECT_VAL BLOB
);

Thanks
Venkatesh

[trisberg]

Just looked at the documentation and it seems the docs are a bit outdated. I have created a JIRA issue to update this appendix.

We do ship ready to use scripts in the spring-batch-dist-1.1.2.RELEASE/sources/spring-batch-core/src/main/resources/ directory. There is a schema-oracle10g.sql flle that uses the Oracle data types.

This is the content of that file is for the next 1.1.3 release (will work with 1.1.2 as well):

-- Autogenerated: do not edit this file

DROP TABLE  BATCH_EXECUTION_CONTEXT ;
DROP TABLE  BATCH_STEP_EXECUTION ;
DROP TABLE  BATCH_JOB_EXECUTION ;
DROP TABLE  BATCH_JOB_PARAMS ;
DROP TABLE  BATCH_JOB_INSTANCE ;

DROP SEQUENCE  BATCH_STEP_EXECUTION_SEQ ;
DROP SEQUENCE  BATCH_JOB_EXECUTION_SEQ ;
DROP SEQUENCE  BATCH_JOB_SEQ ;

CREATE TABLE BATCH_JOB_INSTANCE  (
	JOB_INSTANCE_ID NUMBER(19,0)  NOT NULL PRIMARY KEY ,  
	VERSION NUMBER(19,0) ,  
	JOB_NAME VARCHAR2(100) NOT NULL, 
	JOB_KEY VARCHAR2(2500) 
) ;

CREATE TABLE BATCH_JOB_EXECUTION  (
	JOB_EXECUTION_ID NUMBER(19,0)  NOT NULL PRIMARY KEY ,
	VERSION NUMBER(19,0)  ,  
	JOB_INSTANCE_ID NUMBER(19,0) NOT NULL,
	CREATE_TIME TIMESTAMP NOT NULL,
	START_TIME TIMESTAMP DEFAULT NULL , 
	END_TIME TIMESTAMP DEFAULT NULL ,
	STATUS VARCHAR2(10) ,
	CONTINUABLE CHAR(1) ,
	EXIT_CODE VARCHAR2(20) ,
	EXIT_MESSAGE VARCHAR2(2500) ,
	constraint JOB_INST_EXEC_FK foreign key (JOB_INSTANCE_ID)
	references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) ;
	
CREATE TABLE BATCH_JOB_PARAMS  (
	JOB_INSTANCE_ID NUMBER(19,0) NOT NULL ,
	TYPE_CD VARCHAR2(6) NOT NULL ,
	KEY_NAME VARCHAR2(100) NOT NULL , 
	STRING_VAL VARCHAR2(250) , 
	DATE_VAL TIMESTAMP DEFAULT NULL ,
	LONG_VAL NUMBER(19,0) ,
	DOUBLE_VAL NUMBER ,
	constraint JOB_INST_PARAMS_FK foreign key (JOB_INSTANCE_ID)
	references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) ;
	
CREATE TABLE BATCH_STEP_EXECUTION  (
	STEP_EXECUTION_ID NUMBER(19,0)  NOT NULL PRIMARY KEY ,
	VERSION NUMBER(19,0) NOT NULL,  
	STEP_NAME VARCHAR2(100) NOT NULL,
	JOB_EXECUTION_ID NUMBER(19,0) NOT NULL,
	START_TIME TIMESTAMP NOT NULL , 
	END_TIME TIMESTAMP DEFAULT NULL ,  
	STATUS VARCHAR2(10) ,
	COMMIT_COUNT NUMBER(19,0) , 
	ITEM_COUNT NUMBER(19,0) ,
	READ_SKIP_COUNT NUMBER(19,0) ,
	WRITE_SKIP_COUNT NUMBER(19,0) ,
	ROLLBACK_COUNT NUMBER(19,0) , 
	CONTINUABLE CHAR(1) ,
	EXIT_CODE VARCHAR2(20) ,
	EXIT_MESSAGE VARCHAR2(2500) ,
	constraint JOB_EXEC_STEP_FK foreign key (JOB_EXECUTION_ID)
	references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ;

CREATE TABLE BATCH_EXECUTION_CONTEXT  (
	EXECUTION_ID NUMBER(19,0) NOT NULL,
	DISCRIMINATOR VARCHAR2(1) NOT NULL,
	TYPE_CD VARCHAR2(6) NOT NULL,
	KEY_NAME VARCHAR2(1000) NOT NULL, 
	STRING_VAL VARCHAR2(1000) , 
	DATE_VAL TIMESTAMP DEFAULT NULL ,
	LONG_VAL NUMBER(19,0) ,
	DOUBLE_VAL NUMBER ,
	OBJECT_VAL BLOB 
) ;

CREATE SEQUENCE BATCH_STEP_EXECUTION_SEQ MAXVALUE 9223372036854775807 NOCYCLE;
CREATE SEQUENCE BATCH_JOB_EXECUTION_SEQ MAXVALUE 9223372036854775807 NOCYCLE;
CREATE SEQUENCE BATCH_JOB_SEQ MAXVALUE 9223372036854775807 NOCYCLE;

[venkatesh.s]

Thanks for the update.