I am using db2 version 5 and I have to set rowcount for a select query.
I have tried 'fetch first' or 'optimize for' clauses and row_count() function but I got error messages for all. I think these are not supported in version 5.
I doubt that "optimize for n rows" is what you want since it has no effect on the results returned. This clause is most commonly used to prevent sequential prefetch when not all rows of a cursor will be fetched.
What you probably want is:
FETCH FIRST n ROWS ONLY
which goes after the ORDER BY clause. But I don't know if it is supported in version 5. You did not mention which DB2 platform you are working with.