Method executeQuery cannot be used for update. ERRORCODE=-4476, SQLSTATE=null

Question

I am using Spring batch framework to read from db2 and getting error. Please note that same is working fine with Mysql. When using db2 its making entry in meta tables.

BatchConfig.java

@Configuration
@EnableBatchProcessing
public class BatchConfiguration {

    @Autowired
    public JobBuilderFactory jobBuilderFactory;

    @Autowired
    public StepBuilderFactory stepBuilderFactory;

    @Autowired
    public DataSource dataSource;

    @Bean
    public JdbcCursorItemReader<User> reader()
    {
        JdbcCursorItemReader<User> reader=new JdbcCursorItemReader<>();
        reader.setDataSource(dataSource);
        reader.setSql("Select CORP_ACCT_ID from SAMS.AFLT_ACCT FETCH FIRST 100 ROWS ONLY");
        reader.setRowMapper(new UserRowerMapper());
        return reader;
    }

    @Bean
    public UserItemProcessor processor()
    {
        return new UserItemProcessor();
    }

    @Bean
    public Step step1()
    {
        return stepBuilderFactory.get("step1").<User,User>chunk(10)
                .reader(reader())
                .processor(processor())
                .build();
    }

    @Bean
    public Job job1()
    {
        return jobBuilderFactory.get("jobakaj")
                .incrementer(new RunIdIncrementer())
                .flow(step1())
                .end()
                .build();

    }

}

Error trace

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.5.3.RELEASE)

2017-05-26 01:50:43.321  INFO 1980 --- [           main] c.s.cat.SpringBatchDbReadApplication     : Starting SpringBatchDbReadApplication on KVMOF0487DVLBDC with PID 1980 (C:\Users\pankaj.k.singh\Documents\workspace-test-3\SpringBatch-DBRead\target\classes started by pankaj.k.singh in C:\Users\pankaj.k.singh\Documents\workspace-test-3\SpringBatch-DBRead)
2017-05-26 01:50:43.325  INFO 1980 --- [           main] c.s.cat.SpringBatchDbReadApplication     : No active profile set, falling back to default profiles: default
2017-05-26 01:50:43.383  INFO 1980 --- [           main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@15d9bc04: startup date [Fri May 26 01:50:43 IST 2017]; root of context hierarchy
2017-05-26 01:50:44.022  WARN 1980 --- [           main] o.s.c.a.ConfigurationClassEnhancer       : @Bean method ScopeConfiguration.stepScope is non-static and returns an object assignable to Spring's BeanFactoryPostProcessor interface. This will result in a failure to process annotations such as @Autowired, @Resource and @PostConstruct within the method's declaring @Configuration class. Add the 'static' modifier to this method to avoid these container lifecycle issues; see @Bean javadoc for complete details.
2017-05-26 01:50:44.040  WARN 1980 --- [           main] o.s.c.a.ConfigurationClassEnhancer       : @Bean method ScopeConfiguration.jobScope is non-static and returns an object assignable to Spring's BeanFactoryPostProcessor interface. This will result in a failure to process annotations such as @Autowired, @Resource and @PostConstruct within the method's declaring @Configuration class. Add the 'static' modifier to this method to avoid these container lifecycle issues; see @Bean javadoc for complete details.
2017-05-26 01:50:45.212  INFO 1980 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [org/springframework/batch/core/schema-db2.sql]
2017-05-26 01:50:45.260  INFO 1980 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [org/springframework/batch/core/schema-db2.sql] in 48 ms.
2017-05-26 01:50:45.472  INFO 1980 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2017-05-26 01:50:45.489  INFO 1980 --- [           main] o.s.b.a.b.JobLauncherCommandLineRunner   : Running default command line with: [--spring.output.ansi.enabled=always]
2017-05-26 01:50:45.504  INFO 1980 --- [           main] o.s.b.c.r.s.JobRepositoryFactoryBean     : No database type set, using meta data indicating: DB2ZOS
2017-05-26 01:50:45.645  INFO 1980 --- [           main] o.s.b.c.l.support.SimpleJobLauncher      : No TaskExecutor has been set, defaulting to synchronous executor.
2017-05-26 01:50:45.833  INFO 1980 --- [           main] o.s.b.c.l.support.SimpleJobLauncher      : Job: [FlowJob: [name=jobakaj]] launched with the following parameters: [{run.id=1, -spring.output.ansi.enabled=always}]
2017-05-26 01:50:45.950  INFO 1980 --- [           main] o.s.batch.core.job.SimpleStepHandler     : Executing step: [step1]
2017-05-26 01:50:45.971  INFO 1980 --- [           main] o.s.b.f.xml.XmlBeanDefinitionReader      : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2017-05-26 01:50:46.106  INFO 1980 --- [           main] o.s.jdbc.support.SQLErrorCodesFactory    : SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
2017-05-26 01:50:46.117 ERROR 1980 --- [           main] o.s.batch.core.step.AbstractStep         : Encountered an error executing step step1 in job jobakaj

org.springframework.batch.item.ItemStreamException: Failed to initialize the reader
    at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:147) ~[spring-batch-infrastructure-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.item.support.CompositeItemStream.open(CompositeItemStream.java:96) ~[spring-batch-infrastructure-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep.open(TaskletStep.java:310) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:197) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:67) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:134) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) [spring-core-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:128) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_101]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_101]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_101]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_101]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) [spring-aop-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) [spring-aop-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) [spring-aop-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at com.sun.proxy.$Proxy44.run(Unknown Source) [na:na]
    at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.execute(JobLauncherCommandLineRunner.java:214) [spring-boot-autoconfigure-1.5.3.RELEASE.jar:1.5.3.RELEASE]
    at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.executeLocalJobs(JobLauncherCommandLineRunner.java:231) [spring-boot-autoconfigure-1.5.3.RELEASE.jar:1.5.3.RELEASE]
    at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.launchJobFromProperties(JobLauncherCommandLineRunner.java:123) [spring-boot-autoconfigure-1.5.3.RELEASE.jar:1.5.3.RELEASE]
    at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.run(JobLauncherCommandLineRunner.java:117) [spring-boot-autoconfigure-1.5.3.RELEASE.jar:1.5.3.RELEASE]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:776) [spring-boot-1.5.3.RELEASE.jar:1.5.3.RELEASE]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:760) [spring-boot-1.5.3.RELEASE.jar:1.5.3.RELEASE]
    at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:747) [spring-boot-1.5.3.RELEASE.jar:1.5.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) [spring-boot-1.5.3.RELEASE.jar:1.5.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1162) [spring-boot-1.5.3.RELEASE.jar:1.5.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1151) [spring-boot-1.5.3.RELEASE.jar:1.5.3.RELEASE]
    at com.schwab.cat.SpringBatchDbReadApplication.main(SpringBatchDbReadApplication.java:10) [classes/:na]
Caused by: org.springframework.jdbc.UncategorizedSQLException: Executing query; uncategorized SQLException for SQL [Select CORP_ACCT_ID from SAMS.AFLT_ACCT FETCH FIRST 100 ROWS ONLY]; SQL state [null]; error code [-4476]; [jcc][10103][10941][4.15.82] Method executeQuery cannot be used for update. ERRORCODE=-4476, SQLSTATE=null; nested exception is com.ibm.db2.jcc.am.SqlException: [jcc][10103][10941][4.15.82] Method executeQuery cannot be used for update. ERRORCODE=-4476, SQLSTATE=null
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[spring-jdbc-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.springframework.batch.item.database.JdbcCursorItemReader.openCursor(JdbcCursorItemReader.java:131) ~[spring-batch-infrastructure-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.item.database.AbstractCursorItemReader.doOpen(AbstractCursorItemReader.java:406) ~[spring-batch-infrastructure-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:144) ~[spring-batch-infrastructure-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    ... 35 common frames omitted
Caused by: com.ibm.db2.jcc.am.SqlException: [jcc][10103][10941][4.15.82] Method executeQuery cannot be used for update. ERRORCODE=-4476, SQLSTATE=null
    at com.ibm.db2.jcc.am.fd.a(fd.java:680) ~[db2jcc4.jar:na]
    at com.ibm.db2.jcc.am.fd.a(fd.java:60) ~[db2jcc4.jar:na]
    at com.ibm.db2.jcc.am.fd.a(fd.java:120) ~[db2jcc4.jar:na]
    at com.ibm.db2.jcc.am.po.a(po.java:4378) ~[db2jcc4.jar:na]
    at com.ibm.db2.jcc.am.qo.b(qo.java:4136) ~[db2jcc4.jar:na]
    at com.ibm.db2.jcc.am.qo.hc(qo.java:760) ~[db2jcc4.jar:na]
    at com.ibm.db2.jcc.am.qo.executeQuery(qo.java:725) ~[db2jcc4.jar:na]
    at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_101]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_101]
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc-8.5.14.jar:na]
    at com.sun.proxy.$Proxy56.executeQuery(Unknown Source) ~[na:na]
    at org.springframework.batch.item.database.JdbcCursorItemReader.openCursor(JdbcCursorItemReader.java:126) ~[spring-batch-infrastructure-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    ... 37 common frames omitted

2017-05-26 01:50:46.179  INFO 1980 --- [           main] o.s.b.c.l.support.SimpleJobLauncher      : Job: [FlowJob: [name=jobakaj]] completed with the following parameters: [{run.id=1, -spring.output.ansi.enabled=always}] and the following status: [FAILED]
2017-05-26 01:50:46.180  INFO 1980 --- [           main] c.s.cat.SpringBatchDbReadApplication     : Started SpringBatchDbReadApplication in 3.241 seconds (JVM running for 3.901)
2017-05-26 01:50:46.184  INFO 1980 --- [       Thread-3] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@15d9bc04: startup date [Fri May 26 01:50:43 IST 2017]; root of context hierarchy
2017-05-26 01:50:46.188  INFO 1980 --- [       Thread-3] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown

Please help!!

Answer 1

These are just few suggestions and not necessarily result in solution,

1.Execute query on prompt to see if there is data for query - Select CORP_ACCT_ID from SAMS.AFLT_ACCT FETCH FIRST 100 ROWS ONLY

2.See if there are no special characters or missing spaces in your query

3.Are meta data tables present and entries made to those six tables?

4.Try running job by removing clause - FETCH FIRST 100 ROWS ONLY on some small table to see if issue is caused by that clause

5.As per this chart, your driver version ( 4.15.82 ) seems correct for version 10.1 FP2 but you can try upgrading your driver ( though as last option)

I run your job as is and it completes as shown in below logs. I had changed table & schema to ones that I have. I have DB2 database and six Spring batch meta data tables ready.

Also, I have verified that point # 1 is invalid as I don't get any errors even if target read table is empty.

For your error, nothing specific other than driver version or sql syntax could be said.

2017-05-29 15:04:02.772  INFO 10368 --- [           main] configuration.ConfigurationClass         : Starting ConfigurationClass on dev-C1BF-mdsabi with PID 10368 (D:\workspace\cursoritemreader\target\classes started by mdsabir.khan in D:\workspace\cursoritemreader)
2017-05-29 15:04:02.775  INFO 10368 --- [           main] configuration.ConfigurationClass         : No active profile set, falling back to default profiles: default
2017-05-29 15:04:02.816  INFO 10368 --- [           main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@429bd883: startup date [Mon May 29 15:04:02 IST 2017]; root of context hierarchy
2017-05-29 15:04:03.256  WARN 10368 --- [           main] o.s.c.a.ConfigurationClassEnhancer       : @Bean method ScopeConfiguration.stepScope is non-static and returns an object assignable to Spring's BeanFactoryPostProcessor interface. This will result in a failure to process annotations such as @Autowired, @Resource and @PostConstruct within the method's declaring @Configuration class. Add the 'static' modifier to this method to avoid these container lifecycle issues; see @Bean javadoc for complete details.
2017-05-29 15:04:03.266  WARN 10368 --- [           main] o.s.c.a.ConfigurationClassEnhancer       : @Bean method ScopeConfiguration.jobScope is non-static and returns an object assignable to Spring's BeanFactoryPostProcessor interface. This will result in a failure to process annotations such as @Autowired, @Resource and @PostConstruct within the method's declaring @Configuration class. Add the 'static' modifier to this method to avoid these container lifecycle issues; see @Bean javadoc for complete details.
2017-05-29 15:04:19.683  INFO 10368 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [org/springframework/batch/core/schema-db2.sql]
2017-05-29 15:04:22.477  INFO 10368 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [org/springframework/batch/core/schema-db2.sql] in 2794 ms.
2017-05-29 15:04:22.637  INFO 10368 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2017-05-29 15:04:22.648  INFO 10368 --- [           main] o.s.b.a.b.JobLauncherCommandLineRunner   : Running default command line with: []
2017-05-29 15:04:23.203  INFO 10368 --- [           main] o.s.b.c.r.s.JobRepositoryFactoryBean     : No database type set, using meta data indicating: DB2
2017-05-29 15:04:23.358  INFO 10368 --- [           main] o.s.b.c.l.support.SimpleJobLauncher      : No TaskExecutor has been set, defaulting to synchronous executor.
2017-05-29 15:04:28.097  INFO 10368 --- [           main] o.s.b.c.l.support.SimpleJobLauncher      : Job: [FlowJob: [name=jobakaj]] launched with the following parameters: [{run.id=1}]
2017-05-29 15:04:38.321  INFO 10368 --- [           main] o.s.batch.core.job.SimpleStepHandler     : Executing step: [step1]
2017-05-29 15:04:48.361  INFO 10368 --- [           main] o.s.b.c.l.support.SimpleJobLauncher      : Job: [FlowJob: [name=jobakaj]] completed with the following parameters: [{run.id=1}] and the following status: [COMPLETED]
2017-05-29 15:04:48.366  INFO 10368 --- [           main] configuration.ConfigurationClass         : Started ConfigurationClass in 45.823 seconds (JVM running for 46.645)
2017-05-29 15:04:48.368  INFO 10368 --- [       Thread-3] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@429bd883: startup date [Mon May 29 15:04:02 IST 2017]; root of context hierarchy
2017-05-29 15:04:48.372  INFO 10368 --- [       Thread-3] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown

Answer 2

I was getting similar error with spring boot and db2 while using JPA. In my case I solved it as below:

1. Had issue with the query I was using - identified by enabling hibernate logs and executing the query manually using sql client

2. I was using @Query annotation in my repository class without using @Modifying for update queries. This was the main reason for getting "Method executeQuery cannot be used for update..." error.