Issue with Spring data jpa Db2 pagination

Question

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 db2DATAa0_.c_type AS col_0_0_, 
                       db2DATAa0_.h_proc AS col_1_0_, 
                       db2DATAa0_.n_vin  AS col_2_0_, 
                       db2DATAa0_.i_cust AS col_3_0_ 
                FROM   dcu.v_rpt_data_hist db2DATAa0_ 
                WHERE  db2DATAa0_.reportid = '0H000488089' 
                       AND ( db2DATAa0_.c_type = 'S' 
                              OR db2DATAa0_.c_type = 'N' 
                              OR db2DATAa0_.c_type = 'A' 
                              OR db2DATAa0_.c_type = 'T' ) 
                ORDER  BY db2DATAa0_.h_proc desc 
                FETCH first 30 ROWS only) AS inner2_) AS inner1_ 
WHERE  rownumber_ > 15 
ORDER  BY rownumber_ 

Error: 2719372 [2016-10-21 16:29:02,040] [RxCachedThreadScheduler-13] WARN org.hibern ate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: -199, SQLState: 42601 2719379 [2016-10-21 16:29:02,047] [RxCachedThreadScheduler-13] ERROR org.hibern ate.engine.jdbc.spi.SqlExceptionHelper - DB2 SQL Error: SQLCODE=-199, SQLSTATE= 42601, SQLERRMC=OF;??( [ DESC ASC NULLS RANGE CONCAT || / MICROSECONDS MICROSECO ND, DRIVER=3.57.82

Any idea?

Answer 1

This issue can be resolve by change dialect. Change dialect in configuration or property file to DB2ZOSDialect

Answer 2

Your error states the ILLEGAL USE OF KEYWORD OF. TOKEN [DESC ASC NULLS RANGE CONCAT] WAS EXPECTED.

I identified this as the critical part of the query:

ORDER BY ORDER OF inner2_

DB2 expects one of DESC, ASC, NULLS, RANGE, CONCAT after the second ORDER keyword.