Getting below error while executing a batch job. Not immediate but after half an hour.
2016-02-17 15:24:25,106 ERROR [AbstractStep ][poolTaskExecutor-2 ] Encountered an error executing step processSubscriptionFile-stepTwo in job subscriptionJob
org.springframework.jdbc.UncategorizedSQLException: Attempt to process next row failed; uncategorized SQLException for SQL [
SELECT DISTINCT PERSON_ID FROM CUST_SUB_INTER where job_id = 77317301 order by PERSON_ID
]; SQL state [99999]; error code [17010]; Closed Resultset: next; nested exception is java.sql.SQLException: Closed Resultset: next
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at
My batch job files is as below. In first step I am reading the data from a file and adding to an intermediate table. This chunk uses partition and multiple threads. This step uses partitions and threads. In second step I am reading the data from this temporary table as distinct customer ids and passing it to writer. It is failing halfway through. There are 70000 records in intermediate table and 10000 distinct customer ids. Second step does not use partition and threads. Please help
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:batch="http://www.springframework.org/schema/batch"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/batch http://www.springframework.org/schema/batch/spring-batch.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"
default-autowire="byName">
<bean id="SubscriptionJob-jobDetail" class="org.springframework.scheduling.quartz.JobDetailBean">
<property name="jobClass" value="uk.co.batch.datamigration.JobLaunchDetails"/>
<property name="jobDataAsMap">
<map>
<entry key="jobName" value="SubscriptionJob" />
<entry key="jobLocator" value-ref="jobRegistry" />
<entry key="jobLauncher" value-ref="jobLauncher" />
</map>
</property>
</bean>
<!-- Job -->
<batch:job id="SubscriptionJob" restartable="false">
<batch:step id="copySubscriptionFile" next="processSubscriptionFile">
<tasklet ref="copySubscriptionFileLocally" />
</batch:step>
<batch:step id="processSubscriptionFile" next="processSubscriptionFile-stepTwo">
<partition step="processSubscriptionFile-stepOne" partitioner="SubscriptionPartitioner">
<handler grid-size="20" task-executor="SubscriptionTaskExecutor"/>
</partition>
</batch:step>
<batch:step id="processSubscriptionFile-stepTwo">
<batch:tasklet>
<batch:chunk reader="SubscriptionItemStepTwoReader" writer="SubscriptionItemStepTwoWriter" commit-interval="200"/>
</batch:tasklet>
</batch:step>
<batch:listeners>
<batch:listener ref="SubscriptionJobListener"/>
<batch:listener ref="SubscriptionJobNotifier"/>
</batch:listeners>
</batch:job>
<batch:step id="processSubscriptionFile-stepOne">
<batch:tasklet>
<batch:chunk reader="SubscriptionItemStepOneReader" writer="SubscriptionItemStepOneWriter" commit-interval="200"/>
</batch:tasklet>
</batch:step>
<bean id ="SubscriptionJobNotifier" class="uk.co.and.batch.status.JobExecutionNotifier">
<property name="snsEventType" value="SUBSCRIPTION_JOB_NOTIFICATION"/>
<property name="onlyNotifyFailures" value="true"/>
</bean>
<bean id="SubscriptionPartitioner" class="uk.co.batch.datamigration.FlatFilePartitioner" scope="step">
<property name="resource" value="file:#{jobExecutionContext[tempSubscriptionFile]}" />
</bean>
<bean id="SubscriptionItemStepOneReader" scope="step" autowire-candidate="false" parent="SubscriptionItemStepOneReaderParent">
<property name="resource" value="file:#{jobExecutionContext[tempSubscriptionFile]}" />
<property name="startAt" value="#{stepExecutionContext['startAt']}"/>
<property name="itemsCount" value="#{stepExecutionContext['itemsCount']}"/>
</bean>
<bean id="SubscriptionItemStepOneReaderParent" abstract="true"
class="uk.co.batch.datamigration.MultiThreadedFlatFileItemReader">
<property name="linesToSkip" value="1"/>
<property name="lineMapper">
<bean class="org.springframework.batch.item.file.mapping.DefaultLineMapper">
<property name="lineTokenizer">
<bean class="org.springframework.batch.item.file.transform.DelimitedLineTokenizer">
<property name="delimiter" value="^"/>
<property name="names">
<list >
<value>city_name</value>
<value>country_name</value>
<value>publication_name</value>
<value>subscription_ref_code</value>
<value>subscription_state</value>
<value>unsubscribed</value>
<value>city_id</value>
<value>person_id</value>
<value>subscription_created_at</value>
<value>subscription_updated_at</value>
<value>end_value</value>
</list>
</property>
</bean>
</property>
<property name="fieldSetMapper">
<bean class="org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper">
<property name="prototypeBeanName" value="SubscriptionVO"/>
</bean>
</property>
</bean>
</property>
<property name="recordSeparatorPolicy">
<bean class="org.springframework.batch.item.file.separator.DefaultRecordSeparatorPolicy"/>
</property>
</bean>
<bean id="SubscriptionVO" class="uk.co.batch.datamigration.subscription.SubscriptionVO" scope="prototype"/>
<!-- TODO:Reduce number of columns to improve performance -->
<bean id="SubscriptionItemStepOneWriter"
class="org.springframework.batch.item.database.JdbcBatchItemWriter" scope="step">
<property name="dataSource" ref="dataSource" />
<property name="sql">
<value>
<![CDATA[
insert into CUST_SUB_INTER(ID,CITY_NAME,COUNTRY_NAME,PUBLICATION_NAME,SUBSCRIPTION_REF_CODE,SUBSCRIPTION_STATE,
UNSUBSCRIBED,CITY_ID,PERSON_ID,SUBSCRIPTION_CREATED_AT,SUBSCRIPTION_UPDATED_AT,END_VALUE,JOB_ID,
MIGRATION_FILE_NAME,RECORD_INSERT_DATE)
values (SEQ_CUST_SUB_ERROR.NEXTVAL, :city_name, :country_name, :publication_name,
:subscription_ref_code, :subscription_state, :unsubscribed,
:city_id, :person_id, :subscription_created_at, :subscription_updated_at, :end_value,
#{jobExecutionContext[jobId]}, '#{jobExecutionContext[tempSubscriptionFile]}', SYSTIMESTAMP)
]]>
</value>
</property>
<property name="itemSqlParameterSourceProvider">
<bean class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />
</property>
</bean>
<!-- scope step is critical here-->
<bean id="SubscriptionItemStepTwoReader"
class="org.springframework.batch.item.database.JdbcCursorItemReader" scope="step">
<property name="dataSource" ref="dataSource"/>
<property name="sql">
<value>
<![CDATA[
SELECT DISTINCT PERSON_ID FROM CUST_SUB_INTER where job_id = #{jobExecutionContext.jobId} order by PERSON_ID
]]>
</value>
</property>
<property name="rowMapper">
<bean class="org.springframework.jdbc.core.SingleColumnRowMapper" p:requiredType="java.lang.Long"/>
</property>
</bean>
<bean id="SubscriptionItemStepTwoWriter" class="uk.co.batch.datamigration.subscription.SubscriptionItemStepTwoWriter" scope="step">
<property name="fileName" value="#{jobExecutionContext[tempSubscriptionFile]}" />
<property name="jobId" value="#{jobExecutionContext.jobId}" />
<property name="errorSaving" value="${Job.errorSaving}"/>
</bean>
<bean id="SubscriptionTaskExecutor"
class="org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor">
<property name="corePoolSize" value="10"/>
</bean>
<bean id="SubscriptionJobListener" class="uk.co.batch.datamigration.JobListener">
<property name="sessionFactory" ref="sftpSessionFactory"/>
<property name="remotePath" value="${Job.subscription.sftp.remoteDirectory}"/>
<property name="remoteExtension" value="${Job.sftp.remoteExtension}"/>
<property name="renameRemoteFile" value="true"/>
<property name="jobContextSingleFileParameterName" value="tempSubscriptionFile"/>
<property name="batchAdministratorUserId" value="${Job.batchAdministratorUserId}"/>
</bean>
<bean id="copySubscriptionFileLocally" class="uk.co.and.batch.core.LocalCopyTasklet" scope="prototype">
<property name="deleteRemoteFile" value="false"/>
<property name="fileNamePattern" value="${Job.subscription.filePattern}"/>
<property name="jobContextSingleFileParameterName" value="tempSubscriptionFile"/>
<property name="localPath" value="${Job.subscription.sftp.localDirectory}"/>
<property name="mustMatch" value="true"/>
<property name="remotePath" value="${Job.subscription.sftp.remoteDirectory}"/>
<property name="remoteSessionFactory" ref="sftpSessionFactory"/>
<property name="maxNumberDownload" value="1"/>
<property name="resolver">
<bean class="uk.co.and.batch.core.remote.FileNameResolvers.SFtpFileNameResolver" />
</property>
</bean>
<bean id="sftpSessionFactory" class="org.springframework.integration.sftp.session.DefaultSftpSessionFactory">
<property name="host" value="ftp.co.uk"/>
<property name="port" value="22"/>
<property name="user" value=""/>
<property name="password" value=""/>
<property name="proxy" value="#{ ${proxyRef} }"/>
</bean>
</beans>
