Mainframe, MVS and zOS Discussion

Suntles Singh wrote: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.

• DECLARE
  OPEN
  FETCH
  CLOSE

SELECT column1, column2
    FROM sometablename
   WHERE some_condition;

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

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

Anuj Dhawan wrote:

Suntles Singh wrote: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.