Row-positioned or Rowset-positioned FETCH statements in DB2

How to fetch multi-rows using Cursor
How to fetch multi-rows using Cursor

Rowset-positioning: Specifies whether multiple rows of data can be accessed as a rowset on a single FETCH statement for the cursor. The default is WITHOUT ROWSET POSITIONING.

WITHOUT ROWSET POSITIONING
Specifies that the cursor can be used only with row-positioned FETCH statements. The cursor is to return a single row for each FETCH statement and the FOR n ROWS clause cannot be specified on a FETCH statement for this cursor. WITHOUT ROWSET POSITIONING or single row access refers to how data is fetched from the database engine. For remote access, data might be blocked and returned to the client in blocks.

WITH ROWSET POSITIONING
Specifies that the cursor can be used with either row-positioned or rowset-positioned FETCH statements. This cursor can be used to return either a single row or multiple rows, as a rowset, with a single FETCH statement. ROWSET POSITIONING refers to how data is fetched from the database engine. For remote access, if any row qualifies, at least 1 row is returned as a rowset. The size of the rowset depends on the number of rows specified on the FETCH statement and on the number of rows that qualify. Data might be blocked and returned to the client in blocks.

Best multi-row fetch limit:

OR n ROWS in the FETCH statement explicitly determines size of the rowset, and “n” can be a host variable. By retrieving multiple rows in a single SQL statement, the application can reduce its CPU costs associated with connecting to DB2. So, for example, if you specify FOR 100 ROWS, every FETCH statement will attempt to retrieve up to 100 rows at a time. Thus, you can effectively eliminate up to 99% of your SQL calls.

So, max 100 rows at a time is best option for performance.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.