Error with DB2 connection

[montoya83]

Hi, I'm novice with spring and spring batch frameworks.
In my first examples with spring framework, I've obtained something of obscure for me...

Look this error trace:

3-gen-2011 16.40.00 org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@171732b: startup date [Mon Jan 03 16:40:00 CET 2011]; root of context hierarchy
3-gen-2011 16.40.00 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [applicationContext.xml]
3-gen-2011 16.40.00 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [decodificaFiliali.xml]
3-gen-2011 16.40.00 org.springframework.beans.factory.support.DefaultListableBeanFactory registerBeanDefinition
INFO: Overriding bean definition for bean 'job': replacing [Generic bean: class [org.springframework.batch.core.configuration.xml.SimpleFlowFactoryBean]; scope=; abstract=false; lazyInit=false; autowireMode=0; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=null; factoryMethodName=null; initMethodName=null; destroyMethodName=null] with [Generic bean: class [org.springframework.batch.core.configuration.xml.JobParserJobFactoryBean]; scope=; abstract=false; lazyInit=false; autowireMode=0; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=null; factoryMethodName=null; initMethodName=null; destroyMethodName=null]
3-gen-2011 16.40.00 org.springframework.core.io.support.PropertiesLoaderSupport loadProperties
INFO: Loading properties file from class path resource [jdbc.properties]
3-gen-2011 16.40.00 org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@77a7f9: defining beans [org.springframework.batch.core.scope.internalStepScope,org.springframework.beans.factory.config.CustomEditorConfigurer,org.springframework.batch.core.configuration.xml.CoreNamespacePostProcessor,jobRepository,jobLauncher,org.springframework.batch.core.explore.support.JobExplorerFactoryBean#0,propertyConfigurer,dataSourceDB2,dataSourceDBCP,transactionManager,step,job,reader,processor,writer]; root of factory hierarchy
3-gen-2011 16.40.00 org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.ibm.db2.jcc.DB2Driver
3-gen-2011 16.40.00 org.springframework.batch.core.repository.support.JobRepositoryFactoryBean afterPropertiesSet
INFO: No database type set, using meta data indicating: DB2ZOS
3-gen-2011 16.40.01 org.springframework.batch.core.launch.support.SimpleJobLauncher afterPropertiesSet
INFO: No TaskExecutor has been set, defaulting to synchronous executor.
3-gen-2011 16.40.08 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
3-gen-2011 16.40.08 org.springframework.jdbc.support.SQLErrorCodesFactory <init>
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
3-gen-2011 16.40.08 org.springframework.batch.core.launch.support.CommandLineJobRunner start
GRAVE: Job Terminated in error: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT JOB_INSTANCE_ID, JOB_NAME from CODF.BATCH_JOB_INSTANCE where JOB_NAME = ? order by JOB_INSTANCE_ID desc]; SQL state [null]; error code [-99999]; Invalid operation: result set closed; nested exception is com.ibm.db2.jcc.a.SqlException: Invalid operation: result set closed
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT JOB_INSTANCE_ID, JOB_NAME from CODF.BATCH_JOB_INSTANCE where JOB_NAME = ? order by JOB_INSTANCE_ID desc]; SQL state [null]; error code [-99999]; Invalid operation: result set closed; nested exception is com.ibm.db2.jcc.a.SqlException: Invalid operation: result set closed
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
	at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.getJobInstances(JdbcJobInstanceDao.java:333)
	at org.springframework.batch.core.explore.support.SimpleJobExplorer.getJobInstances(SimpleJobExplorer.java:160)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.getJobExecutionsWithStatusGreaterThan(CommandLineJobRunner.java:401)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.getLastFailedJobExecution(CommandLineJobRunner.java:410)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.start(CommandLineJobRunner.java:324)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.main(CommandLineJobRunner.java:565)
Caused by: com.ibm.db2.jcc.a.SqlException: Invalid operation: result set closed
	at com.ibm.db2.jcc.a.lf.lb(lf.java:3503)
	at com.ibm.db2.jcc.a.lf.c(lf.java:299)
	at com.ibm.db2.jcc.a.lf.next(lf.java:285)
	at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao$3.extractData(JdbcJobInstanceDao.java:322)
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:648)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
	... 9 more

Can you help me?
Thanks
Regards.

Francesco Betti Sorbelli

[montoya83]

If this is interest for you, I've obtained also the DB2 Connection log with "-1" traceLevel (ALL):

...
[ibm][db2][jcc][Time:1294069208508][Thread:main][ResultSet@1e940b] next () returned true
[ibm][db2][jcc][Time:1294069208508][Thread:main][ResultSet@1e940b] next () called
[ibm][db2][jcc][Time:1294069208508][Thread:main][ResultSet@1e940b] next () returned true
[ibm][db2][jcc][Time:1294069208508][Thread:main][ResultSet@1e940b] next () called
[ibm][db2][jcc] [t4][time:1294069208508][thread:main][tracepoint:1][Request.flush]
[ibm][db2][jcc][t4]        SEND BUFFER: RDBCMM                 (ASCII)           (EBCDIC)
[ibm][db2][jcc][t4]        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
[ibm][db2][jcc][t4] 0000   000AD00100010004  200E              ........ .        ..}.......      
[ibm][db2][jcc][t4] 
[ibm][db2][jcc] [t4][time:1294069208508][thread:main][tracepoint:2][Reply.fill]
[ibm][db2][jcc][t4]        RECEIVE BUFFER: ENDUOWRM            (ASCII)           (EBCDIC)
[ibm][db2][jcc][t4]        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
[ibm][db2][jcc][t4] 0000   0030D0520001002A  220C000611490004  .0.R...*"....I..  ..}.............
[ibm][db2][jcc][t4] 0010   0005211501001621  10C4C2C640404040  ..!....!....@@@@  .........DBF    
[ibm][db2][jcc][t4] 0020   4040404040404040  4040400005119FF0  @@@@@@@@@@@.....             ....0
[ibm][db2][jcc][t4] 
[ibm][db2][jcc][t4]        RECEIVE BUFFER: SQLCARD             (ASCII)           (EBCDIC)
[ibm][db2][jcc][t4] 0000   000BD00300010005  2408FF            ........$..       ..}........     
[ibm][db2][jcc][t4] 
[ibm][db2][jcc][Connection@6ffd79] DB2 LUWID: GAF2CEC7.C203.110103154305.0001
[ibm][db2][jcc][Time:1294069208508][Thread:main][ResultSet@1e940b] next () returned false
[ibm][db2][jcc][Time:1294069208508][Thread:main][ResultSet@1e940b] next () called
[ibm][db2][jcc] BEGIN TRACE_DIAGNOSTICS
[ibm][db2][jcc][Thread:main][SQLException@5ff916] java.sql.SQLException
[ibm][db2][jcc][Thread:main][SQLException@5ff916] SQL state  = null
[ibm][db2][jcc][Thread:main][SQLException@5ff916] Error code = -99999
[ibm][db2][jcc][Thread:main][SQLException@5ff916] Message    = Invalid operation: result set closed
[ibm][db2][jcc][Thread:main][SQLException@5ff916] Stack trace follows
com.ibm.db2.jcc.a.SqlException: Invalid operation: result set closed
	at com.ibm.db2.jcc.a.lf.lb(lf.java:3503)
	at com.ibm.db2.jcc.a.lf.c(lf.java:299)
	at com.ibm.db2.jcc.a.lf.next(lf.java:285)
	at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao$3.extractData(JdbcJobInstanceDao.java:322)
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:648)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
	at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.getJobInstances(JdbcJobInstanceDao.java:333)
	at org.springframework.batch.core.explore.support.SimpleJobExplorer.getJobInstances(SimpleJobExplorer.java:160)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.getJobExecutionsWithStatusGreaterThan(CommandLineJobRunner.java:401)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.getLastFailedJobExecution(CommandLineJobRunner.java:410)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.start(CommandLineJobRunner.java:324)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.main(CommandLineJobRunner.java:565)
[ibm][db2][jcc] END TRACE_DIAGNOSTICS
[ibm][db2][jcc][Time:1294069208508][Thread:main][ResultSet@1e940b] close () called
[ibm][db2][jcc][Time:1294069208508][Thread:main][PreparedStatement@6a435f] close () called
[ibm][db2][jcc][Time:1294069208508][Thread:main][Connection@6ffd79] close () called
[ibm][db2][jcc] [t4][time:1294069208508][thread:main][tracepoint:1][Request.flush]
[ibm][db2][jcc][t4]        SEND BUFFER: RDBCMM                 (ASCII)           (EBCDIC)
[ibm][db2][jcc][t4]        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
[ibm][db2][jcc][t4] 0000   000AD00100010004  200E              ........ .        ..}.......      
[ibm][db2][jcc][t4] 
[ibm][db2][jcc] [t4][time:1294069208515][thread:main][tracepoint:2][Reply.fill]
[ibm][db2][jcc][t4]        RECEIVE BUFFER: ENDUOWRM            (ASCII)           (EBCDIC)
[ibm][db2][jcc][t4]        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
[ibm][db2][jcc][t4] 0000   0030D0520001002A  220C000611490004  .0.R...*"....I..  ..}.............
[ibm][db2][jcc][t4] 0010   0005211501001621  10C4C2C640404040  ..!....!....@@@@  .........DBF    
[ibm][db2][jcc][t4] 0020   4040404040404040  4040400005119FF0  @@@@@@@@@@@.....             ....0
[ibm][db2][jcc][t4] 
[ibm][db2][jcc][t4]        RECEIVE BUFFER: SQLCARD             (ASCII)           (EBCDIC)
[ibm][db2][jcc][t4] 0000   000BD00300010005  2408FF            ........$..       ..}........     
[ibm][db2][jcc][t4] 
[ibm][db2][jcc][Connection@6ffd79] DB2 LUWID: GAF2CEC7.C203.110103154305.0002

Bye
Francesco Betti Sorbelli

[Hansie]

Hi,

I am having exactly the same problem. Someone a clue what the problem could be?

Thanks.
Hans

[michael.lange]

Can you both post your spring xml config ?

[Hansie]

Here it is:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx" xmlns:batch="http://www.springframework.org/schema/batch"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schem...-beans-2.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schem...ing-tx-2.5.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schem...ng-aop-2.1.xsd
http://www.springframework.org/schema/batch http://www.springframework.org/schem...-batch-2.1.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">


<context:component-scan base-package="com.t.c.batch.menupricing" />
<context:component-scan base-package="com.c.services" />
<context:component-scan base-package="com.t.c.batch.test" />

<bean id="placeholderProperties"
class="org.springframework.beans.factory.config.Pr opertyPlaceholderConfigurer">
<property name="location" value="classpath:springbatch_local.properties" />
<property name="systemPropertiesModeName" value="SYSTEM_PROPERTIES_MODE_OVERRIDE" />
<property name="ignoreUnresolvablePlaceholders" value="true" />
<property name="order" value="1" />
</bean>

<import
resource="classpath:/com/t/c/batch/menupricing/springbeans/MPdescriptions.xml" />
<import
resource="classpath:/com/t/c/batch/menupricing/springbeans/MPModel.xml" />
<import
resource="classpath:/com/t/c/batch/menupricing/springbeans/MenuLabor.xml" />
<import
resource="classpath:/com/t/c/batch/menupricing/springbeans/MenuPart.xml" />
<import
resource="classpath:/com/t/c/batch/menupricing/springbeans/MenuSundry.xml" />

<import resource="classpath:/applicationContext-jpa.xml" />
<import resource="classpath:/applicationContext-services.xml" />
<import resource="classpath:/applicationContext-no-JMS.xml" />


<bean id="cJobIncrementer" class="com.t.c.batch.menupricing.utils.CJobIncreme nter" />

<batch:job id="TESTMENUPRIC" incrementer="cJobIncrementer">
<batch:listeners>
<batch:listener ref="appJobExecutionListener" />
</batch:listeners>
<batch:step id="MpDescriptLStep" next="MpDescriptTStep">
<batch:tasklet allow-start-if-complete="true">
<batch:listeners>
<batch:listener ref="itemFailureLoggerListener" />
</batch:listeners>
<batch:chunk reader="MenuPricingDescriptionsLItemReader"
writer="MenuPricingDescriptionWriter" commit-interval="100" />
</batch:tasklet>
</batch:step>

<batch:step id="MpDescriptTStep" next="MpSequenceNumberStep">
<batch:tasklet allow-start-if-complete="true">
<batch:listeners>
<batch:listener ref="itemFailureLoggerListener" />
</batch:listeners>
<batch:chunk reader="MenuPricingDescriptionsTItemReader"
writer="MenuPricingDescriptionWriter" commit-interval="100" />
</batch:tasklet>
</batch:step>

<batch:step id="MpSequenceNumberStep" next="MpModelLStep">
<batch:tasklet ref="mpSequenceNumberTasklet">
<batch:listeners>
<batch:listener ref="promotionListener" />
</batch:listeners>
</batch:tasklet>
</batch:step>

<batch:step id="MpModelLStep" next="MpLaborLStep">
<batch:tasklet>
<batch:listeners>
<batch:listener ref="itemFailureLoggerListener" />
</batch:listeners>
<batch:chunk reader="MenuPricingModelLItemReader"
writer="MenuPricingModelitemWriter" commit-interval="100" />
<batch:no-rollback-exception-classes>
<batch:include
class="org.springframework.batch.item.validator.Va lidationException" />
</batch:no-rollback-exception-classes>
</batch:tasklet>
</batch:step>


<batch:step id="MpLaborLStep" next="MpSequenceNumberUpdateStep">
<batch:tasklet>
<batch:listeners>
<batch:listener ref="itemFailureLoggerListener" />
<batch:listener ref="skipListener" />
</batch:listeners>
<batch:chunk reader="MenuPricingSundryLItemReader"
processor="MenuPricingSundryProcessor" writer="MenuPricingSundryItemWriter"
commit-interval="100" skip-limit="100000000">
<batch:skippable-exception-classes>
<batch:include
class="org.springframework.batch.item.validator.Va lidationException" />
<batch:include
class="org.springframework.dao.DataIntegrityViolat ionException" />

</batch:skippable-exception-classes>
</batch:chunk>
<batch:no-rollback-exception-classes>
<batch:include
class="org.springframework.batch.item.validator.Va lidationException" />
</batch:no-rollback-exception-classes>
</batch:tasklet>
</batch:step>

<batch:step id="MpSequenceNumberUpdateStep">
<batch:tasklet ref="mpSequenceNumberUpdateTasklet">
</batch:tasklet>
</batch:step>

</batch:job>


<bean id="mpSequenceNumberTasklet"
class="com.t.c.batch.menupricing.general.MpSequenc eNumberRetrievalTasklet">
</bean>

<bean id="mpSequenceNumberUpdateTasklet"
class="com.t.c.batch.menupricing.general.MpSequenc eNumberUpdateTasklet">
</bean>


<bean id="skipListener" class="com.t.c.batch.menupricing.utils.ItemSkipLis tener">
</bean>

<bean id="macoserveBo" class="com.t.c.batch.menupricing.macoserv.MacoServ eBo">
<property name="macoServeDao">
<bean class="com.t.c.batch.menupricing.macoserv.MacoServ eDaoImpl">
<property name="dataSource" ref="dataSource" />
</bean>
</property>
</bean>

<bean id="menuDescriptions"
class="com.t.c.batch.menupricing.menudescription.M enuDescriptions">
</bean>

<bean id="menupricingLaborBo"
class="com.t.c.batch.menupricing.menulabor.MenuPri cingLaborBo">
<property name="menuPricingLaborDao">
<bean
class="com.t.c.batch.menupricing.menulabor.MenuPri cingLaborDaoImpl">
<property name="dataSource" ref="dataSource" />
</bean>
</property>
</bean>

<bean id="sessionId" class="com.t.c.batch.sb.CSessionSetup"
init-method="init">
<property name="job" ref="TESTMENUPRIC" />
</bean>


<bean id="jobRepository"
class="org.springframework.batch.core.repository.s upport.JobRepositoryFactoryBean">
<property name="transactionManager" ref="transactionManager" />
<property name="isolationLevelForCreate" value="ISOLATION_DEFAULT" />
<property name="dataSource" ref="dataSource" />
<property name="tablePrefix" value="${batch.schema}.BATCH_" />
</bean>

<bean id="jobLauncher"
class="org.springframework.batch.core.launch.suppo rt.SimpleJobLauncher">
<property name="jobRepository" ref="jobRepository" />
</bean>

<bean id="jobOperator"
class="org.springframework.batch.core.launch.suppo rt.SimpleJobOperator"
p:jobLauncher-ref="jobLauncher" p:jobExplorer-ref="jobExplorer"
p:jobRepository-ref="jobRepository" p:jobRegistry-ref="jobRegistry" />

<bean id="jobExplorer"
class="org.springframework.batch.core.explore.supp ort.JobExplorerFactoryBean"
p:dataSource-ref="dataSource" p:tablePrefix="${batch.schema}.BATCH_" />

<bean id="jobRegistry"
class="org.springframework.batch.core.configuratio n.support.MapJobRegistry">
</bean>

<bean id="jobRegistryBeanPostProcessor"
class="org.springframework.batch.core.configuratio n.support.JobRegistryBeanPostProcessor">
<property name="jobRegistry" ref="jobRegistry" />
</bean>


<bean id="promotionListener"
class="org.springframework.batch.core.listener.Exe cutionContextPromotionListener">
<property name="keys" value="mplVersionSysParam" />
</bean>


</beans>


And relevent excerpt from applicationContext-jpa.xml where dataSource is defined:

<bean id="dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource"
p:driverClass="${jdbc.driverClass}"
p:jdbcUrl="${jdbc.url}"
p:user="${jdbc.user}"
password="${jdbc.password}"
p:initialPoolSize="${pool.initialSize}"
p:minPoolSize="${pool.minSize}"
p:maxPoolSize="${pool.maxSize}"
p:maxStatementsPerConnection="120"
p:idleConnectionTestPeriod="14400"
p:maxIdleTime="25200"
preferredTestQuery="select 1;"
destroy-method="close"/>