Hibernate 7.1 upgrade, Db2, setMaxResults stopped working

Hibernate ORM
1

Going from versions
hibernate 6.6.36

to versions:
hibernate 7.1.14

The following worked on the prior versions:

    typedQuery.setMaxResults(1).getResultList()

and correctly generated the following:

fetch first 1 rows only

Under the upgraded version, it is now producing the following:

fetch first ? rows only

I noticed if I downgrade to a really old version of Db2Dialect (like 10.5) that this works, but it looks like with the version 11.1 and beyond, it generates the breaking SQL above.

Please let me know if you need any further details.

2

How is that breaking? According to the documentation, DB2 11.1+ supports variables in the fetch first clause and we even test that.
Note that Hibernate ORM 7.1 requires at least DB2 11.1 since older versions are out of support already.

3

Here are some other details…

Some of the app startup logs:
org.hibernate.orm.jdbc - HHH100017: Database:
name: DB2
version: DSN13015
major: 13
minor: 1

org.hibernate.orm.jdbc - HHH100018: Driver:
name: IBM Data Server Driver for JDBC and SQLJ
version: 4.33.31
major: 4
minor: 33
JDBC version: 4.2

org.hibernate.orm.dialect - HHH035001: Using dialect: org.hibernate.dialect.DB2zDialect, version: 12.1

Some settings from the DB itself:
DB2 Rel : 1315
DB2 F.Lvl : V13R1M504
Max ApplC : V12R1M510
ApplCompat: V12R1M510
Cat Level : V13R1M504

The error I am received when using setMaxResults:
org.hibernate.orm.jdbc.error - DB2 SQL Error: SQLCODE=-4743, SQLSTATE=56038, SQLERRMC=null, DRIVER=4.33.31

4

I’m not sure what is happening exactly. The error code indicates that the compatibility level you configured doesn’t allow using this feature.
But at the same time, the documentation already suggests that variables are supported in the fetch first clause in version 12: IBM Documentation

Maybe this is a bug in DB2? I’d suggest you ask the IBM DB2 support about this problem.

5

After working with IBM some, we had to set a special register to get this working with DB2 z/os:

;specialRegisters=CURRENT PACKAGESET=NULLID_V12R1M500;

We have been going through the code base retesting and everything (dynamic queries with blaze and named queries) appears to work except for named native queries. With named native queries, it’s generating the SQL with the bind parameter, but I do not see it actually binding in a value for it like it does with blaze/named queries (ie, the log below):

TRACE org.hibernate.orm.jdbc.bind - binding parameter (3:INTEGER) <-- [2]

Ie, it just adds the fetch first ? rows without any binding log for that parameter.

Is this expected?

Thanks again for all the help.

6

Thanks for sharing, but just out of curiosity, did IBM also say why this is needed?

Yeah, for native queries the bind value is not logged unfortunately. See bindOffset and bindLimit in org.hibernate.dialect.pagination.AbstractLimitHandler. Please create an improvement request in our issue tracker for this. Maybe you even want to give implementing this a try? It’s pretty easy, you just need to add

			if ( JdbcBindingLogging.LOGGER.isTraceEnabled() ) {
				JdbcBindingLogging.logBinding(
						index + bound,
						SqlTypes.INTEGER,
						getFirstRow( limit )
				);
			}

and

			if ( JdbcBindingLogging.LOGGER.isTraceEnabled() ) {
				JdbcBindingLogging.logBinding(
						index + count,
						SqlTypes.INTEGER,
						getMaxOrLimit( limit )
				);
			}

respectively.