Could not obtain sequence value; nested exception is com.ibm.db2.jcc.c.SqlException

[kulasekara]

Hi ,

I am trying to run the spring batch application that's connecting to DB2 Mainframe server. Below are the configuration that I have done.

ApplicationContext.xml
<bean id="jobRepository"
class="org.springframework.batch.core.repository.s upport.JobRepositoryFactoryBean">
<property name="dataSource" ref="jobRepository-dataSource" />
<property name="transactionManager"
ref="jobRepository-transactionManager" />
<property name="databaseType" value="DB2ZOS" />
<property name="tablePrefix" value="${batch.tablePrefix}" />
<property name="incrementer" ref="incrementerParent" />
</bean>

<bean id="sequenceIncrementerParent" class="${batch.database.incrementer.class}" abstract="true">
<property name="dataSource" ref="jobRepository-dataSource" />
</bean>

<bean id="columnIncrementerParent" class="${batch.database.incrementer.class}" abstract="true" parent="sequenceIncrementerParent">
<property name="columnName" value="ID" />
</bean>

<bean id="incrementerParent" parent="${batch.database.incrementer.parent}">
<property name="incrementerName" value="DUMMY"/>
</bean>


Config.Properties

db2.defaultSchema=DFBP33U
batch.tablePrefix=DFBP33U.BATCH_

batch.database.incrementer.class=org.springframewo rk.jdbc.support.incrementer.DB2MainframeSequenceMa xValueIncrementer
batch.database.incrementer.parent=sequenceIncremen terParent

Version:Spring Batch -2.1.5
Spring -3.1.0.M1
DB2ZOS - version 8.0

I am getting the following exception message when i trying to run my spring batch application.Can you please guide me, if i am missing something in the configuration.Might be the incrementor config is not getting effect?
I have tried creating the sequence in the name of tables in the DB2 and inserted the records like this
INSERT INTO BATCH_STEP_EXECUTION_SEQ values(0); still am getting the same exception.

Exception Message Trace:
org.springframework.dao.DataAccessResourceFailureE xception: Could not obtain sequence value; nested exception is com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -4700, SQLSTATE: 56038, SQLERRMC: null
at org.springframework.jdbc.support.incrementer.Abstr actSequenceMaxValueIncrementer.getNextKey(Abstract SequenceMaxValueIncrementer.java:78)
at org.springframework.jdbc.support.incrementer.Abstr actDataFieldMaxValueIncrementer.nextIntValue(Abstr actDataFieldMaxValueIncrementer.java:121)
at org.springframework.batch.core.repository.dao.Jdbc JobInstanceDao.createJobInstance(JdbcJobInstanceDa o.java:67)
at org.springframework.batch.core.repository.support. SimpleJobRepository.createJobExecution(SimpleJobRe pository.java:184)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:79)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:618)
at org.springframework.aop.support.AopUtils.invokeJoi npointUsingReflection(AopUtils.java:310)
at org.springframework.aop.framework.ReflectiveMethod Invocation.invokeJoinpoint(ReflectiveMethodInvocat ion.java:182)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :149)
at org.springframework.transaction.interceptor.Transa ctionInterceptor.invoke(TransactionInterceptor.jav a:106)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :171)
at org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy0.createJobExecution(Unknown Source)
at org.springframework.batch.core.launch.support.Simp leJobLauncher.run(SimpleJobLauncher.java:79)
at org.springframework.batch.core.launch.support.Comm andLineJobRunner.start(CommandLineJobRunner.java:2 08)
at org.springframework.batch.sample.quartz.CommandLin eLauncher.main(CommandLineLauncher.java:24)
Caused by:
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -4700, SQLSTATE: 56038, SQLERRMC: null
at com.ibm.db2.jcc.c.fg.e(fg.java:1596)
at com.ibm.db2.jcc.c.fg.a(fg.java:1206)
at com.ibm.db2.jcc.b.gb.g(gb.java:140)
at com.ibm.db2.jcc.b.gb.a(gb.java:39)
at com.ibm.db2.jcc.b.w.a(w.java:34)
at com.ibm.db2.jcc.b.vb.g(vb.java:139)
at com.ibm.db2.jcc.c.fg.n(fg.java:1177)
at com.ibm.db2.jcc.c.fg.a(fg.java:1832)
at com.ibm.db2.jcc.c.fg.a(fg.java:473)
at com.ibm.db2.jcc.c.fg.executeQuery(fg.java:457)
at org.apache.commons.dbcp.DelegatingStatement.execut eQuery(DelegatingStatement.java:205)
at org.springframework.jdbc.support.incrementer.Abstr actSequenceMaxValueIncrementer.getNextKey(Abstract SequenceMaxValueIncrementer.java:69)

Kindly help to resolve the issue,
Thanks in advance

Pandiyan S

[arno]

I think you just need to specify the database type, the factory bean should be smart enough to figure out which incrementer to use.

BTW, did you use the DB2 creation script Spring Batch provides? This is where the sequences are created, so you don't need to specify your own names for the sequences.

[kulasekara]

Thanks for your reply.

I have specified the databasae type as "DB2ZOS" in the JobRepository bean.
As per your suggestion, it will figure out which incrementor to use(as per config it's DB2MainframeSequenceMaxValueIncrementer). But, I have doubt, the error message shows that it's trying to use class (AbstractSequenceMaxValueIncrementer). Sorry if am wrong anywhere.

Yeah, I have used the DB2 Script provided by spring batch.
Sorry I have missed out to mention this, I have created the spring batch tables and sequence and ran the spring batch application for DB2ZOS, it's working fine.

My DB Engineer suggest that using sequence will cost some memory issues and performance in the DB,So I am trying run the spring batch without creating the sequence in the DB2.

Can you please suggest any approach or solution for this.

Thanks
Pandiyan S

[arno]

My DB Engineer suggest that using sequence will cost some memory issues and performance in the DB,So I am trying run the spring batch without creating the sequence in the DB2.

Can you please suggest any approach or solution for this.

ask you DB engineer to suggest an alternative to sequences! Spring Batch needs a strategy to create IDs for its entities (job instances, job executions, etc.), if you come up with an alternative, you could then either provide your own implementation of an incrementer or even override the DAOs.

[kulasekara]

Is there any alternative approach for DB2 by creating the table instead of sequence as in the case of Mysql database.

How to configure the DB2MainframeSequenceMaxValueIncrementer.java?
And one more thing, the configuration of DB2MainframeSequenceMaxValueIncrementer.java class is not getting effective in the application. that's the reason the exception message shows Abstr actSequenceMaxValueIncrementer.java class instead of DB2MainframeSequenceMaxValueIncrementer.java class

If it would've been effective, the following code snippet would be called in the
DB2MainframeSequenceMaxValueIncrementer.java

protected String getSequenceQuery()
{
return (new StringBuilder("select next value for ")).append(getIncrementerName()).append(" from sysibm.sysdummy1").toString();
}
Error Message:
at org.springframework.jdbc.support.incrementer.Abstr actSequenceMaxValueIncrementer.getNextKey(Abstract SequenceMaxValueIncrementer.java:78)
at org.springframework.jdbc.support.incrementer.Abstr actDataFieldMaxValueIncrementer.nextIntValue(Abstr actDataFieldMaxValueIncrementer.java:121).

Kindly correct me if my understanding is wrong in this case.

Thanks
Pandiyan S

[arno]

you need to provide your own incrementerFactory to the JobRepositoryFactoryBean to control the way IDs are created. The factory can return a custom DataFieldMaxValueIncrementer that uses tables instead of sequences (the DataFieldMaxValueIncrementer implementations provided by Spring rely on sequences).