---
title: "DB2 Fetch Multi-rows: Top Ideas to use Row-set Positioning"
description: "Rowset 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. https..."
url: https://srinimf.com/2016/07/13/row-positioned-or-rowset-positioned-fetch-statements-in-db2/
date: 2016-07-13
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2016/07/multi-row.jpg?fit=1200%2C900&ssl=1
categories: ["DB2"]
tags: ["Best option for n rows in DB2 for Multi-fetch", "sql row set positioned cursor"]
type: post
lang: en
---

# DB2 Fetch Multi-rows: Top Ideas to use Row-set Positioning

![](https://i0.wp.com/srinimf.com/wp-content/uploads/2016/07/untitled.png?resize=263%2C150&ssl=1)
**Rowset 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.

Related[DB2 Rowset to Fetch Multi-rows Easily](https://srinimf.com/2010/10/16/db2-we-can-fetch-multi-rows-with-cursor/)

### **WITHOUT ROWSET POSITIONING**

Specifies that the cursor can be used only with row-positioned FETCH statements.

Related[DB2 Tough Questions (3 of 4)](https://srinimf.com/2012/02/01/db2-tough-questions-3-of-4/)

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 fetching](https://i0.wp.com/srinimf.com/wp-content/uploads/2019/09/multi-row-fetching.jpg?resize=851%2C315&ssl=1)

### **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.

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2016%2F07%2F13%2Frow-positioned-or-rowset-positioned-fetch-statements-in-db2%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2016%2F07%2Fmulti-row.jpg%3Ffit%3D1200%252C900%26ssl%3D1&description=DB2%20Fetch%20Multi-rows%3A%20Top%20Ideas%20to%20use%20Row-set%20Positioning)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2016/07/13/row-positioned-or-rowset-positioned-fetch-statements-in-db2/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2016/07/13/row-positioned-or-rowset-positioned-fetch-statements-in-db2/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2016/07/13/row-positioned-or-rowset-positioned-fetch-statements-in-db2/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2016/07/13/row-positioned-or-rowset-positioned-fetch-statements-in-db2/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20Fetch%20Multi-rows%3A%20Top%20Ideas%20to%20use%20Row-set%20Positioning&body=https%3A%2F%2Fsrinimf.com%2F2016%2F07%2F13%2Frow-positioned-or-rowset-positioned-fetch-statements-in-db2%2F&share=email)
-

### Like this:

Like Loading…