Issue with DB2iDialect and Sequence Support in Hibernate 6

Hibernate ORM
1

Hello,

I have a question regarding the DB2iDialect in Hibernate. It appears to support sequences and uses the following SQL to fetch sequence data:

SELECT DISTINCT sequence_name  
FROM qsys2.syssequences  
WHERE current_schema = '*LIBL'    
AND sequence_schema IN (SELECT schema_name FROM qsys2.library_list_info)     
OR sequence_schema = current_schema

However, the dialect uses SequenceInformationExtractorDB2DatabaseImpl, which extends SequenceInformationExtractorLegacyImpl. In the extractMetadata method, it triggers the above query and attempts to read several columns (e.g., sequence_name, start, increment, etc.). Since the query only returns sequence_name, this results in a runtime exception.

Am I missing something, or is it the case that DB2iDialect doesn’t support sequences?

Context:

  • I’m using a DB2 (IBM i V7R4M0) database.

  • I have a JPA entity that I cannot modify.

  • The entity uses @GeneratedValue(strategy = GenerationType.AUTO).

  • With Hibernate 5.6, the dialect defaults to SEQUENCE, but I was able to override this by setting:

    spring.jpa.hibernate.use-new-id-generator-mappings=false

    This forced Hibernate to use the IDENTITY strategy instead, which worked fine.

Now I’m upgrading to Spring Boot 3.6.4 and Hibernate 6. This property has been removed, and I haven’t found an alternative. Since I cannot change the generation type on the entity, I’m trying to switch to using sequences — but the current dialect setup fails due to the issue above.

Questions:

  • Is DB2iDialect supposed to support sequences properly in Hibernate 6?

  • Is there a workaround or recommended approach for this situation, given the limitations?

Thanks in advance!

2

As far as I understand, DB2 for i is supposed to support sequences as of version 7.3, but we heard of other people having trouble before, see HHH-19453. Unfortunately, we don’t have a way to test IBM DB2 for i yet and so we don’t really know if we are missing something.

Clearly, this query misses to produce select items with the following aliases

  • seqname
  • seqschema
  • start
  • minvalue
  • maxvalue

I created HHH-19453 Fix sequence information extraction on DB2 for i by beikov · Pull Request #10713 · hibernate/hibernate-orm · GitHub to fix this and will backport the fix. I’d be very grateful if you could help us with testing of the dialect in the meantime. Can you run the Hibernate ORM testsuite on an isolated database for testing?

Just checkout the branch for my PR, add the following entry to local-build-plugins/src/main/groovy/local.databases.gradle and run ./gradlew check -Pdb=db2i:

            db2i : [
                    'db.dialect' : 'org.hibernate.dialect.DB2iDialect',
                    'jdbc.driver': 'com.ibm.db2.jcc.DB2Driver',
                    'jdbc.user'  : 'orm_test',
                    'jdbc.pass'  : 'orm_test',
                    'jdbc.url'   : 'jdbc:db2://' + dbHost + ':50000/orm_test',
                    'jdbc.datasource' : 'com.ibm.db2.jcc.DB2Driver',
//                        'jdbc.datasource' : 'com.ibm.db2.jcc.DB2SimpleDataSource',
                    'connection.init_sql' : ''
            ],
3

Seems like the branch is already merged and deleted but i will test it on the main branch.