Spring Batch Reader for distributed DB2 database

Question

I am trying to write a job using Spring batch framework. Job needs to get data from a clustered db2 database, call some logic on each fetched record and then store transformed data in same db ( different table than from where it was read). I am trying to write step1 as below,

@Bean
    public Step step1(StepBuilderFactory stepBuilderFactory,
            ItemReader<RemittanceVO> reader, ItemWriter<RemittanceClaimVO> writer,
            ItemProcessor<RemittanceVO, RemittanceClaimVO> processor) {

        return stepBuilderFactory.get("step1")
                .<RemittanceVO, RemittanceClaimVO> chunk(100).reader(reader)
                .processor(processor).writer(writer).build();
    }

Currently, I face two challenges due to database being DB2 and being clustered,

1.

SQLs provided for meta data at - /org/springframework/batch/core/schema-db2.sql doesn't work for distributed DB2. It fails on command , constraint JOB_INST_UN unique (JOB_NAME, JOB_KEY) .

Queries written in this file can be tweaked to distributed db2 or I can create tables manually too but I am not sure if I should create tables manually? if that will have some further complications?

I need all these tables because I wanted to used Spring batch for its PAUSE , RESTART functionalities.

2.

We need to fire all SELECT queries on DB2 with READ ONLY WITH UR SO question. If we don't run queries with this keyword, db can get locked.

Problem in point # 2 is that I can't use in built reader classes of Spring Batch (JdbcPagingItemReader etc )as those doesn't support this db2 specific keyword.

By reading useless simple examples on Internet that explain advantages of this framework, I thought that I will be up and running in a very short period but it looks I have to write own query provider classes, research meta data sqls and what not if db happens to be DB2 and distributed.

Has anybody implemented similar job for distributed Db2 database and guide me on above points?

score 0 · Accepted Answer · edited May 23 '17 at 10:29

I guess, to solve point # 1 , I will create tables manually since I have confirmed in another question that tables will not get dropped automatically so recreation will not be needed. One time manual activity should be enough.

and I will solve point # 2 by specifying isolation levels at transaction level so WITH UR in SELECT queries will not be needed,

@Autowired
    private DataSource dataSource;
    @Bean
        public TransactionTemplate transactionTemplateUR(){
            TransactionTemplate txnTemplate = new TransactionTemplate();
            txnTemplate.setIsolationLevelName("ISOLATION_READ_UNCOMMITTED");
            txnTemplate.setTransactionManager(txnManager);
            return txnTemplate;
        }

        @Bean
        public PlatformTransactionManager txnManager(DataSource dataSource){
            DataSourceTransactionManager txnManager = new DataSourceTransactionManager();
            txnManager.setDataSource(dataSource);
            return txnManager;
        }

Your solution for point #2 isn't good, because you will get inconsistent data if rollback occurs. So you should use org.springframework.batch.item.database.support.Db2PagingQueryProvider as starting point and modify it for your requirements. — Dmitry, Aug 22 '16 at 11:45

Spring Batch Reader for distributed DB2 database

1 Answers1

Linked