Hi ,

Am new to Hibernate and currently working on a service which connects to DB2 Z/os version 11.
The service is using JPA repositories and ORM model to pull data from DB2. The ORM model has associations and relationships defined in them.
Upon trying to send a pageable object in the JPA Repository and trying to query for say - pagenumber =2 and pagesize = 1 , I am getting the following error :-

ILLEGAL USE OF KEYWORD OF. TOKEN ??( [ DESC ASC NULLS RANGE CONCAT || / MICROSECONDS MICROSECOND WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=4.19.26

Upon checking the logs could find that hibernate is creating the below query for pagination to select the second page for employee details from EMPLOYEE table with paging size parameter as 1:-

select * from
( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_
from
( select id as id_,name as name_ from employee as employee_ where employee_.name="employee1" order by employee_.id fetch first 2 rows only )
as inner2_)
as inner1_ where rownumber_ > 1 order by rownumber_;

If I take the query created by hibernate and execute in DB2 , I receive the same error.

This is because of using ORDER BY ORDER OF <tablename> clause inside an OLAP function like ROW_NUMBER() OVER .

According to IBM, this is not allowed :- https://www.ibm.com/support/knowledgece ... lause.html

"For an ORDER BY clause in an OLAP specification, table-designator must not specify a table function, a materialized view, a nested table expression that is materialized, an alias, or a synonym"



My question is why is Hibernate trying to create a query which is not allowed by IBM . Is there any way I can force Hibernate to create the right query without NamedQuery ?

Regards,
Abhishek

It might be a bug. Write a replicated test case and open a Jira issue. If you provide a Pull Request, the fix will be integrated with higher priority.

Thank you for the prompt response.

As mentioned in the topic, the SQL query created while doing pagination does not work with DB2 database.
While using H2 database, a different SQL is created with limit and offset parameters which work absolutely fine with H2.

If so, how do you propose that I write the replicable test cases that uses DB2 and not H2 because the issue is only with DB2Dialect working with DB2 database and not with H2 database or Oracle.

Regards,
Abhishek

That's right. Please right the replicating test case and the fix for DB2 and send it as a Pull Request.

Hi Vlad,

As of now we have fixed the issue as follows :-

The issue is with the DB2Dialect class in hibernate-orm/hibernate-core/src/main/java/org/hibernate/dialect/DB2Dialect.java while creating the processSql in AbstractLimitHandler().

Link :- https://github.com/hibernate/hibernate- ... alect.java

rownumber() over(order by order of inner2_) clause is not allowed by DB2 . DB2 has restrictions in using order by order of clause inside an OLAP function.

The change should be :-

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

or for cases when the subquery isn't ordered and the service does not care about the order returned from the database:
select * from ( select inner2_.*, rownumber() over() as rownumber_ from ( ...

Regards,
Abhishek

@abhishek.. I have the same exact issue as you do. Were you able to come up with a solution for this issue? If yes, can you please share?

@rohithkodakandla

Send us a Pull Request with a replicating test case and a fix, and we will integrate it.

Unfortunately, we don't have a DB2 Z/os version 11 in Jenkins to test it.