Fetch the Data from DB2 and save in MongoDB using

2019-08-21 02:04发布

站内问答 / SpringBoot
858 2 5

I need to fetch all the data from Db2 table and copy to mongoDB. I am using Spring batch without metadata tables. I am using JpaPagingItemReader for fetching data from Db2. I am able to get the data from Db2 for the first fetch which based on the PageSize attribute and save in mongoDB successfully but for the next set of data, getting error in fetching the data with the pagination.

Below given the Error:

15:17:36,085 INFO  [stdout] (default task-2) 2017-12-06 15:17:36.084  INFO 7088 --- [ default task-2] c.t.controller.ItemController   : About to copy All Item
15:17:36,135 INFO  [stdout] (default task-2) 2017-12-06 15:17:36.135  INFO 7088 --- [ default task-2] o.s.b.c.l.support.SimpleJobLauncher      : Job: [SimpleJob: [name=readDB2]] launched with the following parameters: [{}]
15:17:36,180 INFO  [stdout] (default task-2) 2017-12-06 15:17:36.180  INFO 7088 --- [ default task-2] o.s.batch.core.job.SimpleStepHandler     : Executing step: [step1]
15:17:36,215 INFO  [stdout] (default task-2) Hibernate: select itm0_.itm_nbr as t1_4_, itm0_.itm_des_txt as itm_des13_4_ from itm itm0_ fetch first 10 rows only
15:17:36,884 INFO  [stdout] (default task-2) 2017-12-06 15:17:36.884 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate      : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:38,621 INFO  [stdout] (default task-2) 2017-12-06 15:17:38.621  INFO 7088 --- [ default task-2] org.mongodb.driver.connection            : Opened connection [connectionId{localValue:3, serverValue:1998}] to lxmdbpmdmdev001:27017
15:17:38,894 INFO  [stdout] (default task-2) 2017-12-06 15:17:38.894 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate      : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:39,139 INFO  [stdout] (default task-2) 2017-12-06 15:17:39.139 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate      : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:39,386 INFO  [stdout] (default task-2) 2017-12-06 15:17:39.386 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate      : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:39,632 INFO  [stdout] (default task-2) 2017-12-06 15:17:39.632 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate      : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:39,881 INFO  [stdout] (default task-2) 2017-12-06 15:17:39.881 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate      : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:40,123 INFO  [stdout] (default task-2) 2017-12-06 15:17:40.123 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate      : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:40,377 INFO  [stdout] (default task-2) 2017-12-06 15:17:40.377 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate      : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:40,619 INFO  [stdout] (default task-2) 2017-12-06 15:17:40.619 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate      : Saving DBObject containing fields: [_class, itmDesTxt, itemId]
15:17:40,865 INFO  [stdout] (default task-2) 2017-12-06 15:17:40.865 DEBUG 7088 --- [ default task-2] o.s.data.mongodb.core.MongoTemplate      : Saving DBObject containing fields: [_class, itmDesTxt, itemId]

15:17:41,109 INFO  [stdout] (default task-2) Hibernate: select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from ( select itm0_.itm_nbr as t1_4_, itm0_.itm_des_txt as itm_des13_4_ from itm itm0_ fetch first 20 rows only ) as inner2_ ) as inner1_ where rownumber_ > 10 order by rownumber_

15:17:41,346 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: -199, SQLState: 42601
15:17:41,347 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=OF;;??( [ DESC ASC NULLS RANGE CONCAT || / MICROSECONDS MICROSECOND, DRIVER=4.18.60
15:17:41,347 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: -516, SQLState: 26501
15:17:41,347 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=4.18.60
15:17:41,347 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: -514, SQLState: 26501
15:17:41,347 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLN200C1, DRIVER=4.18.60
15:17:41,362 INFO  [stdout] (default task-2) 2017-12-06 15:17:41.362 ERROR 7088 --- [ default task-2] o.s.batch.core.step.AbstractStep         : Encountered an error executing step step1 in job readDB2

15:17:41,362 INFO  [stdout] (default task-2) 
15:17:41,362 INFO  [stdout] (default task-2) javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
15:17:41,362 INFO  [stdout] (default task-2)    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
15:17:41,362 INFO  [stdout] (default task-2)    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
15:17:41,362 INFO  [stdout] (default task-2)    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:492) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
15:17:41,362 INFO  [stdout] (default task-2)    at org.springframework.batch.item.database.JpaPagingItemReader.doReadPage(JpaPagingItemReader.java:225) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,362 INFO  [stdout] (default task-2)    at org.springframework.batch.item.database.AbstractPagingItemReader.doRead(AbstractPagingItemReader.java:108) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,362 INFO  [stdout] (default task-2)    at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.read(AbstractItemCountingItemStreamItemReader.java:88) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,362 INFO  [stdout] (default task-2)    at org.springframework.batch.core.step.item.SimpleChunkProvider.doRead(SimpleChunkProvider.java:91) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,362 INFO  [stdout] (default task-2)    at org.springframework.batch.core.step.item.SimpleChunkProvider.read(SimpleChunkProvider.java:157) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO  [stdout] (default task-2)    at org.springframework.batch.core.step.item.SimpleChunkProvider$1.doInIteration(SimpleChunkProvider.java:116) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO  [stdout] (default task-2)    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO  [stdout] (default task-2)    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO  [stdout] (default task-2)    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO  [stdout] (default task-2)    at org.springframework.batch.core.step.item.SimpleChunkProvider.provide(SimpleChunkProvider.java:110) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO  [stdout] (default task-2)    at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:69) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO  [stdout] (default task-2)    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO  [stdout] (default task-2)    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO  [stdout] (default task-2)    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) ~[spring-tx-4.3.12.RELEASE.jar:4.3.12.RELEASE]
15:17:41,363 INFO  [stdout] (default task-2)    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:272) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,363 INFO  [stdout] (default task-2)    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO  [stdout] (default task-2)    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO  [stdout] (default task-2)    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO  [stdout] (default task-2)    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO  [stdout] (default task-2)    at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO  [stdout] (default task-2)    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200) ~[spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO  [stdout] (default task-2)    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) [spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO  [stdout] (default task-2)    at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:392) [spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO  [stdout] (default task-2)    at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:135) [spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO  [stdout] (default task-2)    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306) [spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO  [stdout] (default task-2)    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135) [spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]
15:17:41,376 INFO  [stdout] (default task-2)    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) [spring-core-4.3.12.RELEASE.jar:4.3.12.RELEASE]
15:17:41,376 INFO  [stdout] (default task-2)    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:128) [spring-batch-core-3.0.8.RELEASE.jar:3.0.8.RELEASE]

15:17:41,376 INFO  [stdout] (default task-2)    at com.test.service.impl.ItemServiceImpl.itemCopy(ItemServiceImpl.java:266) [classes:na]
15:17:41,376 INFO  [stdout] (default task-2)    at com.test.controller.ItemController.allItemCopy(ItemController.java:48) [classes:na]

....

15:17:41,410 INFO  [stdout] (default task-2) 2017-12-06 15:17:41.410  INFO 7088 --- [ default task-2] o.s.b.c.l.support.SimpleJobLauncher      : Job: [SimpleJob: [name=readDB2]] completed with the following parameters: [{}] and the following status: [FAILED]

Batch Configuration:

@Configuration
@EnableBatchProcessing
public class ItemBatch {

    @Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Autowired
    EntityManagerFactory entityManagerFactory;

    @Autowired
    MongoTemplate mongoTemplate;

    @Bean
    public Job readDB2() {
        return jobBuilderFactory.get("readDB2").start(step1()).build();
    }

    @Bean
    public Step step1() {
        return stepBuilderFactory.get("step1")
                .<Itm, com.test.model.mongodb.Itm> chunk(5)
                .reader(reader())
                .writer(writer())
                .build();
    }

    @Bean
    public ItemStreamReader<Itm> reader() {
        JpaPagingItemReader<Itm> databaseReader = new JpaPagingItemReader<>();
        try {
            databaseReader.setEntityManagerFactory(entityManagerFactory);
            JpaQueryProviderImpl<Itm> jpaQueryProvider = new JpaQueryProviderImpl<>();
            jpaQueryProvider.setQuery("Itm.findAll");
            databaseReader.setQueryProvider(jpaQueryProvider);
            databaseReader.setPageSize(10);
            databaseReader.afterPropertiesSet();
        } catch (Exception e) {
            System.err.println("Error :" + e);
        }
        return databaseReader;
    }

    @Bean
    public MongoItemWriter<com.test.model.mongodb.Itm> writer() {
        MongoItemWriter<com.test.model.mongodb.Itm> writer = new MongoItemWriter<>();
        try {
            writer.setTemplate(mongoTemplate);
        } catch (Exception e) {
            e.printStackTrace();
        }
        writer.setCollection("itm");
        return writer;
    }
}

In-Memory Configuration instead of metadata:

@Configuration
public class SpringBatchTestConfiguration {

    @Bean
    public ResourcelessTransactionManager transactionManager() {
        return new ResourcelessTransactionManager();
    }

    @Bean
    public MapJobRepositoryFactoryBean mapJobRepositoryFactory(ResourcelessTransactionManager txManager)
            throws Exception {
        MapJobRepositoryFactoryBean factory = new MapJobRepositoryFactoryBean(txManager);
        factory.afterPropertiesSet();
        return factory;
    }

    @Bean
    public JobRepository jobRepository(MapJobRepositoryFactoryBean factory) throws Exception {
        return factory.getObject();
    }

    @Bean
    public JobExplorer jobExplorer(MapJobRepositoryFactoryBean factory) {
        return new SimpleJobExplorer(factory.getJobInstanceDao(), factory.getJobExecutionDao(),
                factory.getStepExecutionDao(), factory.getExecutionContextDao());
    }

    @Bean
    public SimpleJobLauncher jobLauncher(JobRepository jobRepository) {
        SimpleJobLauncher launcher = new SimpleJobLauncher();
        launcher.setJobRepository(jobRepository);
        return launcher;
    }
}

QueryProvider:

public class JpaQueryProviderImpl<E> extends AbstractJpaQueryProvider {

    private Class<E> entityClass;
    private String query;

    @Override
    public Query createQuery() {
        return getEntityManager().createNamedQuery(query, entityClass);
    }

    public void setQuery(String query) {
        this.query = query;
    }

    public void setEntityClass(Class<E> entityClazz) {
        this.entityClass = entityClazz;
    }

    @Override
    public void afterPropertiesSet() throws Exception {
        Assert.isTrue(StringUtils.hasText(query), "Query cannot be empty");
        Assert.notNull(entityClass, "Entity class cannot be NULL");
    }
}

Entity:

@Entity
@Table(name="ITM")
@NamedQueries({ @NamedQuery(name = "Itm.findAll", query = "select i from Itm i") })
public class Itm implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="ITEM_NBR")
    private int itemId;

    @Column(name="ITM_DES_TXT")
    private String itmDesTxt;

    //getter and setter

}

2条回答
何必那么认真
2楼-- · 2019-08-21 02:44

The root cause is this error:

DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=OF;;??( [ DESC ASC NULLS RANGE CONCAT || / MICROSECONDS MICROSECOND, DRIVER=4.18.60

which points you to the OF keyword being unexpected.

ORDER BY ORDER OF in the OLAP specification is not valid in Db2 for z/OS v11, as can be seen in the manual. You'll need to rewrite your statement to order by some explicit expression -- or skip ORDER BY altogether since you're not ordering the inner2_ subselect anyway.

查看更多
0人赞 添加讨论(0) 举报
爱情/是我丢掉的垃圾
3楼-- · 2019-08-21 02:56

It is not issue with Spring batch, it is the issue in Spring Pagination with DB2 Z/OS. To fix the issue, we have to with the new dialect. Still I am facing issue with the new dialect. Need to find the proper dialect configuration.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)