To get the number of rows affected by a cursor open

[gkarun]

Hi,

I have a cursor opened by a PREPARE statement. I need the total number of rows whcih will be fetched by that cursor. Now I am taking the total number by a COUNT(*) of that dynamic SQL. As COUNT(*) affects the performance, I would like to get the same along with the PREPARE statement.

Will any value in the SQLERRD table gives the total number fetched ? If so please specify how it can be done.

Thanks
Arun

[frozenmist]

Hi Arun,
If you want to know when to stop fetching,then you can use this method also.
you can declare a continue handler for not found and assign a flag some value.
After fetching check the flag value and then give subsequent statements.
eg:

declare flag integer;
declare continue handler for not found
begin
set flag= -1;
end;
......
set flag=0;
<open your cursor>
while(flag=0)
fetch <cursor> into <variables>;
if (flag=0) then <your statements> end if;
end while;
<close your cursor>
 

When no more rows are there NOT FOUND exception will be raised. As it is a continue handler, it will continue execution from next line.
So until flag value is -1 the fetching continues.

Hope you got it..
Cheers

[gkarun]

Hi frozenmist,

That will not solve my problem. I want to know whether any register value or so is there to store the total number of matching rows for a SELECT query.
Suppose my SELECT query select 100 rows from a 1000 row table, is there any system managed code (like SQLCODE or SQLERRD) contains the value 100 there.
I referred the following link.
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/db2/rbafzmstfielddescsqlca.htm

But SQLERRD(3) is not giving me the expected value when I displayed after the PREPARE Statement which prepares my SELECT query.
Do you have any suggestion on it ?

Thanks
Arun