StoredProcedureItemReader in Spring boot batch

Question

I'm trying to read a DB2 stored procedure in spring batch using StoredProcedureItemReader for cursor. The sql string is not getting executed, a default sql is getting passed to jdbctemplate

    public class MyItemPreparedStatementSetter implements PreparedStatementSetter {

    @Override
    public void setValues(PreparedStatement ps) throws SQLException {

        ps.setString(1, "UH");
        ((CallableStatement) ps).registerOutParameter(2, Types.VARCHAR);
        ((CallableStatement) ps).registerOutParameter(3, Types.CHAR);
        ((CallableStatement) ps).registerOutParameter(4, Types.INTEGER);
    }

}
   @Bean
public StoredProcedureItemReader<PrintResponse> jdbcReader(JdbcTemplate jdbcTemplate) throws Exception {
    StoredProcedureItemReader<PrintResponse> storedProcedureItemReader = new StoredProcedureItemReader<>();
    SqlParameter[] parameters = {new SqlParameter("@I_PRODUCT_CDE", Types.CHAR)
            , new SqlParameter("@O_ERROR_MESSAGE", Types.VARCHAR), new SqlParameter("@O_SQLSTATE", Types.CHAR)
            ,new SqlParameter("@O_SQLCODE", Types.INTEGER)};

    storedProcedureItemReader.setFunction(true);        
    storedProcedureItemReader.setDataSource(jdbcTemplate.getDataSource());
    storedProcedureItemReader.setProcedureName("DEVTEST.PP_GETALLPRINTREADY");        
    storedProcedureItemReader.setRowMapper(new PolicyPrintResultRowMapper());
    storedProcedureItemReader.setParameters(parameters);
    storedProcedureItemReader.setPreparedStatementSetter(new MyItemPreparedStatementSetter());

    storedProcedureItemReader.setCurrentItemCount(0);

    storedProcedureItemReader.afterPropertiesSet();
    System.out.println(storedProcedureItemReader.getSql());

    return storedProcedureItemReader;       
}

I'm using the Batch step which is invoked by the Job:

@Autowired
public JobBuilderFactory jobBuilderFactory;

@Autowired
public StepBuilderFactory stepBuilderFactory;

@Autowired
@Qualifier("jdbcTemplate")
JdbcTemplate jdbcTemplate; 

    @Bean
public ItemWriter<PrintResponse> fileWriter(@Value("${output}") Resource resource) {

    LineAggregator<PrintResponse> lineAggregator = new DelimitedLineAggregator<>();
    return new FlatFileItemWriterBuilder<PolicyPrint>()
            .name("file-writer")
            .resource(resource) 
            .lineAggregator(lineAggregator)
            .build();
}

@Bean
public Job jobPrintRecord(JobBuilderFactory jobBuilderFactory, Step step1) {
    return jobBuilderFactory.get("jobPrintAll")
            .incrementer(new RunIdIncrementer())
            .start(step1)
            .build();
 }

@Bean
public Step step1() throws Exception {
    return stepBuilderFactory.get("step1")
            .<PrintResponse, PrintResponse>chunk(5)
            .reader(jdbcReader(jdbcTemplate))
            .writer(fileWriter(null))
            .build();
}

Here is the default query being passed:

bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? order by JOB_INSTANCE_ID desc];

The error stack is as follows:

 java.lang.IllegalStateException: Failed to execute CommandLineRunner org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:797) [spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]   ... 5 common frames omitted

Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=TEIWUDB1.BATCH_JOB_INSTANCE, DRIVER=4.21.29 at com.ibm.db2.jcc.am.kd.a(kd.java:810) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.am.kd.a(kd.java:66) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.am.kd.a(kd.java:140) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.am.sp.c(sp.java:2796) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.am.sp.d(sp.java:2784) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.am.sp.a(sp.java:2212) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.am.tp.a(tp.java:7997) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.t4.bb.i(bb.java:148) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.t4.bb.b(bb.java:41) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.t4.p.a(p.java:32) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.t4.vb.i(vb.java:145) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.am.sp.kb(sp.java:2181) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.am.tp.yc(tp.java:3698) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.am.tp.a(tp.java:4578) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.am.tp.b(tp.java:4173) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.am.tp.lc(tp.java:770) ~[db2jcc4-11.1-4.21.29.jar:na] at com.ibm.db2.jcc.am.tp.executeQuery(tp.java:735) ~[db2jcc4-11.1-4.21.29.jar:na] at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:666) ~[spring-jdbc-5.0.5.RELEASE.jar:5.0.5.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-5.0.5.RELEASE.jar:5.0.5.RELEASE] ... 27 common frames omitted

Why am I not getting the resultset or able to execute the query. I'm new to spring batch and kind of stuck. The debug shows, the datasource is configured properly.

Thanks!

@MichaelMinella No, I've not created those and not aware about them. Do I need to create them in DB2? What all tables need to be created? Could you please link a documentation or example. Thank you! — Abhilash Muthuraj

Abhilash Muthuraj Abhilash Muthuraj · Accepted Answer · 2018-05-13T00:37:48

The issue is resolved, I've used the default memory use of Map instead of a physical database. Anyone experiencing the same issue, override the setDatasource and leave it empty as follows:

    public class BatchConfiguration extends DefaultBatchConfigurer
 { 
@Override public void setDataSource(DataSource dataSource) {
 // override to do not set datasource even if a datasource exist. 
// initialize will use a Map based JobRepository (instead of database) 
} 
}

StoredProcedureItemReader in Spring boot batch

1 Answers