fetch-clause

The fetch-clause sets a maximum number of rows that can be retrieved. It specifies that an application does not want to retrieve more than fetch-row-count rows, regardless of how many rows there might be in the intermediate result table when this clause is not specified. An attempt to fetch beyond fetch-row-count rows is handled the same way as normal end of data.

A fetch-clause can also be specified in a SELECT INTO statement.

Determining a predictable set of rows to retrieve requires the specification of an ORDER BY clause with sort keys that would uniquely identify the sort order of each row in the intermediate result table. If the intermediate result table includes duplicate sort keys for some rows, the order of these rows is not deterministic. If there is no ORDER BY clause, the intermediate result table is not in a deterministic order. If the order of the intermediate result table is not deterministic, the set of rows retrieved is unpredictable. If both the fetch-clause and the ORDER BY clause are specified, the fetch-clause is processed on the ordered data.

If the fullselect contains an SQL data change statement in the FROM clause, all the rows are modified regardless of the limit on the number of rows fetched.

fetch-row-count

A constant or variable that specifies the maximum number of rows to retrieve. fetch-row-count must be a numeric value that is a positive number or zero. If the value is not BIGINT, the value is cast to a BIGINT value. fetch-row-count must not be the null value.

When the fetch-clause is specified as a prepare attribute, fetch-row-count must not reference a variable.

Use of the fetch-clause with a constant for fetch-row-count that is not greater than the maximum big integer influences query optimization of the subselect or fullselect, based on the fact that, at most, a known number of rows will be retrieved.

Limiting the result table to a specified number of rows can improve performance. In some cases, the database manager will cease processing the query when it has determined the specified number of rows. If the offset-clause is also specified with a constant for offset-row-count, the database manager will also consider the offset value constant in determining when to cease processing.

Row access controls can indirectly affect the fetch-clause because row access controls affect the rows that are accessible to the authorization ID or role of the subselect or fullselect. Column access controls do no affect the fetch-clause.