EXPERT RESPONSE
The FETCH FIRST in ROWS ONLY clause is exactly what you are looking for. Unfortunately, this clause may not be supported by DB2 on the version you are using.
The fetch-first clause sets a maximum number of rows that can be retrieved. It lets the database manager know that the application does not want to retrieve more than integer rows, 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 (SQLSTATE 02000). The value of integer must be a positive integer (not zero). Limiting the result table to the first integer rows can improve performance.
The database manager will cease processing the query once it has determined the first integer rows. If both the fetch-first clause and the optimize-for clause are specified, the lower of the integer values from these clauses will be used to influence the communications buffer size. The values are considered independently for optimization purposes.
If your version of DB2 does not support the fetch-first clause, you are sort of stuck with opening a cursor and counting rows. You might be able to limit the number of rows returned using some "tricky" SQL but performance might suffer. Consult this article I wrote on this topic for more details.
Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our discussion forums.
|
