Manage memory for collections

does not automatically allocate or deallocate memory for collection variables. You must explicitly manage the memory that is allocated to a collection variable.

Use the following SQL statements to manage memory for both typed and untyped collection host variables:

The ALLOCATE COLLECTION statement allocates memory for the specified collection variable.
This collection variable can be a typed or untyped collection. The ALLOCATE COLLECTION statement sets SQLCODE (sqlca.sqlcode) to zero if the memory allocation was successful and a negative error code if the allocation failed.
The DEALLOCATE COLLECTION statement deallocates memory for a specified collection variable.
After you free the collection variable with the DEALLOCATE COLLECTION statement, you can reuse the collection variable.

Important: You must explicitly deallocate memory allocated to a collection variable. Use the DEALLOCATE COLLECTION statement to deallocate the memory.

The following code fragment declares the a_set host variable as a typed collection, allocates memory for this variable, then deallocates memory for this variable:

EXEC SQL BEGIN DECLARE SECTION;
   client collection set(integer not null) a_set;
EXEC SQL END DECLARE SECTION;
;

EXEC SQL allocate collection :a_set;
;

EXEC SQL deallocate collection :a_set;

The ALLOCATE COLLECTION statement allocates memory for the collection variable and the collection data.

When DEALLOCATE COLLECTION fails because a cursor on the collection is still open, an error message is returned. Before this, the error is not trapped.