After upgrading to Hibernate ORM 6.6.35.Final, we observed a noticeable change in the generated SQL for queries involving composite keys (@EmbeddedId) and fields mapped via @Type / UserType.
This change appears to be related to HHH-19895 and is understood as an intentional correctness improvement.
However, on DB2 z/OS, the resulting SQL shape can lead to significant performance degradation due to loss of index usage.
Environment
Hibernate ORM: 6.6.35.Final
Database: IBM DB2 z/OS
Mapping:
@EmbeddedId (composite primary key)
One component mapped via @Type using a custom UserType
Index:
Composite index on the EmbeddedId columns
Before 6.6.35.Final (index-friendly)
...
WHERE
h1_0.txt_id = ?
AND h1_0.nr_doc = ?
DB2 uses the composite index
Index scan, good performance
Since 6.6.35.Final
...
WHERE
(
h1_0.txt_id,
h1_0.nr_doc
)
IN
(
SELECT
CAST(? AS VARCHAR(32672)),
CAST(? AS DECIMAL(19,2))
FROM
SYSIBM.SYSDUMMY1
)
Tuple predicate rendered as IN (SELECT …)
Explicit CAST applied to parameters
Additional access to SYSIBM.SYSDUMMY1
Observed Impact on DB2 z/OS
Composite index is no longer used
DB2 optimizer chooses a table scan
Queries that were previously index-based show significantly increased execution time
This behavior is reproducible and disappears when downgrading to Hibernate 6.6.29.Final.
Purpose of This Issue
The goal of this issue is to:
Share a performance-related observation for DB2 z/OS users
Document a potential side effect of the changes introduced with HHH-19895
Provide context for others encountering similar behavior with composite keys and custom types
Notes / Clarifications
We assume that the behavior is correct from a type-safety perspective. The performance impact seems to be specific (or at least more pronounced) on DB2 z/OS. Furthermore, it should be noted that this behavior only became visible with a very large database (on the order of ~millions entries). With a similar transaction with a significantly smaller database (on the order of ~thousands entries), the behavior was not observed.
Can you please capture the explain plans for both SQL queries and post them here?
Also, assuming that the VARCHAR size is not the one of the column, can you please also attach the explain plan for something like
...
WHERE
(
h1_0.txt_id,
h1_0.nr_doc
)
IN
(
SELECT
CAST(? AS VARCHAR(255)),
CAST(? AS DECIMAL(19,2))
FROM
SYSIBM.SYSDUMMY1
)
or whatever the size is that the column actually has?
I’m trying to understand what the optimizer doesn’t use the index in this case. Maybe you have a DBA or can ask your IBM support about the details. This information would be very valuable for us to ensure we’re doing the right thing.
Thanks for the feedback — we’ve captured the DB2 z/OS visual explain plans for both SQL variants.
Result summary:
Old SQL (col1 = ? AND col2 = ?):
DB2 uses an IXSCAN on the composite index WPHGD_BAHISTORIE_I0101, followed by FETCH.
No access to SYSIBM.SYSDUMMY1, no SORT, no work file:
New SQL (tuple IN (SELECT CAST(...) FROM SYSIBM.SYSDUMMY1)):
DB2 switches to a TBSCAN on WPHGD_BAHISTORIE_T01.
The plan additionally shows access to SYSIBM.SYSDUMMY1, a SORT and a work file:
(Sorry for the way it’s displayed; as a new user, I’m only allowed to upload one image.)
This indicates that DB2 z/OS no longer treats the predicate as index-matching when rendered as a tuple IN subselect with CASTs, even though the logical comparison is equivalent.
From the DB2 perspective, the combination of:
tuple predicate
parameter CASTs
subselect over SYSIBM.SYSDUMMY1
appears to make the predicate non-sargable, preventing composite index usage.
According to the DB2 for z/OS documentation, this kind of predicate should be indexable though: IBM Documentation also see the case (COL1,...COLn) IN (noncor subq)
Can you please try lowering the size of the varchar like I asked?
The affected column is currently mapped using columnDefinition = “CHAR(36)”, but without an explicit length attribute. As we understand it, Hibernate does not derive length information from columnDefinition, which explains why the generic fallback VARCHAR(32672) is currently used for the parameter CAST.
We will update the mapping to explicitly declare the column length so that Hibernate can use the correct size when rendering the CAST and avoid the fallback. We’ll then rerun the explains with the adjusted mapping and follow up with the results.
We’ve now explicitly declare a fixed length. Concretely, the column is mapped with a fixed length of 36 (CHAR(36)) and an explicit JDBC type. Despite this, Hibernate still renders the tuple predicate using
CAST(? AS VARCHAR(32672))
instead of a length-matching CAST.
This suggests that the VARCHAR(32672) is not caused by missing length metadata in the column mapping, but rather by the SQL rendering of tuple IN predicates in this context (EmbeddedId / custom type), where Hibernate appears to fall back to a generic VARCHAR size regardless of the declared column length.
From the DB2 z/OS optimizer perspective, this SQL shape consistently prevents composite index usage, even when the actual column length and JDBC type are explicitly defined.
We wanted to share this update, as it seems to rule out the oversized VARCHAR as a mapping configuration issue on our side.
Thanks for getting back to me. I am in contact with DB2 z/OS engineers now to understand what is going on, because the documentation suggests that this query pattern should be fine. I’ll get back to you as soon as possible on this.
FYI, I created HHH-20041 to track this. The DB2 for z engineer confirmed that this kind of predicate does not support index usage, so I will work on restoring the old way of SQL rendering.
After upgrading to Hibernate 6.6.41, the performance issue is no longer observable. The generated SQL no longer leads to a table scan on DB2 z/OS, and the expected index access path is used again.
