ROWSET Processing using Cursor – DB2

ROWSET processing is a group of rows for the result set of a query that is returned by a single FETCH statement. You can control the number of rows you want to fetch and return by specifying rowset limit in your program. ROWSET size is specified on the FETCH statement, the maximum rowset size is 32,767. This Can significantly improve performance as large amounts of data are either retrieved or inserted into DB2 tables at one go.

Multi-row FETCH: A single FETCH statement can retrieve multiple rows of data from the result table of a query as a rowset. DECLARE CURSOR adds a new “WITH ROWSET POSITIONING” clause. Row positioning FETCH statements may be used with cursor declared with the “WITH ROWSET POSITIONING” clause, or the “WITHOUT ROWSET POSITIONING” clause. Rowset positioning FETCH statements may only be used with cursors declared with the “WITH ROWSET POSITIONING” clause.

Working Storage – ROWSET Processing:

Your program needs to be written to include arrays that will work with multi-row SQL operations.

• A host variable array is an array in which each element of the array contains a value for the same column
• Can only be referenced in multi-row fetch or insert
• In general, arrays may not be arrays of structures

01 OUTPUT-VARS.
 05 LASTNME OCCURS 10 TIMES.
    49 LASTNME-LGTH PIC S9(4) USAGE COMP.
    49 LASTNME-DATA PIC X(50).
 05 EMPNO PIC S9(9) COMP-4 OCCURS 10 TIMES.

Operations – ROWSET Processing

DECLARE Cursor

To enable a cursor to fetch rowsets, use the WITH ROWSET POSITIONING clause in the DECLARE CURSOR statement.

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

OPEN Cursor

EXEC SQL
   OPEN C1
END-EXEC.

FETCH Cursor

Rowset positioning specifies whether multiple rows of data can be accessed as a rowset on a single FETCH statement – default is WITHOUT ROWSET POSITIONING. Fetch the first x rows and leave the cursor positioned on that rowset at the completion of the fetch. FOR n ROWS with FETCH FIRST n ROWS ONLY these two clauses may be used together. FETCH FIRST n ROWS ONLY dominates.

 EXEC SQL
 FETCH FIRST ROWSET FROM C1 FOR 50 ROWS
      INTO :LASTNME,:EMPNO
 END-EXEC.
 EXEC SQL
      FETCH NEXT ROWSET FROM C1 FOR 50 ROWS
      INTO :LASTNME,:EMPNO
 END-EXEC.

For example, if there are 180 rows being fetched and the rowset size is 50 so in ROWSET processing:
Fetch 1 will get a zero SQLCODE and the SQLERRD(3) will be 50
Fetch 2 will get a zero SQLCODE and the SQLERRD(3) will be 50
Fetch 3 will get a zero SQLCODE and the SQLERRD(3) will be 50
Fetch 4 will get a zero SQLCODE and the SQLERRD(3) will be 30

If you try to fetch beyond the result set you will receive an end of data condition.

• Consider, for example, that there are only 30 rows left in the result table and you issue the following FETCH request: FETCH NEXT ROWSET FOR 50 ROWS
• The last 30 rows will be returned along with an SQLCODE +100. SQLERRD(3) would be set to 30.
• This is also the case when FETCH FIRST n ROWS ONLY has been specified

The current “sweet spot” for multi-row FETCH is at about 100 rows. Performance gains start with 10 rows; if you are going to retrieve less than that it may not make a lot of sense to code multi-row FETCH.

How to fetch the specified number of rows regardless of the current position of cursor: Fetch 5 rows starting with row 10 regardless of the current position of the cursor and cause the cursor to be positioned on that rowset at the completion of the fetch.

FETCH ROWSET
    STARTING AT ABSOLUTE 10 FROM C1
FOR 5 ROWS INTO …

If FOR n ROWS is NOT specified and the cursor is declared for ROWSET positioning. The ROWSET will be the same as the previous ROWSET FETCH if…

• It was the previous FETCH for this cursor…
• Or the previous FETCH was a FETCH BEFORE or FETCH AFTER and the FETCH before that was a ROWSET FETCH.
• Otherwise the ROWSET defaults to 1

Multi- Row Fetch SQLCODES for ROWSET Processing

• –227 FETCH fetch-orientation IS NOT ALLOWED, BECAUSE CURSOR cursor-name HAS AN UNKNOWN POSITION
• -246 STATEMENT USING CURSOR cursor-name SPECIFIED NUMBER OF ROWS num-rows WHICH IS NOT VALID WITH dimension
• -248 A POSITIONED DELETE OR UPDATE FOR CURSOR cursor-name SPECIFIED ROW n OF ROWSET, BUT THE ROW IS NOT CONTAINED WITHIN THE CURRENT ROWSET
• -249 DEFINITION OF ROWSET ACCESS FOR CURSOR cursor-name IS INCONSISTENT WITH THE FETCH ORIENTATION CLAUSE clause SPECIFIED
• -253 A NON-ATOMIC statement STATEMENT SUCCESSFULLY COMPLETED FOR SOME OF THE REQUESTED ROWS, POSSIBLY WITH WARNINGS, AND ONE OR MORE ERRORS
• -254 A NON-ATOMIC statement STATEMENT ATTEMPTED TO PROCESS MULTIPLE ROWS OF DATA, BUT ERRORS OCCURRED
• -353 FETCH IS NOT ALLOWED, BECAUSE CURSOR cursor-name HAS AN UNKNOWN POSITION
• -508 THE CURSOR IDENTIFIED IN THE UPDATE OR DELETE STATEMENT IS NOT POSITIONED ON A ROW OR ROWSET THAT CAN BE UPDATED OR DELETED
• -589 A POSITIONED DELETE OR UPDATE STATEMENT FOR CURSOR cursor-name SPECIFIED A ROW OF A ROWSET, BUT THE CURSOR IS NOT POSITIONED ON A ROWSET
• -5012 HOST VARIABLE host-variable IS NOT EXACT NUMERIC WITH SCALE ZERO
• -20185 CURSOR cursor-name IS NOT DEFINED TO ACCESS ROWSETS, BUT A CLAUSE WAS SPECIFIED THAT IS VALID ONLY WITH ROWSET ACCESS

INSERT Cursor

A single INSERT statement can add multiple rows of data from an array. For static and dynamic SQL (examples upcoming) FOR n ROWS. Maximum n is 32,767.

ATOMIC – if any single row insert fails, all fail

NOT ATOMIC – rows inserted independently. NOT ATOMIC CONTINUE ON SQLEXCEPTION allows some rows to be successfully inserted into a table and failing rows to be indicated for possible further processing (For example, SQLCODE -803). GET DIAGNOSTICS should be used to determine the failing rows.

• If errors occur during execution of INSERT, processing continues
• Diagnostics are available for each failed row through GET DIAGNOSTICS (more on GET DIAGNOSTICS later)
• SQLCODE will indicate if all failed, all were successful or at least one failed

Static SQL INSERT with host variable arrays.

INSERT INTO EMP FOR n ROWS
VALUES(:LASTNME,:EMPNO) ATOMIC

UPDATE Cursor

Update a specified row in the rowset:

UPDATE T1
SET C1 = 5
WHERE CURRENT OF cursor-name
FOR ROW row-number OF ROWSET

Instead, if you specify the existing WHERE CURRENT OF cursor-name, all the rows in the rowset are updated. For example: Update all the rows in the rowset that cursor CSR1 is positioned on.

UPDATE T1 SET C1 = 5 WHERE CURRENT OF CSR1

DELETE Cursor

Following clause to delete a specified row in the rowset processing:

DELETE FROM T1
WHERE CURRENT OF cursor-name
FOR ROW row-number OF ROWSET

Instead, if you specify the existing WHERE CURRENT OF cursor name, all the rows in the rowset are deleted. The following example deletes the fourth row in the rowset that cursor CSR1 is positioned on:

DELETE FROM T1
WHERE CURRENT OF CSR1
FOR ROW 4 OF ROWSET

DB2 blogs : Click Here IBM DB2 Manual : Click Here