Announcement Announcement Module
Collapse
No announcement yet.
JdbcCursorItemReader - Connection handing Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JdbcCursorItemReader - Connection handing

    Hi All,

    I have a job with JdbcCursorItemReader, read a data from the sql server 2005 database tables, validate data and write in my business table.
    Voume of data is around 2 -3 million.

    My commit intervel is 500.
    DataSource : org.apache.commons.dbcp.BasicDataSource.
    Spring batch 2.1
    hibernate3

    Code:
    <bean id="stepListQueryReader"
    		class="org.springframework.batch.item.database.JdbcCursorItemReader"
    		scope="step">
    		<property name="dataSource" ref="dataSource" />
    		<property name="rowMapper">
    			<bean class="xxy.batch.core.mapper.StepListRowMapper" />
    		</property>
    		<property name="sql" ref="#{jobParameters['stepName']}" />
    	</bean>
    After processing around 1 million record, i am getting connection reset execption.
    How to handle this?
    JdbcCursorItemReader will open a connection and use it. For a long running batch this connection active for more than 3 hours. The SQL server 2005 will keep the connection open for 7,200,000 milliseconds (2 hours) only right.

    In this case JdbcCursorItemReader will sure throw the "Connection Reset Exception".

    Code:
    [2012-02-08 14:25:20,462] [Thread-3] ERROR org.springframework.batch.core.step.AbstractStep  - Encountered an error executing the step
    org.springframework.dao.DataAccessResourceFailureException: Attempt to process next row failed; SQL [
    			SELECT  ReportId AS Key, ReportId AS Sort FROM  
    			PBTT
    			]; Connection reset; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
    	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:103)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    	at org.springframework.batch.item.database.AbstractCursorItemReader.doRead(AbstractCursorItemReader.java:450)
    	at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.read(AbstractItemCountingItemStreamItemReader.java:87)
    	at sun.reflect.GeneratedMethodAccessor38.invoke(Unknown Source)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    	at java.lang.reflect.Method.invoke(Unknown Source)
    If i am wrong please correct me.
    How to handle this issue?

    Thanks for your reply in advance.
Working...
X