Data Manipulation with SQL › Bulk Processing › Executing a Bulk Select

Executing a Bulk Select

A bulk select is a SELECT statement that retrieves multiple rows from the database into a host variable array:

1. Declare a host variable array
2. Issue the SELECT statement with a BULK clause, as in this example:

   EXEC SQL
      SELECT DEPT_ID,
             DEPT_NAME,
             DIV-CODE,
             DEPT_HEAD_ID
         BULK :BULK-DEPARTMENT
         FROM DEPARTMENT
   END-EXEC.
   

Checking the Status of a Bulk Select

A successful bulk select returns 100 to SQLCODE. A value of 100 will be returned if there are fewer result rows than entries in the bulk array or if the number of result rows is the same as the number of entries. If the array is too small for the result table, the statement returns a cardinality violation error.

The following table shows the possible combinations of SQLCODE and SQLCNRP values on a bulk select:

Advantage of a Bulk Select

A bulk select retrieves a set of rows using fewer resources than a series of single-row SELECT statements to retrieve the same rows.

Bulk Select Considerations

A bulk select:

• Cannot retrieve more rows than there are occurrences in the host variable array
• Retrieves the same set of rows, not the next set of rows, if the statement is reissued within the database transaction
• Causes the contents of the host variable array to be unpredictable if an error occurs during processing

A bulk select is appropriate only when selecting from a table with a number of rows that you consider fixed, such as a table of the 50 states and their mailing codes.

If the size of the host variable array may be too small for the result table, you should declare a cursor for the SELECT statement and use a bulk fetch.