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

Executing a Bulk Fetch

A bulk fetch is a FETCH statement that retrieves multiple rows from a cursor into a host variable array.

To execute a bulk fetch:

1. Declare a host variable array
2. Open the cursor
3. Issue a FETCH statement with the BULK clause

Note: For more information about the FETCH statement, see the CA IDMS SQL Reference Guide.

Cursor Position

The first execution of a FETCH BULK statement retrieves the first set of rows from the cursor result table. After statement execution, cursor position is on the last row fetched. If the FETCH BULK statement is executed again before the cursor is closed, the next set of rows retrieved begins with the row following the cursor position. Fetching proceeds sequentially through the cursor result table until no more rows are found.

How Many Rows Are Fetched?

If you do not specify a ROWS parameter in the BULK clause, the FETCH statement retrieves as many rows as will fit between the starting row of the array and the end of the array.

If you specify a ROWS parameter in the BULK clause, the FETCH statement retrieves a number of rows equal to the value in the ROWS. This value must be less than or equal to the number of rows between the starting row of the array and the end of the array.

Maximum Rows Example

In this example, the program assigns a ROWS value that corresponds to the number of rows that can be displayed on a given display terminal:

 EXEC SQL  BEGIN DECLARE SECTION  END-EXEC.

 01 BULK-DIVISION.
    02 BULK-DIV OCCURS 100 TIMES.
       03 DEPT-ID         PIC   9(4).
       03 DEPT-NAME       PIC   X(40).
 01 DIV-CODE              PIC   X(3).
 01 WS-SCREEN-LENGTH      PIC   S9(4) COMP.
  .
  .
  .
 EXEC SQL
   DECLARE DIV_DEPT CURSOR FOR
     SELECT DEPT_ID, DEPT_NAME
       FROM DEPARTMENT
       WHERE DIV_CODE = :DIV-CODE
 END-EXEC.
 ACCEPT SCREENSIZE INTO WS-SCREEN-LENGTH.
 SUBTRACT 4 FROM WS-SCREEN-LENGTH.
 IF WS-SCREEN-LENGTH > 100 MOVE 100 TO
 WS-SCREEN LENGTH.
  .
  .
  .
 MOVE INPUT-DIV-CODE TO DIV-CODE.

 EXEC SQL
 OPEN DIV_DEPT
 END-EXEC.

 FETCH-PARAGRAPH.

 EXEC SQL
   FETCH DIV_DEPT
     BULK :BULK-DIVISION ROWS :WS-SCREEN-LENGTH
 END-EXEC.

 IF SQLCODE=100 MOVE 'Y' TO END-FETCH.
  .
  .
  .
 (Iterate paragraph until no more rows)

Specifying a Starting Row

The DBMS assigns the first row of the result table to the first row of the array unless you include the START parameter on the BULK clause. The START value corresponds to the subscript value of the array occurrence.

Checking Statement Execution

If program logic calls for repeating the FETCH BULK statement until no more rows are found, the program must test for SQLCODE = 100, as described in Using a Cursor. The DBMS always sets the value of SQLCNRP equal to the number of rows returned unless an error occurs during processing.

The following table shows the possible combination of values returned to SQLCODE and SQLCNRP on a FETCH BULK statement:

SQLCODE and SQLCNRP Values

Advantages of a Bulk Fetch

Using a BULK clause with a FETCH statement minimizes resources to retrieve data.

Unlike a bulk select, the program can retrieve an unlimited number of result rows by repeating a bulk fetch.

Bulk Fetch Considerations

• With a bulk fetch, the program generally cannot perform current or cursor operations such as a positioned update or delete because the cursor is always positioned on (or after) the last row fetched
• If an error occurs during the processing of a bulk fetch, the contents of the host variable array are unpredictable
• If a bulk fetch results in retrieval of a null value, the contents of the host variable for the corresponding column is unpredictable

Bulk Fetch Example

In this example, the program issues an INCLUDE TABLE statement to declare a host variable array for several columns of the EMPLOYEE table. Then it declares a cursor to select the column values from all rows of the table.

 EXEC SQL  BEGIN DECLARE SECTION  END-EXEC.

 EXEC SQL
   INCLUDE TABLE EMPLOYEE AS BULK-EMPLOYEE
   (EMP_ID, EMP_FNAME, EMP_LNAME, DEPT_ID)
   NUMBER OF ROWS 50
   PREFIX 'BULK-'
 END-EXEC.
 EXEC SQL  END DECLARE SECTION  END-EXEC.
  .
  .
  .
 EXEC SQL
   DECLARE EMP_CRSR CURSOR FOR
     SELECT EMP_ID,
            EMP_FNAME,
            EMP_LNAME,
            DEPT_ID
       ORDER BY 4, 3, 2
 END-EXEC.

When the FETCH statement is executed, the first 50 rows of the cursor result table are assigned to the BULK-EMPLOYEE array, because the default starting row assignment is 1 and the default number of rows assigned is the array size. If the FETCH statement is repeated, the next 50 rows of the result table are assigned to the array.

EXEC SQL
  OPEN EMP_CRSR
END-EXEC.
 .
 .
 .
EXEC SQL
  FETCH EMP_CRSR
    BULK :BULK-EMPLOYEE
END-EXEC.

IF SQLCODE = 100 MOVE 'Y' TO END-FETCH.