Rowset fetches and inserts

For rowset-positioned cursors, when the cursor is opened for rowset processing, the answer set is returned in a single query block. The query block contains exactly the number of rows that are specified for the rowset.

Because a rowset is returned in a single query block, the size of a rowset is limited to 10 MB. This rowset size minimizes the impact to the network when retrieving a large rowset with a single fetch operation.

Rowset-positioned cursors also enable a form of multiple-row inserts. The FOR n ROWS form of the INSERT statement inserts multiple rows into a table or view by using values that are provided in a host-variable array. With this form of INSERT, all insert data is sent in a single network message, instead of separate INSERT statements being sent for inserted row. For more information about this form of INSERT statement, see Inserting multiple rows of data from host-variable arrays.

Rowset fetches and accelerated queries

Accelerated queries are also limited to a rowset size of 10 MB, to minimize network impact and Db2 storage consumption.

The 10 MB limit applies regardless of whether the cursor is declared WITH ROWSET POSITIONING. For example, a JDBC application might need to reduce the number of network exchanges that are required to fetch an accelerated query's results. To do that, the application needs to use the Statement.setFetchSize method and the ResultSet.setFetchSize method to specify the default fetch size for the accelerated query.

When you specify a fetch size, ensure that the fetch size does not exceed the rowset size limit of 10 MB for a single query block.