The ALLOCATE CURSOR statement defines a cursor and associates
it with a result set locator variable.
Invocation
This statement can be embedded
in an application program. It is an executable statement that can
be dynamically prepared. It cannot be issued interactively. It must
not be specified in REXX.
Authorization
None required.
Syntax

>>-ALLOCATE -cursor-name--CURSOR FOR RESULT SET--rs-locator-variable-><
Description
- cursor-name
- Names the cursor. The name must not identify a cursor that has
already been declared in the source program.
- CURSOR FOR RESULT SET rs-locator-variable
- Specifies a result set locator variable that has been declared
in the application program according to the rules for declaring result
set locator variables.
- The result set locator variable must contain a valid result set
locator value as returned by the ASSOCIATE LOCATORS or DESCRIBE PROCEDURE
SQL statement. The value of the result set locator variable is used
at the time the cursor is allocated. Subsequent changes to the value
of the result set locator have no effect on the allocated cursor.
The result set locator value must not be the same as a value used
for another cursor allocated in the source program.
Rules
- The following rules apply when you use an allocated cursor:
- You cannot open an allocated cursor with the OPEN statement.
- You can close an allocated cursor with the CLOSE statement. Closing
an allocated cursor closes the associated cursor defined in the stored
procedure.
- You can allocate only one cursor to each result set.
- Allocated cursors follow the same rules as declared cursors in
a program with the CLOSQLCSR option. The CLOSQLCSR option can be specified
on the CRTSQLxxx command or using the SET OPTION statement.
- A commit operation closes allocated cursors that are not defined
WITH HOLD by the procedure and are not running with a commit level
other than *NONE.
- Closing an allocated cursor closes the associated cursor in the
procedure.
Example
This SQL procedure example defines
and associates cursor C1 with the result set locator variable LOC1
and the related result set returned by the SQL procedure:
ALLOCATE C1 CURSOR FOR RESULT SET LOC1;