Problem solve Get help with specific problems with your technologies, process and projects.

DB2 and the fetch-first clause

I'm an applications architect for a large PBM running DB2 version 6.1.2. Is there any way to limit the number of rows returned from a cursor? For example, if I know the result set is a total of 100 rows can I limit the result to 50 in the open rather than say incrementing a counter and stopping the fetch operation?
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 .VO7aaqqaAFk.0@/search390>discussion forums.

Dig Deeper on IBM DB2 management

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.