Announcement Announcement Module
Collapse
No announcement yet.
Meta-Data Scripts for Oracle Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Meta-Data Scripts for Oracle

    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?

    Code:
    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

  • #2
    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.

    Comment


    • #3
      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.

      Code:
      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

      Comment


      • #4
        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):

        Code:
        -- 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;

        Comment


        • #5
          Thanks for the update.

          Comment

          Working...
          X