Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: Hibernate issue with DB2 query during pagination
PostPosted: Tue Jan 17, 2017 12:14 pm 
Newbie

Joined: Tue Jan 17, 2017 11:58 am
Posts: 3
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


Top
 Profile  
 
 Post subject: Re: Hibernate issue with DB2 query during pagination
PostPosted: Tue Jan 17, 2017 4:02 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1518
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.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate issue with DB2 query during pagination
PostPosted: Wed Jan 18, 2017 1:00 pm 
Newbie

Joined: Tue Jan 17, 2017 11:58 am
Posts: 3
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


Top
 Profile  
 
 Post subject: Re: Hibernate issue with DB2 query during pagination
PostPosted: Wed Jan 18, 2017 1:27 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1518
That's right. Please right the replicating test case and the fix for DB2 and send it as a Pull Request.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate issue with DB2 query during pagination
PostPosted: Thu Jan 19, 2017 11:39 am 
Newbie

Joined: Tue Jan 17, 2017 11:58 am
Posts: 3
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


Top
 Profile  
 
 Post subject: Re: Hibernate issue with DB2 query during pagination
PostPosted: Sat Oct 28, 2017 2:29 am 
Newbie

Joined: Sat Oct 28, 2017 2:26 am
Posts: 1
@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?


Top
 Profile  
 
 Post subject: Re: Hibernate issue with DB2 query during pagination
PostPosted: Sat Oct 28, 2017 5:14 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1518
@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.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.