DB2 for System I, Hibernate, pagination

Question

Hibernate uses for pagination still subquery, but DB2 query language supports LIMIT and OFFSET

the Dialect used is: org.hibernate.dialect.DB2iDialect

In our Unittests with H2 hibernate uses LIMIT and OFFSET. This behaviour resolves in Performance Issues.

I also tried to write a custom Dialect where i copied "getLimitHandler" from H2Dialect. It still didn't resolve the Issue

As side Information Springboot, Spring Data is used (Version:3.1.1)

Reference article for paging on DB2 https://www.itjungle.com/2016/01/12/fhg011216-story03/

Generated for DB2 Dialect:

select * from (
select ... ,row_number() over(order by ...) rn 
from ... p1_0 
left join ... d1_0 on ...
left join ... d2_0 on ... 
join ... c1_0 on ... where ...
) r_0_ where r_0_.rn<=?+? and r_0_.rn>? order by r_0_.rn

Generated for H2 Dialect

select ... from ... p1_0 
left join ... d1_0 on ...
left join ... d2_0 on ... 
where ... offset ? rows fetch first ? rows only 

Code used for Pagination

        val query = entityManager.createQuery(
            ...
        )

        query.firstResult = pageable.offset.toInt()
        query.maxResults = pageable.pageSize
        return query.resultList

Solution: Bug in: DB2iLegacySqlAstTranslator see: https://github.com/beikov/hibernate-orm/blob/main/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DB2iLegacySqlAstTranslator.java

@Override
protected boolean shouldEmulateFetchClause(QueryPart queryPart) {
    // Check if current query part is already row numbering to avoid infinite recursion
    if ( getQueryPartForRowNumbering() == queryPart ) {
        return false;
    }
    // Percent fetches or ties fetches aren't supported in DB2
    if ( useOffsetFetchClause( queryPart ) && !isRowsOnlyFetchClauseType( queryPart ) ) {
        return true;
    }
    // According to LegacyDB2LimitHandler, variable limit also isn't supported before 7.10
    return  version.isBefore(7, 10)
            && queryPart.getFetchClauseExpression() != null
            && !( queryPart.getFetchClauseExpression() instanceof Literal );
}

@Override
protected boolean supportsOffsetClause() {
    return version.isSameOrAfter(7, 10);
}

Version schould be 7,1 and not 7, 10

Workaround:

class DB2iDialectCustom : DB2iDialect(DatabaseVersion.make(7, 40)) {
    override fun getLimitHandler(): LimitHandler {
        //also supports LIMIT n OFFSET m
        return OffsetFetchLimitHandler.INSTANCE
    }
}