JpaSystemException using Criteria, DB2zDialect and Pagination

Question

so I have the following setup of a spring-boot application:

org.springframework.boot:spring-boot-starter:3.2.4
com.ibm.db2:jcc:11.5.8.0
java.version=21
org.hibernate.orm:hibernate-core:6.4.4.Final

connecting to a DB2 z/OS database (DSN12015) v12.015.

I have the following dialect:

import org.hibernate.dialect.DB2zDialect;
import org.hibernate.dialect.pagination.LegacyDB2LimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;

public class Db2zOsDialect extends DB2zDialect {
  @Override
  public SequenceInformationExtractor getSequenceInformationExtractor() {
    return LegacyDb2zOsSequenceInformationExtractor.INSTANCE;
  }

  @Override
  public LimitHandler getLimitHandler() {
    return LegacyDB2LimitHandler.INSTANCE;
  }
}

I have the following entity:

@Table(name = "\"KEY\"")
public class Key {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "ID", unique = true, nullable = false)
  private Long id;

  @Column(name = "\"VALUE\"", nullable = false, length = 100)
  private String value;

  ...
}

And the following repository Repository:

@Repository
interface KeyRepository extends JpaRepository<Key, Long>, JpaSpecificationExecutor<Key> {}

I now want to read it using specification:

final String toSearchFor = "%foo%"
Specification<User> spec = (root, query, criteriaBuilder) -> criteriaBuilder.like(root.get("value"), toSearchFor);
final PageRequest pageRequest = PageRequest.of(0, 100, Sort.by(Direction.DESC, "id"));
repo.findAll(specification, pageRequest)

Using exactly the same code without PageRequest works perfectly. But as soon as the pagination is there it fails. I discovered since we updated from spring-boot 2.X the query changed internally executed by JPA and hibernate:

Original-Query:

select key0_.ID                        as id1_0_,
       key0_."VALUE"                   as value_2_0_,
       ...
from MY_SCHEMA."KEY" key0_
where key0_."VALUE" like '%foo%'
order by key0_.ID desc
fetch first 100 rows only;

New-Query:

select k1_0.ID,
       k1_0."VALUE",
       ...
from MY_SCHEMA."KEY" k1_0
where k1_0."VALUE" like '%foo%'
order by k1_0.ID desc
offset 0 rows fetch first 100 rows only;

Both queries run if I execute them via SQL tool against the database

What I found out so far that it is not going to use the Db2zOsDialect and instead does something else which is including offset. This happens in the constructor of DeferredResultSetAccess:

Exception:

org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [select k1_0.ID, k1_0."VALUE", ... from MY_SCHEMA."KEY" k1_0 where k1_0."VALUE" like '%foo%' order by k1_0.ID desc offset 0 rows fetch first 100 rows only] [DB2 SQL Error: SQLCODE=-4743, SQLSTATE=56038, SQLERRMC=null, DRIVER=4.32.28] [n/a]

Error Logs:

2024-04-04T09:36:41.769+02:00 ERROR 22710 --- [nio-9080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : DB2 SQL Error: SQLCODE=-4743, SQLSTATE=56038, SQLERRMC=null, DRIVER=4.32.28
2024-04-04T09:36:41.769+02:00 ERROR 22710 --- [nio-9080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=4.32.28
2024-04-04T09:36:41.769+02:00 ERROR 22710 --- [nio-9080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=4.32.28

Questions:

• Why is "offset" used and not my LimitHandler
• I know there were updates to "aliases" on hibernate 6, but why would it have an effect
• Is this an internal JPA/Hibernate/JDBC problem?

EDIT:

Dialect is picked up, none the less Hibernate does some shady stuff:

Spring-Boot config application.yml:

spring:
  jpa:
    hibernate:
      ddl-auto: none
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
    properties:
      hibernate:
        dialect: com.my.package.Db2zOsDialect
        default_schema: MY_SCHEMA
        criteria:
          literal_handling_mode: inline
        hbm2ddl:
          halt_on_error: true
    open-in-view: false
  datasource:
    url: jdbc:db2://someserver:1234/DBNAME:sslConnection=true;currentSchema=MY_SCHEMA;useUnicode=yes;characterEncoding=UTF-8;
    username: someuser
    password: sumepassword
    driver-class-name: com.ibm.db2.jcc.DB2Driver

EDIT 2:

I removed the Specification and Sort from the code and still got the error:

repo.findAll(PageRequest.of(0, 100))

also the query got much simpler:

select k1_0.ID, k1_0."VALUE" from MY_SCHEMA."KEY" k1_0 offset 0 rows fetch first 100 rows only

I now even tried to upgrade to the latest JDBC driver by using com.ibm.db2:jcc:11.5.9.0 as a dependency. But this now produces a new issue:

com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException: [jcc][t4][2010][11246][4.33.31] Connection authorization failure occurred.  Reason: Local security service non-retryable error. ERRORCODE=-4214, SQLSTATE=28000

And I also downgraded to older versions of the JDBC driver, but the problem keeps being the same.

Answer 1

Looking at the hibernate code I endup at the DB2SqlAstTranslator which is also returned from the Dialect using the getSqlAstTranslatorFactory method. The DB2SqlAstTranslator is what eventually creates the query. This has been probably been due to the refactoring of the whole AST processing in Hibernate 6+.

The DB2SqlAstTranslator has a supportsOffsetClause method in which checks only the version and will return true. When true is returned it will go into the path that generates the query with the offset 0 ... if it returns false it will return the older query.

You could override that method to return false and then in your Db2zOsDialect return your custom AstTranslatorFactory.

But it is weird that the execution of the query succeeds in the normal tooling but fails through JPA. The error codes indicate some issue with preparing the statement (as I suspect this is some dummy SQL and not the original this is hard to determine). You might want to register an issue with Hibernate as well.