DB2 WITH HOLD option What does WITH HOLD option do ?
-
forces cursor to close after commit
-
Keeps cursor open after commit
-
Does not allow cursor to close till all rows are fetched
-
Locks the cursor
-
None of the above
The WITH HOLD option in DB2 (and other RDBMS) keeps a cursor open across COMMIT operations. Without WITH HOLD, the cursor would close automatically when a COMMIT is issued. It doesn't force closure, lock the cursor, or wait for all rows to be fetched.
The WITH HOLD option on a DB2 cursor declaration keeps the cursor open and its position preserved across a COMMIT. Normally, a commit closes all open cursors and releases their locks/position; WITH HOLD overrides that default so an application can continue fetching rows after committing, which is useful for long-running batch processes that need periodic commits without losing their place.