BULK Table Processing

BULK table processing offers a way to retrieve or insert multiple rows with the execution of a single SQL command. Three commands can be used in this fashion:

You can use the BULK SELECT command when you know in advance the maximum number of rows in a multiple-row query result, as when the query result will contain a row for each month of the year or day of the week. This command minimizes the time a table is locked for the retrieval operation, because the program can execute the BULK SELECT command, then immediately terminate the transaction, even before displaying any rows.
You can use the BULK FETCH command to handle multiple-row query results of unpredictable maximum length. This use of a cursor is most suitable for display only applications, such as programs that let a user browse through a query result, so many rows at a time.
You can use the BULK INSERT command to insert multiple rows into a table. Like the BULK SELECT command, this command is efficient for concurrency, because any exclusive lock acquired to insert rows need be held only until the BULK INSERT command is executed.

In each of these three commands, the host variables that hold rows are in an array, as illustrated in the following example. The example shows how you can use a cursor to retrieve and display ten rows at a time from the active set. The host variable named STARTINDEX is set to 1 so that the first row in each group of rows fetched is stored in the first element of the PARTSTABLE array. The host variable named NUMBEROFROWS controls the maximum number of rows returned with each execution of the BULK FETCH command. STARTINDEX and NUMBEROFROWS are set in the paragraph named DISPLAY-TABLE.



   EXEC SQL BEGIN DECLARE SECTION END-EXEC.

   01  PARTSTABLE.

     05  TABLE-ELEMENT        OCCURS 10 TIMES.

       10  PARTNUMBER         PIC X(16).

       10  PARTNAME           PIC X(30).

       10  PARTNAMEIND        SQLIND.

   01  STARTINDEX             PIC S9(9) COMP.

   01  NUMBEROFROWS           PIC S9(9) COMP.

   EXEC SQL END DECLARE SECTION END-EXEC.

   01  OK                     PIC S9(9) COMP VALUE    0.

   01  NOTFOUND               PIC S9(9) COMP VALUE  100.

   01  I                      PIC S9(9) COMP.

   01  MAXIMUMROWS            PIC S9(9) COMP VALUE   10.

   .

   .

   .

   PROCEDURE DIVISION.



       EXEC SQL DECLARE PARTSCURSOR

                 CURSOR FOR

                 SELECT PARTNUMBER, PARTNAME

                   FROM PURCHDB.PARTS

       END-EXEC.

   .

   .

   .

       EXEC SQL OPEN PARTSCURSOR END-EXEC.

       IF SQLCODE = OK

          PERFORM DISPLAY-TABLE THRU DISPLAY-TABLE-EXIT

          UNTIL SQLCODE = NOTFOUND

       ELSE

       IF SQLCODE = NOTFOUND

          DISPLAY "The PurchDB.Parts table is empty!"

       ELSE

          PERFORM SQL-STATUS-CHECK.

   DISPLAY-TABLE.

       The STARTINDEX and NUMBEROFROWS host variables

       are initialized, then the BULK FETCH command is

       executed.

       MOVE 1 TO STARTINDEX.

       MOVE MAXIMUMROWS TO NUMBEROFROWS.

       EXEC SQL BULK FETCH  PARTSCURSOR

                      INTO :PARTSTABLE,

                           :STARTINDEX,

                           :NUMBEROFROWS

       END-EXEC.

       As many as ten rows are put into the PARTSTABLE

       array.  If the FETCH command executes without error,

       the value in SQLERRD(3) indicates the number of rows

       returned to PARTSTABLE.

       IF SQLCODE = OK

          PERFORM DISPLAY-ROW VARYING I FROM 1 BY 1

            UNTIL I = SQLERRD(3)

       ELSE

       IF SQLCODE = NOTFOUND

          DISPLAY "No more rows qualify!"

       ELSE

          PERFORM SQL-STATUS-CHECK.

   DISPLAY-TABLE-EXIT.

   DISPLAY-ROW.

       This paragraph displays all the rows returned

       to the PARTSTABLE array during the last BULK FETCH.

BULK table processing is discussed in additional detail in the chapter, "Bulk Table Processing."

BULK Table Processing

Technical documentation

» Table of Contents

» Index