CODEWITHSUNDEEP
springhibernatespring-bootspring-data-jpa
Abdul Gaffar Khan
Abdul Gaffar Khan

Reputation: 244

DB2ZOS dialect not working after upgrading spring boot version from 1.3.5 to 1.4.2

After upgrading spring boot version from 1.3.5 to 1.4.2 seems to be DB2ZOS dialect not working for pagination query in Spring JPA earlier itwas working. I am using Spring JPA with DB2, when i use paging repository and queries for second page it throws error. This is the generated query.

select
        * 
    from
        ( select
            inner2_.*,
            rownumber() over(
        order by

        order of inner2_) as rownumber_ from
            ( select
                componentd0_.comp_data_field_st_msg_id as comp_dat1_0_,
                componentd0_.comp_data_file_id as comp_dat5_0_,
                componentd0_.comp_data_file_field_id as comp_dat6_0_,
                componentd0_.component_serial_no as componen2_0_,
                componentd0_.controller_short_name as controll3_0_,
                componentd0_.message as message4_0_ 
            from
                cs_comp_data_field_st_msg componentd0_ 
            left outer join
                cs_comp_data_file componentd1_ 
                    on componentd0_.comp_data_file_id=componentd1_.comp_data_file_id 
            where
                componentd1_.comp_data_file_id=? 
            order by
                componentd0_.comp_data_field_st_msg_id asc fetch first 50 rows only ) as inner2_ ) as inner1_ 
        where
            rownumber_ > 25 
        order by
            rownumber_
o.h.engine.jdbc.spi.SqlExceptionHelper   :  DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=OF;??( [ DESC ASC NULLS RANGE CONCAT || / MICROSECONDS MICROSECOND, DRIVER=3.69.24
2016-12-26 13:41:42.886 ERROR [componentdatafiles,c50b4d59a68eb961,c50b4d59a68eb961,false] AK51602      20296 --- [io-8081-exec-75] o.h.engine.jdbc.spi.SqlExceptionHelper   :  DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=3.69.24
2016-12-26 13:41:42.887 ERROR [componentdatafiles,c50b4d59a68eb961,c50b4d59a68eb961,false] AK51602      20296 --- [io-8081-exec-75] o.h.engine.jdbc.spi.SqlExceptionHelper   :  DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=3.69.24
2016-12-26 13:41:42.918 DEBUG [componentdatafiles,c50b4d59a68eb961,c50b4d59a68eb961,false] AK51602      20296 --- [io-8081-exec-75] c.d.c.n.service.NonDeereXmlServiceImpl   :  Critical system error

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:491)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMeta

Upvotes: 1

Views: 830

Answers (1)

Abdul Gaffar Khan
Abdul Gaffar Khan

Reputation: 244

org.hibernate.dialect.DB2Dialect class generating invalid query that why its failing.I have override the getLimit function in my dialect and its resolved.

Upvotes: 0

Related Questions