Spring batch samples 1.0 m4 on DB2

[joelee]

Anyone successfully ran the m4 samples on DB2?

First error, "solved"

The schema-db2.sql included in the distribution did not define NOT NULL for all the primary keys in the CREATE TABLE statements. Added and then ok (I couldn't refresh the one in the jar though so I just disabled the sql running in the init xml and run the schema sql manually).

Second, I ran the Football job sample and found the data-source-context.xml is missing the dialect property in the session factory definition

hibernate.dialect=org.hibernate.dialect.DB2Dialect

Added and then ok.

Then I am stuck. I got this error:
[org.springframework.jdbc.support.incrementer.DB2Se quenceMaxValueIncrementer]: No property 'columnName' found
I already updated the entry in batch.properties to
org.springframework.jdbc.support.incrementer.DB2Se quenceMaxValueIncrementer

What do I have to do? Thanks.

[adrianshum]

modify the abstract bean "incrementerParent" and add property
"columnName" to it.
(I've added <property name="columnName" value="ID" /> )

I bet you are creating your own app context and trying out your code :)
for the original unit test, it make use of batch.properties + PropertyOverrideConfigurer to setup such property)

[joelee]

modify the abstract bean "incrementerParent" and add property
"columnName" to it.
(I've added <property name="columnName" value="ID" /> )

I bet you are creating your own app context and trying out your code :)
for the original unit test, it make use of batch.properties + PropertyOverrideConfigurer to setup such property)

The strange thing is I am not creating my own app context or own code. I am just trying to run the sample against DB2, the DB we are using in our shop :(

And thanks for the pointer, though I found this line in my batch.properties

# Bean Properties for override
# for HSQLDB:
incrementerParent.columnName=ID

Though it said for HSQLDB, does it work for DB2?

Adrian, is this the same as what you said on your post? Actually I am not sure which file you mean ...do you mean modifying a Java class or a XML config file?

Thanks!

[adrianshum]

The strange thing is I am not creating my own app context or own code. I am just trying to run the sample against DB2, the DB we are using in our shop :(

And thanks for the pointer, though I found this line in my batch.properties

# Bean Properties for override
# for HSQLDB:
incrementerParent.columnName=ID

Though it said for HSQLDB, does it work for DB2?

Adrian, is this the same as what you said on your post? Actually I am not sure which file you mean ...do you mean modifying a Java class or a XML config file?

Thanks!

I think I misunderstood a bit on your problem in previous reply... anywya, if you are running the sample on another DB, probably u have already changed these file:
batch.properties:
1) JDBC related settings
2) changed batch.schema.script and batch.business.schema.script to DB2 ones
3) batch.database.incrementer.class to some incrementer supporting DB2, i.e. DB2SequenceMaxValueIncrementer

As DB2SequenceMaxValueIncrementer do not have "columnName" property, so if u still have incrementerParent.columnName=ID in your batch.properties, it will probably cause problem. So I think just remark that line should be fine if u have done the above setups correctly

[joelee]

Thanks! I just found out similar thing...I commented out the columnName property from batch.properties and I can moved one step forward........

Now this:

org.springframework.dao.DataIntegrityViolationExce ption: PreparedStatementCallback; SQL [INSERT into BATCH_STEP_EXECUTION(STEP_EXECUTION_ID, VERSION, STEP_INSTANCE_ID, JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS, COMMIT_COUNT, TASK_COUNT, TASK_STATISTICS, CONTINUABLE, EXIT_CODE, EXIT_MESSAGE) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null; nested exception is com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null

SQL 302 in DB2 means:

The value of an input host variable was found to be too large for its use in the SELECT, VALUES, or prepared statement. One of the following occurred:

The corresponding host variable or parameter marker used in the SQL statement is defined as string, but the input host variable contains a string that is too long.
The corresponding host variable or parameter marker used in the SQL statement is defined as numeric, but the input host variable contains a numeric value that is too large.

But I don't know what is the violating input variables....any idea?

I saw a similar problem posted about Spring Batch can't insert a STEP EXECUTION record

http://jira.springframework.org/browse/BATCH-360

It is different error messages so I don't know if they are the same problem. Nonetheless, am I fighting a losing battle with m4 ...should I try m5? What is the current status of m4 working with DB2 at all?

[joelee]

Ok, so I tried m5 now. Did everything I did to fix it up.....rerun the schema.sql as well as the business-schema.sql for db2.

I changed to use the org.springframework.jdbc.support.incrementer.DB2Se quenceMaxValueIncrementer....

now I got this:

org.springframework.dao.DataAccessResourceFailureE xception: Could not obtain identity(); nested exception is com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: DB2INST1.BATCH_JOB_SEQ
at org.springframework.jdbc.support.incrementer.HsqlM axValueIncrementer.getNextKey(HsqlMaxValueIncremen ter.java:157)
at org.springframework.jdbc.support.incrementer.Abstr actDataFieldMaxValueIncrementer.nextLongValue(Abst ractDataFieldMaxValueIncrementer.java:103)


first I don't know why HsqlMaxValueIncrementer showed up. I am sure DB2SequenceMaxValueIncrementer is the batch.properties is effective, because if I just uncomment #incrementerParent.columnName=ID, it will complain DB2SequenceMaxValueIncrementer does not have a property named columnName.....

[lucasward]

Precisely because of the problem you mentioned above with the incrementers, we switched to use a factory bean for the JobRepository. Change the databaseType property to "db2" and you should be fine.

Sorry for the confusion.

[joelee]

Precisely because of the problem you mentioned above with the incrementers, we switched to use a factory bean for the JobRepository. Change the databaseType property to "db2" and you should be fine.

Sorry for the confusion.


THANKS! That's it...

<bean id="jobRepository" class="org.springframework.batch.execution.repository.Job RepositoryFactoryBean"
p:databaseType="db2" p:dataSource-ref="dataSource" />

I didn't realize there is a databaseType property here before. Thanks for pointing it out :)

[kdandu]

In the same breath as a table prefix, is there a property that tells 'Spring Batch' to use a specific schema, this is the schema where spring batch tables are present(compared to default schema that is user name in DB2)

[lucasward]

Using the table prefix should help with your schema issue as well, if I'm not mistaken. Just set the prefix as something like 'OTHERSCHEMA.batch'

If that doesn't work, you can always wrap connections to change the current schema to the one you want batch to be working with. There's a db2 specific command for that, but I've forgotten it. Something like 'set schema OTHERSCHEMA'

[rajasekhar17]

Hello All,
I'm trying to execute a simple example in db2 using CommandLineJobRunner
my batch-db2.propertis are defined as bellow.
batch.jdbc.driver=com.ibm.db2.jcc.DB2Driver
batch.jdbc.url=jdbc:db2://db2dev1:50000/DB2DF
batch.jdbc.user=@DB2SLCD
batch.jdbc.password=aaaaa
batch.schema=
#batch.jndi.name=jdbc/dscapp/db2
batch.naming.factory.initial=
batch.naming.provider.url=
batch.schema.script=schema-db2.sql
batch.business.schema.script=business-schema-db2.sql
batch.database.incrementer.class=
org.springframewo rk.jdbc.support.incrementer.DB2 .SequenceMaxValueIncrementer
batch.lob.handler.class=
org.springframework.jdbc.s upport.lob.DefaultLobHandler.

When I run the application, I'm getting the following exception. It is complaining about "sequence value not found".

17:08:46,267 INFO main SimpleJobLauncher:130 - No TaskExecutor has been set, defaulting to synchronous executor.
17:08:46,533 INFO main CommandLineJobRunner:191 - XML Application Context is loaded
17:08:46,533 INFO main CommandLineJobRunner:203 - Job Name islistJob
17:08:46,814 ERROR main CommandLineJobRunner:212 - Job Terminated in error:
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)
... 17 more


************************************************** *****


Now I tried again by adding the following property in batch-db2.propertis.
incrementerParent.nextKey =1
However, it is complaining that, nextKey is an invalid property.Exception is as follows.


org.springframework.beans.InvalidPropertyException : Invalid property 'nextKey' of bean class [org.springframework.jdbc.support.incrementer.DB2Se quenceMaxValueIncrementer]: No property 'nextKey' found
at org.springframework.beans.BeanWrapperImpl.convertF orProperty(BeanWrapperImpl.java:382)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.convertForProperty(Abst ractAutowireCapableBeanFactory.java:1288)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.applyPropertyValues(Abs tractAutowireCapableBeanFactory.java:1249)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.populateBean(AbstractAu towireCapableBeanFactory.java:1010)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.doCreateBean(AbstractAu towireCapableBeanFactory.java:472)
... 15 more


Please suggest me the right way to fix this problem.
Thanks
Hey jolee,
seems like you already faced this problem, and managed to fix it.
Any suggestions?
Any suggestions are highly appreciated.