Announcement Announcement Module
Collapse
No announcement yet.
Repository table definitions Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Repository table definitions

    Hi,

    I've got some questions on the definition of the repository tables (the five "BATCH_"-tables used to store job and step information for the Spring Batch).

    Background for the following questions

    In spring-batch-dist-1.1.2.RELEASE\sources\spring-batch-core\src\main\sql there are a number of "vpp"-files. These seem to contain some kind of generic DDL statements that can be programmatically customized.

    tables-main.sql.vpp
    tables-context.sql.vpp

    There are also pairs of files that carry information about the different supported RDBMS's.

    x.properties - SQL Language Oddities etc
    x.vpp - macros for not null and sequence

    where x is the name of the RDBMS.

    If you then look into spring-batch-dist-1.1.2.RELEASE\sources\spring-batch-core\src\main\resources you will find pairs of sql-files with ready-to-use DDL for each supported RDBMS.

    schema-x.sql
    schema-upgrade-x.sql

    Questions
    1. When are these five tables (and three sequences) created?
    2. Can you create the tables by yourself by executing the relevant DDL (in the file schema-x.sql)? If so, will Spring Batch use the created tables?
    3. When is the DDL generated? Is it at the packaging of Spring Batch or at some other time?
    4. How can you influence the generation of the DDL? There is a $!{VOODOO} parameter in the generic DDL statements that could have been used for more specific customization. For example, DB2 for LUW has the notion of a tablespace in which you might want to place the data of a table. This is done by adding "IN tsname" to its create table statements. The same goes for DB2 for z/OS that can have a "IN DATABASE dbname" or a "IN dbname.tsname" in its create table statements.
    5. Is it possible to tell SpringBatch what schema name it should use to access the these tables or does it have to be the same schema name as the userid Spring Batch is running under?
    Thanks in advance, Len...

  • #2
    1. When are these five tables (and three sequences) created?
    2. Can you create the tables by yourself by executing the relevant DDL (in the file schema-x.sql)? If so, will Spring Batch use the created tables?
    You need to run the scripts to create the tables before you run your jobs. Spring Batch will look for these tables.

    3. When is the DDL generated? Is it at the packaging of Spring Batch or at some other time?
    The DDL is generated during the build in the spring-batch-core/target/generated-resources directory. They are also packaged with the release in the spring-batch-core/src/main/resources directory

    4. How can you influence the generation of the DDL? There is a $!{VOODOO} parameter in the generic DDL statements that could have been used for more specific customization. For example, DB2 for LUW has the notion of a tablespace in which you might want to place the data of a table. This is done by adding "IN tsname" to its create table statements. The same goes for DB2 for z/OS that can have a "IN DATABASE dbname" or a "IN dbname.tsname" in its create table statements.
    You could add a VOODOO variable in the db2.properties file to pass in these clauses

    5. Is it possible to tell SpringBatch what schema name it should use to access the these tables or does it have to be the same schema name as the userid Spring Batch is running under?
    Yes, you can specify a "tablePrefix" property with "SCHEMA.BATCH_" as the value. The default for this property is "BATCH_".

    Comment


    • #3
      Repository tables

      Thanks a lot for your reply, Thomas!

      What is the strategy of Spring Batch when it comes to migrating these tables from one release to another? The schema-upgrade.sql file dropped all the objects and redefined them. This is not the way we want it to work in a real production environment. We need the schema to be fairly stable and if a change is needed, it has to be done in a non-disruptive way using schema evolution techniques (by using online ALTERs) and keeping the tables usable by applications running with older versions of Spring Batch.

      What are the plans for handling schema upgrades of the repository tables?

      Thanks in advance, Len...

      Comment


      • #4
        We are providing upgrade scripts from the 1.0 version to the 1.1 version. These scripts do migrate some data into a new table. What upgrade script did you use that dropped and recreated the tables?

        Comment


        • #5
          Having a single set of repository tables

          I was wrong in my previous post when saying that all tables were dropped and recreated. The script I referred to was schema-upgrade-x.sql that can be found in spring-batch-dist-1.1.2.RELEASE\sources\spring-batch-core\src\main\resources, and I believe you refer to the same.

          The script creates a new table (EXECUTION_CONTEXT), populate it from the old table (STEP_EXECUTION_CONTEXT), and then drops the old table (STEP_EXECUTION_CONTEXT). Furthermore it does some other maintenance on the repository tables, by for example adding columns.

          What we want to accomplish is to have a single set of repository tables (and sequences) that is to be used by all applications. Since applications will be developed over time, they will most likely use various versions of the Spring Batch framework.

          Let's take an example based on the above described migration.

          Application A is developed using version 1.0 of the framework, the repository tables are created and the application is put into production.

          Later, Application B is developed using version 1.1 of the framework. When putting into production it won't work since it requires the EXECUTION_CONTEXT table to run. We then run the migration script mentioned above. Application B will now run since the tables are migrated to the new definition.

          But, what about Application A? It won't run anymore since it requires the STEP_EXECUTION_CONTEXT table which was dropped by the migration script.

          This simplified example shows that there is no backward compatibility when moving on using new versions of the framework. If every version of the framework makes changes to the repository tables as the one between 1.0 and 1.1, we need to migrate all our applications everytime there is a new version of Spring Batch that any application wants to exploit.

          This is the reason behind my question in my previous post in this thread.

          Any comment?

          /Len... (with greetings from a cloudy Stockholm)

          Comment


          • #6
            Len,

            I would recommend keeping separate sets of batch tables for each version - you can specify a prefix in the job repository configuration - so it could be BATCH1_, BATCH11_ and BATCH2_ or whatever you like. I would hope that future point releases will be able to coexist with 2.0 job metadata, but for any existing versions the table structures aren't compatible.

            Comment

            Working...
            X