How do I limit the number of records in a cursor using DB2 native SQL? I know how to do it in ODBC and JDBC.
The answer to your question is to use the FETCH FIRST n ROWS ONLY clause. This clause, new as of DB2 Version 7, is appended to the end of a SELECT statement to limit the actual number of rows selected to whatever value is supplied for "n". So, for example, to retrieve employee information but to limit the number of rows that can be returned to 5 or fewer, you could code:
SELECT FIRST_NAME, LAST_NAME, EMPNO
FETCH FIRST 5 ROWS ONLY;
Even if there are more than five rows in the EMP table, only five will be returned by this SQL statement. Of course, fewer than five rows can be returned if there are less than five rows in the table.
Dig Deeper on IBM DB2 management
Related Q&A from Craig S. Mullins
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ... Continue Reading
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2. Continue Reading
Craig Mullins explains various definitions of a data mart and sheds some light on the complexity of its relation to a data warehouse. Continue Reading