Spring Batch - how to COMPLETE a job when the JdbcPagingItemReader returns no data

Question

My question is reverse of this existing SO question.

Behavior of JdbcPagingItemReader seems reverse of what is being described in that question i.e. job is marked as FAILED if JdbcPagingItemReader doesn't find any records.

Logs indicate that Job is marked as FAILED because reader couldn't fetch pages from page 1 on wards and SELECT fails due to SQLCODE=-313 i.e.

-313 THE NUMBER OF HOST VARIABLES SPECIFIED IS NOT EQUAL TO THE NUMBER OF PARAMETER MARKERS

So overall step is marked as failed leading to job being failed.

For query from page 1 on wards, sort key is included in SELECT like PAYMENT_ID > ? and I guess since there are no PAYMENT_IDs , value for placeholder is not found so error.

How can I ignore this error and mark job as COMPLETE in this particular scenario?

I tried solution specified in Trever Shick's answer in other question and returning

if(stepExecution.getReadCount() == 0 ){
            return ExitStatus.COMPLETED;
        }

is not fixing the issue.

Both Chunk Size and Reader page sizes are equal to 10 & THROTTLE_LIMIT=20.

@Bean
    public Step step1(StepBuilderFactory stepBuilderFactory,
            ItemReader<RemittanceVO> syncReader, ItemWriter<RemittanceClaimVO> writer,
            ItemProcessor<RemittanceVO, RemittanceClaimVO> processor) {

        return stepBuilderFactory.get("step1")
                .<RemittanceVO, RemittanceClaimVO> chunk(Constants.SPRING_BATCH_CHUNK_SIZE)
                .reader(syncReader)
                .listener(afterReadListener)
                .processor(processor)
                .writer(writer)
                .taskExecutor(simpleAsyntaskExecutor)
                .throttleLimit(Constants.THROTTLE_LIMIT)
                .build();
    }

Not full stack trace but a line from logs ,

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT PAYMENT_ID,INSURED_LAST_NM,INSURED_FIRST_NM,LAST_NM,FIRST_NM,CONTRACT_NUM,CARRIER_CD,CARRIER_GROUP,CONTRACT_NUM,REL_AR_SEQ_NUM,FROM_DOS_DT,THRU_DOS_DT,BILL_AMT,RX_NUM_BP,CERT_NUM_LEFT_BP,MODIFIER,PROC_CD ,DEPOSIT_ID,PRNT_CONTRACT_NUM,REMIT_TYPE_CD AS RMT_TYPE FROM AR.PAYMENTS WHERE (CONTRACT_NUM IN (SELECT CONTRACT_NUM FROM AR.PAYMENTS WHERE RMTST_RF='M' AND DELETE_IND='N' GROUP BY CONTRACT_NUM ) AND DELETE_IND='N' AND RMTST_RF='M') AND ((PAYMENT_ID > ?)) ORDER BY PAYMENT_ID ASC FETCH FIRST 50 ROWS ONLY]; nested exception is com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-313, SQLSTATE=07004, SQLERRMC=null, DRIVER=4.11.77

Column PAYMENT_ID is my sort key and section AND ((PAYMENT_ID > ?)) has been added by spring batch.

My reader beans ,

@Bean
    public ItemReader<RemittanceVO> syncReader() {
        SynchronizedItemStreamReader<RemittanceVO> syncReader = new SynchronizedItemStreamReader<RemittanceVO>();
        syncReader.setDelegate(reader());
        return syncReader;
    }

    @Bean
    public ItemStreamReader<RemittanceVO> reader() {
        JdbcPagingItemReader<RemittanceVO> reader = new JdbcPagingItemReader<RemittanceVO>();
        reader.setDataSource(dataSource);
        reader.setRowMapper(new RemittanceRowMapper());
        reader.setQueryProvider(queryProvider);
        reader.setPageSize(Constants.SPRING_BATCH_READER_PAGE_SIZE);
        return reader;
    }

Query provider bean ,

@Bean
    public PagingQueryProvider queryProvider() throws Exception{
        SqlPagingQueryProviderFactoryBean queryProviderBean= new SqlPagingQueryProviderFactoryBean();
        queryProviderBean.setDataSource(dataSource);
        queryProviderBean.setDatabaseType("DB2");
        queryProviderBean.setSelectClause(Constants.REMITTANCES_SELECT_CLAUSE);
        queryProviderBean.setFromClause(Constants.REMITTANCES_FROM_CLAUSE);
        queryProviderBean.setWhereClause(Constants.REMITTANCES_WHERE_CLAUSE);
        queryProviderBean.setSortKey(Constants.REMITTANCES_SORT_KEY);
        PagingQueryProvider queryProvider = queryProviderBean.getObject();
        return queryProvider;
    }

Condition, `stepExecution.getReadCount() == 0` remains true if this scenario is encountered but framework doesn't recognize `ExitStatus` set or returned by `Step` or `Job` 's listeners. — Sabir Khan, Aug 31 '16 at 07:50
I meant to say that the values dumped in debug logs by framework are same - no impact of setting or returning different values. than original ones. — Sabir Khan, Aug 31 '16 at 08:02
I'm same issue, Could you please update the solution if you found any — vsr, Aug 03 '18 at 10:58
@vsr: Thanks for asking. See if my answer helps. I recall doing similar thing but not 100% sure. — Sabir Khan, Aug 03 '18 at 11:25

score 1 · Answer 1 · answered Aug 03 '18 at 11:23

I am not able to recall for sure but I think , I did below in step & job listeners.

I basically wrote a StepExecutionListener and overridden afterStep method as below,

@Override
        public ExitStatus afterStep(StepExecution stepExecution) {
        if (stepExecution.getReadCount() == 0) {
            logger.info(
                "!!! Step is marked as FAILED because no rows OR no valid rows  were read by reader of this step !");
            ExitStatus newExitStatus = ExitStatus.COMPLETED;
            stepExecution.setExitStatus(newExitStatus);
            return newExitStatus;
        }
        return null;
        }

And also wrote a job level listener - JobExecutionListenerSupport , put something like that in overridden afterJob method ,

    @Override
    public void afterJob(JobExecution jobExecution) {
        .......
        .......
        .......
        .......    
        boolean zeroRead = true;
        for (StepExecution stepExecution : stepExecutions) {
        if (stepExecution.getReadCount() != 0) {
            zeroRead = false;
        }
        }
        if (zeroRead) {
        logger.info("***** JOB is FAILED because read count is Zero *****");
        jobExecution.setExitStatus(ExitStatus.COMPLETED);
        return;
        }

Spring Batch - how to COMPLETE a job when the JdbcPagingItemReader returns no data

1 Answers1

Linked