Spring Batch with DB2

Question

I'm running a straight forward batch job to read a record from DB2 DB, I'm not getting any result in the output or an error even.

Any glance of where I'm missing anything?

Here is the code of the batch.

    @Configuration
    @EnableBatchProcessing
    @AllArgsConstructor
    public class ETLConfig {
        
        private JobBuilderFactory jobBuilderFactory;
        private StepBuilderFactory stepBuilderFactory;
        private EntityManagerFactory emf;
    
        @Bean
        public Step sampleStep(){
    
            return stepBuilderFactory.get("sampleStep")
                    .<String, String>chunk(5)
                    .reader(itemReader())
                    .writer(i -> i.stream().forEach(j -> System.out.println(j)))
                    .build();
        }
    
        @Bean
        public Job sampleJob(){
            return jobBuilderFactory.get("sampleJob")
                    .incrementer(new RunIdIncrementer())
                    .start(sampleStep())
                    .build();
        }
    
    
        @Bean
        public JpaPagingItemReader itemReader(/*@Value("${query}") String query*/){
            return new JpaPagingItemReaderBuilder<>()
                    .name("db2Reader")
                    .entityManagerFactory(emf)
                    .queryString("select * FROM EXPORT.FCR_HDR F WHERE F.FCR_REF = 'R2G0128330'")
                    .pageSize(3)
                    .build();
        }
    
    }

If I run the same query on the DB I'm getting the output of this Reference.

@Component
@AllArgsConstructor
public class NoPersistenceBatchConfigurer extends DefaultBatchConfigurer {


    @Bean
    public DataSource batchDataSource() throws ClassNotFoundException {

        return DataSourceBuilder.create()
                .driverClassName("org.h2.Driver")
                .url("jdbc:h2:mem:testdb")
                .username("sa")
                .password("")
                .build();
    }

    @Override
    public void setDataSource(DataSource batchDataSource) {
        try {
            super.setDataSource(batchDataSource());
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

} 

Answer 1

Since you have multiple data sources, you need to:

• configure the job repository to use the H2 database by providing a custom BatchConfigurer (as you mentioned in the description)
• configure the reader to read data from DB2. For the JpaPagingItemReader, you need to configure the entityManagerFactory to point to the DB2 datasource.

Answer 2

It is necessary to specify a main datasource so that spring batch, more specifically the Job repository, saves the metadata related to the jobs, steps, etc.

Example

import javax.sql.DataSource;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

@Configuration
public class DatabaseConfig {
    
    @Bean()
    @Primary
    public DataSource datasouce() {
                DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
                dataSourceBuilder.driverClassName("org.h2.Driver");
                dataSourceBuilder.url("jdbc:h2:mem:testdb");
                dataSourceBuilder.username("sa");
                dataSourceBuilder.password("password");
                
                return dataSourceBuilder.build();
         
    }
    
    
}

after, we can use specific datasource of other database .

Example of itemReader

public DataSource as400datasource() {

        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName("com.ibm.as400.access.AS400JDBCDriver");
        dataSourceBuilder.url("jdbc:as400://PUB400.COM;naming=system;error=true");
        dataSourceBuilder.username("User");
        dataSourceBuilder.password("pass");
        return dataSourceBuilder.build();

    }

    public JdbcCursorItemReader<Empleado> jdbcCursorItemReader() {
        JdbcCursorItemReader<Empleado> jdbcCursorItemReader = new JdbcCursorItemReader<Empleado>();

        jdbcCursorItemReader.setDataSource(as400datasource());

        jdbcCursorItemReader.setSql("SELECT * FROM EMPLEADOS");

        jdbcCursorItemReader.setRowMapper(new BeanPropertyRowMapper<Empleado>() {
            {
                setMappedClass(Empleado.class);
            }
        });

        return jdbcCursorItemReader;
    }

Example of all job (this is only an example to see results fast).

@Configuration
public class SampleJob {

    @Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Autowired
    private FirstItemWriter firstItemWriter;

    @Bean
    public Job chunkJob() {
        return jobBuilderFactory.get("Chunk Job").incrementer(new RunIdIncrementer()).start(firstChunkStep()).build();
    }

    private Step firstChunkStep() {
        return stepBuilderFactory.get("First Chunk Step").<Empleado, Empleado>chunk(5).reader(jdbcCursorItemReader())
                .writer(firstItemWriter).build();
    }
    
    public DataSource as400datasource() {

        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName("com.ibm.as400.access.AS400JDBCDriver");
        dataSourceBuilder.url("jdbc:as400://PUB400.COM;naming=system;error=true");
        dataSourceBuilder.username("User");
        dataSourceBuilder.password("pass");
        return dataSourceBuilder.build();

    }

    public JdbcCursorItemReader<Empleado> jdbcCursorItemReader() {
        JdbcCursorItemReader<Empleado> jdbcCursorItemReader = new JdbcCursorItemReader<Empleado>();

        jdbcCursorItemReader.setDataSource(as400datasource());

        jdbcCursorItemReader.setSql("SELECT * FROM EMPLEADOS");

        jdbcCursorItemReader.setRowMapper(new BeanPropertyRowMapper<Empleado>() {
            {
                setMappedClass(Empleado.class);
            }
        });

        return jdbcCursorItemReader;
    }

}

Finally I show the results in itemWriter to check if it was successful connection

Example of itemWriter

@Component
public class FirstItemWriter implements ItemWriter<Empleado> {

    @Override
    public void write(List<? extends Empleado> items) throws Exception {
        System.out.println("Inside Item Writer");
        for(int i = 0 ; i<items.size() ; i++) {
            Empleado empleado = items.get(i);
            System.out.println("Nombre "+ empleado.getNOMBRE());
        }
    }

}

In this particular case, I connect to a DB2 Iseries/as400 and to connect, I need the jdbc jt400.

Note if you connect to as400 Using the one in the version of java 6 or version 8 that come within the jtopen library, the latest versions give problems of unimplemented methods such as isValid().