Db2 fetch multi rows top ideas to use Row-set positioning

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.

Multi-row fetch limit

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.