When WITH HOLD is specified, a commit operation commits all the changes in the current unit of work, and releases all locks except those that are required to maintain the cursor position. Afterwards, a FETCH statement is required before a Positioned UPDATE or DELETE statement can be executed.
All cursors are implicitly closed by a CONNECT (Type 1) or rollback operation. All cursors associated with a connection are implicitly closed by a disconnect of the connection. A cursor is also implicitly closed by a commit operation if WITH HOLD is not specified, or if the connection associated with the cursor is in the release-pending state.
If a cursor is closed before the commit operation, the effect is the same as if the cursor was declared without the WITH HOLD option.
For both SQL and external stored procedures, cursors defined using the WITH RETURN clause (or identified as a result set cursor in a SET RESULT SETS statement) that are still open when the procedure ends define the result sets for the procedure. For SQL stored procedures, all other open cursors are closed when the procedure ends. For external stored procedures, all other open cursors remain open.
For both SQL and external stored procedures, if no cursors in the stored procedure are defined using the WITH RETURN or WITHOUT RETURN clauses, and no cursors are identified as a result set cursor in a SET RESULT SETS statement, then any cursor that is open when the stored procedure ends becomes a result set cursor.
For non-scrollable cursors, the result set consists of all rows from the current cursor position to the end of the result table. For scrollable cursors, the result set consists of all rows of the result table.
TO CLIENT may be necessary if the result set is returned from an ILE program with multiple modules.
The select-statement must not include parameter markers (except for REXX), but can include references to variables. In host languages, other than REXX, the declarations of the host variables must precede the DECLARE CURSOR statement in the source program. In REXX, parameter markers must be used in place of variables and the statement must be prepared.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.