Is there any way to get the number of records selected when I declare a cursor without fetching all those records or doing a select count(*)... ?

AFAIK, it is not possible ..

Under certain circumstances, you may be able to start fetching from the cursor, even before the materialization of the cursor is complete .. Example: Select all rows in table

Cheers
Sathyaram

If you have a cursor, DB2 sometimes tries not to read (materialized in a temp table) the entire cursor until you fetch each specific row. Therefore, DB2 may not know how many rows there are in the cursor until you fetch them all (although sometimes it does, especially if you have an ORDER BY).

In cases where the cursor is not materialized, the rows in the cursor are not locked until fetched, so they could be deleted before you access them (or more rows could be inserted).

I should have taken an IX lock on this thread before sathyaram_s posted.

Ok, thanks guys.

If you are working with DB2 for i-series, you should state that. Everyone should state which OS and DB2 version they are working with, but it is even more important for i-series because it is often different from the other DB2's.