# Performance note on DB2 z/OS: CAST-based tuple predicates for @EmbeddedId + UserType after HHH-19895

**URL:** <https://discourse.hibernate.org/t/performance-note-on-db2-z-os-cast-based-tuple-predicates-for-embeddedid-usertype-after-hhh-19895/12044>\
**Category:** Hibernate ORM\
**Created:** [December 15, 2025, 2:22pm UTC](https://discourse.hibernate.org/t/performance-note-on-db2-z-os-cast-based-tuple-predicates-for-embeddedid-usertype-after-hhh-19895/12044 "2025-12-15T14:22:15Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Djamel](https://avatars.discourse-cdn.com/v4/letter/d/76d3ee/32.png) [@Djamel](https://discourse.hibernate.org/u/Djamel)\
**Post date:** [December 15, 2025, 2:22pm UTC](https://discourse.hibernate.org/t/performance-note-on-db2-z-os-cast-based-tuple-predicates-for-embeddedid-usertype-after-hhh-19895/12044/1 "2025-12-15T14:22:15Z")

</div>

## Description

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)

```sql
...
WHERE
    h1_0.txt_id = ?
AND h1_0.nr_doc = ?

```

- DB2 uses the composite index
- Index scan, good performance

### Since 6.6.35.Final

```sql
...
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.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [December 15, 2025, 2:35pm UTC](https://discourse.hibernate.org/t/performance-note-on-db2-z-os-cast-based-tuple-predicates-for-embeddedid-usertype-after-hhh-19895/12044/2 "2025-12-15T14:35:18Z")

</div>

Can you please capture the [explain plans](https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=statements-explain) 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

```sql
...
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.

---

<div class="post-metadata">

**Author:** ![Djamel](https://avatars.discourse-cdn.com/v4/letter/d/76d3ee/32.png) [@Djamel](https://discourse.hibernate.org/u/Djamel)\
**Post date:** [December 15, 2025, 3:39pm UTC](https://discourse.hibernate.org/t/performance-note-on-db2-z-os-cast-based-tuple-predicates-for-embeddedid-usertype-after-hhh-19895/12044/3 "2025-12-15T15:39:15Z")

</div>

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:  
 ![image-2025-12-09-18-30-02-166](https://canada1.discourse-cdn.com/flex035/uploads/hibernate/original/2X/5/530d0063b26e9210d8a322b6f9ad7b117ddf1293.png)
- 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:

```txt
QUERY
 └─ QB1
    └─ NLJOIN
    │ ├─ NLJOIN
    │ │ ├─ NLJOIN
    │ │ │ ├─ TBSCAN
    │ │ │ │ └─ WPHGD_BAHISTORIE_T01
    │ │ │ └─ FETCH
    │ │ │ └─ IXSCAN
    │ │ │ │ └─ WPHGD_ENDKUNDE_I0101
    │ │ │ └─ WPHGD_ENDKUNDE_T01│
    │ │ │           
    │ │ └─ FETCH
    │ │ └─ IXSCAN
    │ │ │ └─ WPHGD_BAKENNTNISSE_I0101
    │ │ └─ WPHGD_BAKENNTNISSE_T01
    │ └─ FETCH
    │ └─ IXSCAN
    │ │ └─ WPHGD_BAWETT_KENNTNISSE_I0101
    │ └─ WPHGD_BAWETT_KENNTNISSE_T01
	│
	└─ QB4
		└─ WKFILE
			└─ SORT
				└─ TBSCAN
					└─ SYSIBM.SYSDUMMY1

```

(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.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [December 15, 2025, 3:49pm UTC](https://discourse.hibernate.org/t/performance-note-on-db2-z-os-cast-based-tuple-predicates-for-embeddedid-usertype-after-hhh-19895/12044/4 "2025-12-15T15:49:22Z")

</div>

According to the DB2 for z/OS documentation, this kind of predicate should be indexable though: [IBM Documentation](https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=efficiently-summary-predicate-processing) also see the case `(COL1,...COLn) IN (noncor subq)`

Can you please try lowering the size of the varchar like I asked?

---

<div class="post-metadata">

**Author:** ![Djamel](https://avatars.discourse-cdn.com/v4/letter/d/76d3ee/32.png) [@Djamel](https://discourse.hibernate.org/u/Djamel)\
**Post date:** [December 16, 2025, 7:13am UTC](https://discourse.hibernate.org/t/performance-note-on-db2-z-os-cast-based-tuple-predicates-for-embeddedid-usertype-after-hhh-19895/12044/5 "2025-12-16T07:13:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Djamel](https://avatars.discourse-cdn.com/v4/letter/d/76d3ee/32.png) [@Djamel](https://discourse.hibernate.org/u/Djamel)\
**Post date:** [December 16, 2025, 4:19pm UTC](https://discourse.hibernate.org/t/performance-note-on-db2-z-os-cast-based-tuple-predicates-for-embeddedid-usertype-after-hhh-19895/12044/6 "2025-12-16T16:19:14Z")

</div>

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

```sql
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.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [December 16, 2025, 4:36pm UTC](https://discourse.hibernate.org/t/performance-note-on-db2-z-os-cast-based-tuple-predicates-for-embeddedid-usertype-after-hhh-19895/12044/7 "2025-12-16T16:36:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [January 7, 2026, 4:14pm UTC](https://discourse.hibernate.org/t/performance-note-on-db2-z-os-cast-based-tuple-predicates-for-embeddedid-usertype-after-hhh-19895/12044/8 "2026-01-07T16:14:38Z")

</div>

FYI, I created [HHH-20041](https://hibernate.atlassian.net/browse/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.

---

<div class="post-metadata">

**Author:** ![Djamel](https://avatars.discourse-cdn.com/v4/letter/d/76d3ee/32.png) [@Djamel](https://discourse.hibernate.org/u/Djamel)\
**Post date:** [February 2, 2026, 3:38pm UTC](https://discourse.hibernate.org/t/performance-note-on-db2-z-os-cast-based-tuple-predicates-for-embeddedid-usertype-after-hhh-19895/12044/9 "2026-02-02T15:38:59Z")

</div>

We can confirm that the fix works for us.

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.

Thanks for the quick turnaround on this!