fetch-first-clause

                .-1-------.                   
>>-FETCH FIRST--+---------+--+-ROW--+--ONLY--------------------><
                '-integer-'  '-ROWS-'         

The FETCH FIRST clause sets a maximum number of rows that can be retrieved. FETCH FIRST specifies that only integer rows should be made available to be retrieved, regardless of how many rows there might be in the result table when this clause is not specified. An attempt to fetch beyond integer rows is handled the same way as normal end of data. The value of integer must be a positive integer (not zero). The default is 1.

The FETCH FIRST clause specifies an ordering of the rows of the result table. A subselect that contains a FETCH FIRST clause cannot be specified in the following objects:

• The outermost fullselect of a view
• The definition of a materialized query table

Limiting the result table to the first n rows can improve performance. The DB2® system will cease processing the query when it has determined the first n rows. If both the FETCH FIRST clause and the OPTIMIZE FOR clause are specified, the lower of the integer values from these clause will be used to influence the buffer size. The values are considered independently for optimization purposes. If the OPTIMIZE FOR clause is not specified, a default of OPTIMIZE FOR integer ROWS, where integer is the value that is specified in the FETCH FIRST clause, is assumed. The DB2 system uses this value for access path optimization.

Specification of the FETCH FIRST clause in an outermost fullselect makes the result table read-only. A read-only result table must not be referenced in an UPDATE, MERGE, or DELETE statement. The FETCH FIRST clause cannot be used in an outermost fullselect that contains a FOR UPDATE clause.

If both the FETCH FIRST clause and the ORDER BY clause are specified, the ordering is performed on the entire result table prior to returning the first n rows.

If the FETCH FIRST clause is specified in the outermost fullselect of a SELECT statement that contains a data change statement (an INSERT, DELETE, UPDATE, or MERGE statement), all rows are processed by the specified data change statement, but only the number of rows that is specified in the FETCH FIRST clause are returned in the final result table.

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