Saturday, 14 September 2013

DB2 Rowset Positioning Cursor.

A rowset is a group of rows operated that are operated on as a set. Such a cursor enables your program to reterieve more than one row using single FETCH statement. By FETCHING multiple rows at once, your request might become more efficient for distributed request.
To use this feature you must DECLARE your cursor with the WITH ROWSET POSITIONING parameter.
For example :
       EXEC SQL
           DECLARE CURSOR C1
              WITH ROWSET POSITIONING
           FOR
                 SELECT EMPNO FROM DSN900.EMP

       END-EXEC
Further to handle multiple row you should define appropriate structure of host variable (possibly an array) into which fetch row can be placed for processing. For example
     EXEC SQL
          FETCH ROWSET from C1 FOR 5 Rows INTO HOST-ARRAY
      END-EXEC

Rowset positioning can be combined with scrollable cursor to deliver very flexible and efficent data access. Same FIRST, LAST, NEXT etc. keyword are applicable for rowset positioning cursor as well but you need to suffix each keyword with "ROWSET" keyword for example FIRST ROWSET, NEXT ROWSET etc.


Created with Artisteer

No comments:

Post a Comment