Find the number of records fetched in a cursor.

[Suntles Singh]

Hi

While working with cursors, I need to know the number of records selected by a cursor. I want to know this before processing the first row is fetched by cursor. Can we get this information from a variable of SQLCA? I don't know the answer to it. Need help.

[zprogrammer]

If you could elaborate you could get better reply

I would normally do a Count(*)

[enrico-sorichetti]

IIRC the SQLERRD(3) variable in the SQLCA should contain the value You are looking for

https://www-01.ibm.com/support/knowledg ... ields.html

[Chandan Yadav]

I doubt SQLERRD(3) will give the count no of rows selected by a cursor before hand.

As far as selecting rows concerned, SQLERRD(3) (3) gives the count of rows selected in a multi-fetch/rowset Oriented cursor for a single fetch operation.

I don't think there is any field in SQLCA which will give the total no rows selected by a cursor in advance

Correct me if I am wrong

Thanks and regards,
Chandan

[Suntles Singh]

Thank for the answers. So basically, it is not possible actually?

[nicc]

Pandora-Box's reply still applies - do a COUNT using your selection criteria before doing your SELECT.

[Suntles Singh]

Ok. But I still not sure why it is not possible.

[zprogrammer]

Suntles,

You could do a count(*) and find the number of records and then process the cursor.

[Anuj Dhawan]

While working with cursors, I need to know the number of records selected by a cursor. I want to know this before processing the first row is fetched by cursor. Can we get this information from a variable of SQLCA? I don't know the answer to it. Need help.

Let's take a step back and understand what is the life-cycle of a CURSOR in a COBOL DB2 program. There are four stages -

• DECLARE
  OPEN
  FETCH
  CLOSE

... the cursor.

The cursor won't even have a clue about the rows in the table until you FETCH and by then two of the stages of its life-cycle are already passed! Which means you are expecting something before it happened. It's just not possible, AFAIK, in DB2-on-zOS[sup]![/sup]. Cursors do not store any rows they are a pointer to a compiled SQL statement that can be used to fetch the rows and until you FETCH, well, the count is zero. For example, you can have a CURSOR like this:

Code: Select all

SELECT column1, column2
    FROM sometablename
   WHERE some_condition;

And can write a separate SQL with the same WHERE clause to get the count of rows, as PB and nicc has said:

Code: Select all

 SELECT count(*)
    INTO ctr_variable
    FROM sometablename
   WHERE some_condition;

[sup]![/sup]: In oracle[sup]*[/sup], you can use %ROWCOUNT like this:

Code: Select all

DECLARE
  CURSOR some_cursor IS
  SELECT *
    FROM SomeTable;
BEGIN
  OPEN some_cursor;
  DBMS_OUTPUT.PUT_LINE(some_cursor%ROWCOUNT);
  CLOSE some_cursor;
END;

But it'll always yield 0 for count, because no FETCH is done yet.[/size]

[sup]*[/sup]Have taken reference from oracle documents and online references.

[Suntles Singh]

While working with cursors, I need to know the number of records selected by a cursor. I want to know this before processing the first row is fetched by cursor. Can we get this information from a variable of SQLCA? I don't know the answer to it. Need help.

Let's take a step back and understand what is the life-cycle of a CURSOR in a COBOL DB2 program. There are four stages -

• DECLARE
  OPEN
  FETCH
  CLOSE

... the cursor.

The cursor won't even have a clue about the rows in the table until you FETCH and by then two of the stages of its life-cycle are already passed! Which means you are expecting something before it happened. It's just not possible, AFAIK, in DB2-on-zOS[sup]![/sup]. Cursors do not store any rows they are a pointer to a compiled SQL statement that can be used to fetch the rows and until you FETCH, well, the count is zero. For example, you can have a CURSOR like this:

Code: Select all

SELECT column1, column2
    FROM sometablename
   WHERE some_condition;

And can write a separate SQL with the same WHERE clause to get the count of rows, as PB and nicc has said:

Code: Select all

 SELECT count(*)
    INTO ctr_variable
    FROM sometablename
   WHERE some_condition;

[sup]![/sup]: In oracle[sup]*[/sup], you can use %ROWCOUNT like this:

Code: Select all

DECLARE
  CURSOR some_cursor IS
  SELECT *
    FROM SomeTable;
BEGIN
  OPEN some_cursor;
  DBMS_OUTPUT.PUT_LINE(some_cursor%ROWCOUNT);
  CLOSE some_cursor;
END;

But it'll always yield 0 for count, because no FETCH is done yet.[/size]

[sup]*[/sup]Have taken reference from oracle documents and online references.

Thanks Anuj! It's a lengthy explain. But I am able to get what you all have said.. need to read it multiple times.