GTT State Persistence Across Db2 LUW Backup Restoration and Hibernate JOIN FETCH Pagination Interaction

Direct Answer

GTT definitions and data are not preserved during a Db2 LUW backup restoration. Applications must explicitly recreate GTTs and repopulate their data after the restore completes. Hibernate queries that combine JOIN FETCH with setFirstResult/setMaxResults trigger in‑memory pagination, which can exhaust heap when processing large GTT result sets. Avoid this combination or use scrollable results, batch fetching, or native SQL with LIMIT/OFFSET.

Confirmed Behavior

GTT Scope and Backup/Restore

• Session‑scoped GTTs (ON COMMIT PRESERVE ROWS) exist for the lifetime of a connection. Transaction‑scoped GTTs (ON COMMIT DELETE ROWS) lose data at each commit.
• During a backup restore, both the GTT definition and its data are dropped. Any subsequent reference from a new connection raises SQL0204N (object not found).
• No Db2 LUW mechanism automatically recreates GTTs after restore; the application (or a deployment script) must re‑execute the DECLARE GLOBAL TEMPORARY TABLE statement and re‑insert required rows.

Hibernate JOIN FETCH Pagination

• When a query uses JOIN FETCH together with setFirstResult/setMaxResults, Hibernate cannot push the limit/offset to the database because the join would change the row count semantics. It therefore fetches the full result set into memory and applies pagination in the JVM.
• This behavior is version‑dependent but present in all widely used Hibernate 5.x and 6.x releases when the dialect does not support a safe transformation.
• With a large GTT (thousands of rows and eager‑fetched collections), the in‑memory collection can cause OutOfMemoryError or severe GC pressure.

Required Steps for This Case

1. Add GTT recreation logic to your post‑restore startup sequence. Execute DECLARE GLOBAL TEMPORARY TABLE SESSION.MY_GTT ... ON COMMIT PRESERVE ROWS (or DELETE ROWS if transaction‑scoped) from a fresh connection after the restore finishes.
2. Repopulate GTT data within the same transaction (for ON COMMIT DELETE ROWS) or session (for ON COMMIT PRESERVE ROWS) before any Hibernate query touches the table.
3. Eliminate in‑memory pagination on JOIN FETCH queries against the GTT. Options:
   • Use ScrollableResults with setFetchSize for streaming.
   • Replace JOIN FETCH with a separate batch fetch (@BatchSize or hibernate.default_batch_fetch_size) and paginate the root entity only.
   • Write a native SQL query that uses Db2 LIMIT n OFFSET m (or FETCH FIRST n ROWS ONLY) and map results via ResultTransformer or DTO projection.
4. Verify by enabling Hibernate SQL logging (org.hibernate.SQL=DEBUG) and confirming a single SELECT ... LIMIT ? OFFSET ? (or FETCH FIRST) is issued, not a full select followed by in‑memory slicing.

Alternative Temporary Storage Patterns

For most Hibernate applications, a regular table with a SESSION_ID or REQUEST_ID column and a scheduled purge job provides the simplest restore resilience.

One Diagnostic Detail Needed

What Hibernate version and Db2 dialect (e.g., org.hibernate.dialect.DB2Dialect vs. DB210Dialect) are you using? Newer dialects may support LIMIT/OFFSET pushdown for certain JOIN FETCH patterns, which would change the pagination mitigation strategy.