IBM Db2 LUW Global Temporary Table Scope and Hibernate Pagination Behavior

The session versus transaction scope of a Db2 LUW global temporary table (GTT) does not change the SQL text that Hibernate generates for setFirstResult/setMaxResults pagination; the dialect always emits FETCH FIRST n ROWS ONLY regardless of scope. However, the execution plan that Db2 selects can differ because session-scoped GTTs persist across the entire session, allowing the optimizer to cache a plan that reuses the temporary table definition, while transaction-scoped GTTs are dropped after each transaction, forcing a fresh plan compilation on every paginated query. In Db2 10.5 the optimizer may treat session-scoped GTTs as volatile, producing a more conservative plan, whereas 11.1 and later introduce plan-caching for GTTs, which can yield more aggressive plans when statistics are available. Pagination performance is therefore sensitive to the cardinality estimate of the GTT; if the table's size varies between paginated requests, a cached plan that was optimal for the first page may become suboptimal for later pages.

Confirmed facts:

• Hibernate's Db2 dialect consistently generates FETCH FIRST n ROWS ONLY regardless of GTT scope.
• Execution plans can vary because GTT scope influences optimizer visibility and statistics retention.
• Db2 10.5, 11.1, and 11.5+ have differing default behaviors for temp-table plan caching.

Steps to verify in your environment:

1. CREATE GLOBAL TEMPORARY TABLE SESSION_TBL (c1 INT, c2 VARCHAR(32)) ON COMMIT PRESERVE ROWS;
2. CREATE GLOBAL TEMPORARY TABLE TRANSACTION_TBL (c1 INT, c2 VARCHAR(32)) ON COMMIT DELETE ROWS;
3. Run a Hibernate paginated query (e.g., setFirstResult(0).setMaxResults(10)) against a table that populates the GTT, and capture Db2 explain output: db2 EXPLAIN SQL <generated-sql>
4. Compare the EXPLAIN output for differences in plan steps, join methods, and cost estimates between the two scopes.
5. RUNSTATS ON TABLE SESSION_TBL and repeat the query to see if the plan changes.

If you can share the Db2 LUW version you are running and whether the captured plans differ between session and transaction scope, I can refine the recommendation for plan-stability strategies or optimizer-hint adjustments.