DB2 V8 - Multiple-row INSERT and FETCH statements


Volta a página anterior

Volta ao Menu Principal


Desenvolvido por DORNELLES Carlos Alberto - Analista de Sistemas - Brasília DF. - cad_cobol@hotmail.com

DB2 V8 - Multiple-row INSERT and FETCH statements

You can enhance the performance of your application programs by using multiple-row INSERT and FETCH statements to request that DB2 send multiple rows of data at one time to and from the database.
For local applications, using these multiple-row statements results in fewer accesses of the database.
For distributed applications, using these multiple-row statements results in fewer network operations and a significant improvement in performance.
This topic provides an overview of how you can:

  • Insert multiple rows of data from host variable arrays that have been declared and populated in your application program into the database; see Inserting multiple rows.
  • Fetch multiple rows of data from the database into host variable arrays that have been declared or dynamically allocated in your program; see Fetching multiple rows.

To use a host variable array in an SQL statement, specify a host variable array that is declared according to host language rules.
You can specify host variable arrays in C, C++, COBOL, and PL/I application programs.
You must declare the array in the host program before you use it in an SQL statement.

You can also use a storage area that you allocate dynamically when you use a descriptor to describe the data areas that you want DB2 to use to insert or place the data. You can specify a descriptor in assembler, C, C++, COBOL, and PL/I application programs.
You must include an SQL descriptor area (SQLDA) in the host program.

Inserting multiple rows

You can use a form of the INSERT statement to insert multiple rows from values that are provided in host variable arrays.
Each array contains values for a column of the target table.
The first value in an array corresponds to the value for that column for the first inserted row, the second value in the array corresponds to the value for the column in the second inserted row, and so on.
DB2 determines the attributes of the values based on the declaration of the array.

Example: You can insert the number of rows that are specified in the host variable NUM-ROWS by using the following INSERT statement: 

EXEC SQL
     INSERT INTO DSN8810.ACT 
           (ACTNO
     ,      ACTKWD
     ,      ACTDESC)
            VALUES 
           (:HVA1
     ,      :HVA2
     ,      :HVA3 :IVA3)
            FOR :NUM-ROWS ROWS
END-EXEC.

Assume that the host variable arrays HVA1, HVA2, and HVA3 have been declared and populated with the values that are to be inserted into the ACTNO, ACTKWD, and ACTDESC columns.
The NUM-ROWS host variable specifies the number of rows that are to be inserted, which must be less than or equal to the dimension of each host variable array.

Assume also that the indicator variable array IVA3 has been declared and populated to indicate whether null values are inserted into the ACTDESC column.
Use indicator variable arrays with host variable arrays in the same way that you use indicator variables with host variables.
An indicator variable array must have at least as many entries as its host variable array.

You can use the multiple-row INSERT statement both statically and dynamically.
If you prepare and execute the INSERT statement, you can code the EXECUTE statement to use either host variable arrays or an SQL descriptor (SQLDA).
If you use host variable arrays, each host variable array in the USING clause of the EXECUTE statement represents a parameter marker in the INSERT statement.
If you use an SQLDA, the host variable in the USING clause of the EXECUTE statement names the SQLDA that describes the parameter markers in the INSERT statement.

Fetching multiple rows

You can retrieve multiple rows of data by using a row-set positioned cursor.
A row-set positioned cursor retrieves zero, one, or more rows at a time, as a row set, from the result table of the cursor into host variable arrays.
You can reference all of the rows in the row set, or only one row in the row set, when you use a positioned DELETE or positioned UPDATE statement after a FETCH statement that retrieves row sets.

A multiple-row FETCH statement can be used to copy a row set of column values into either of the following data areas:

  • Host variable arrays that are declared in your program
  • Dynamically allocated arrays whose storage addresses are put into an SQL descriptor area (SQLDA), along with the attributes of the columns to be retrieved
Declaring a row-set positioned cursor

You must first declare a row-set positioned cursor before you can retrieve row sets of data.
To enable a cursor to fetch row sets, use the WITH ROWSET POSITIONING clause in the DECLARE CURSOR statement.

Example: The following statement declares a row set cursor: 

EXEC SQL
     DECLARE C1 CURSOR 
             WITH ROWSET POSITIONING FOR
             SELECT EMPNO
             ,      LASTNAME
             ,      SALARY
               FROM DSN8810.EMP
END-EXEC.

To tell DB2 that you are ready to process the first row set of the result table, execute the OPEN statement in your program.
DB2 then uses the SELECT statement within the DECLARE CURSOR statement to identify the rows in the result table.

Using a multiple-row FETCH statement with host variable arrays

When your program executes a FETCH statement with the ROWSET keyword, the cursor is positioned on a row set in the result table.
That row set is called the current row set. Declare the dimension of each of the host variable arrays to be greater than or equal to the number of rows that are to be retrieved.

Example: The following FETCH statement retrieves 20 rows into host variable arrays that are declared in your program: 

EXEC SQL
     FETCH NEXT ROWSET FROM C1 
           FOR 20 ROWS 
      INTO :HVA-EMPNO
      ,    :HVA-LASTNAME
      ,    :HVA-SALARY :INDA-SALARY
END-EXEC.
Using a multiple-row FETCH statement with a descriptor

Suppose that you want to dynamically allocate the necessary storage for the arrays of column values that are to be retrieved from the employee table.
You must do the following steps:

  1. Declare an SQLDA structure.
  2. Dynamically allocate the SQLDA and the necessary arrays for the column values.
  3. Set the fields in the SQLDA for the column values that are to be retrieved.
  4. Open the cursor.
  5. Fetch the rows.

After allocating the SQLDA and the necessary arrays for the column values, you must set the fields in the SQLDA.

Example: After the OPEN statement, the program fetches the next row set by using the following statement: 

EXEC SQL 
     FETCH NEXT ROWSET FROM C1 
           FOR 20 ROWS
           USING DESCRIPTOR :outsqlda; 
END-EXEC.

The USING clause of the FETCH statement names the SQLDA that describes the columns that are to be retrieved.

Using row-set positioned UPDATE statements

After your program executes a FETCH statement to establish the current row set, you can use a positioned UPDATE statement with either of the following clauses:

  • WHERE CURRENT OF cursor-name to update:
    • a single row if the cursor is on a single row
    • all the rows of a row set if the cursor is on a row set
  • WHERE CURRENT OF cursor-name FOR ROW n OF ROWSET to update only row n of the current row set

Updating all rows of the current row set: The following positioned UPDATE statement uses the WHERE CURRENT OF clause: 

EXEC SQL
     UPDATE DSN8810.EMP 
        SET SALARY = 50000
      WHERE CURRENT OF C1
END-EXEC.

When the UPDATE statement is executed, the cursor must be positioned on a row or row set of the result table.
If the cursor is positioned on a row, that row is updated.
If the cursor is positioned on a row set, all of the rows in the row set are updated.

Updating a specific row of the current row set: The following positioned UPDATE statement uses the WHERE CURRENT OF cursor FOR ROW n OF ROWSET clause:

EXEC SQL
     UPDATE DSN8810.EMP 
        SET SALARY = 50000
      WHERE CURRENT OF C1 FOR ROW 5 OF ROWSET
END-EXEC.

When the UPDATE statement is executed, the cursor must be positioned on a row set of the result table.
The specified row (in the example, row 5) of the current row set is updated.

Using row-set positioned DELETE statements

After your program executes a FETCH statement to establish the current row set, you can use a positioned DELETE statement with either of the following clauses:

  • WHERE CURRENT OF cursor-name to delete:
    • a single row if the cursor is on a single row
    • all the rows of a row set if the cursor is on a row set
  • WHERE CURRENT OF cursor-name FOR ROW n OF ROWSET to delete only row n of the current row set

Deleting all rows of the current row set: The following positioned DELETE statement uses the WHERE CURRENT OF clause: 

EXEC SQL
     DELETE FROM DSN8810.EMP 
      WHERE CURRENT OF C1
END-EXEC.

When the DELETE statement is executed, the cursor must be positioned on a row or row set of the result table.
If the cursor is positioned on a row, that row is deleted, and the cursor is positioned before the next row of its result table.
If the cursor is positioned on a row set, all of the rows in the row set are deleted, and the cursor is positioned before the next row set of its result table.

Deleting a single row of the current row set:
The following positioned DELETE statement uses the WHERE CURRENT OF cursor FOR ROW n OF ROWSET clause:

EXEC SQL
     DELETE FROM DSN8810.EMP 
      WHERE CURRENT OF C1 FOR ROW 5 OF ROWSET
END-EXEC.

When the DELETE statement is executed, the cursor must be positioned on a row set of the result table.
The specified row of the current row set is deleted, and the cursor remains positioned on that row set.
The deleted row (in the example, row 5 of the row set) cannot be retrieved or updated.