Setting Result Set Holdability

By default, a result set created within a transaction is closed after the transaction is committed to the database. It is sometimes useful for the result set to be held open after the transaction has been committed. To prevent the result set from being closed, set the holdability property to HOLD_CURSORS_OVER_COMMIT.

As a result of using the HOLD_CURSORS_OVER_COMMIT value, the result set is materialized in-memory. To facilitate queries with result sets that exceed memory available, NuoDB provides Spill to Disk capability. If Spill to Disk is enabled and the result set exceeds the DISKSPILL_MEMORY_THRESHOLD_MB value, the result set will be written to disk on the Transaction Engine (TE). For more information on Spill to Disk in NuoDB, see Reducing Memory Pressure on Transaction Engines.