In DB2 version 7 it became possible to limit the number of rows returned by a SQL statement. You simply add the phrase FETCH FIRST n ROWS ONLY to your SQL SELECT statement. If you only want to fetch one row you may write FETCH FIRST ROW ONLY.

There are a number of good things to say about this new possibility. One of them is that DB2 stops fetching rows as soon as the specified number of rows has been returned. Another is that DB2 tries to find the quickest possible access path to the data. This is especially useful if you want to determine the existance of one or more rows fulfilling certain criterias, but you do not care about the contents of such rows. With a FETCH FIRST ROW ONLY you will get such an answer in the most optimal way performance wise.

If you use a cursor in a program you will always get SQLCODE 100 when you issue FETCH number n + 1 (if n or more rows fulfils your WHERE clause). Be aware that an ORDER BY will sort all rows fulfilling the WHERE clause and not just the first n rows. DB2 will attempt to avoid any sorts if possible by using appropriate indexes.

In DB2 version 7 it is not possible to use ORDER BY combined with a SELECT INTO which is quite annoying, but in DB2 version 8 it is possible. So if you have not moved to DB2 version 8 yet then hurry up. As a final remark SELECT INTO combined with FETCH FIRST ROW ONLY effectively prevents your single row SELECT from returning SQLCODE -811.