Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2010
    Posts
    1

    Unanswered: Number of cursor rows returned??

    Is there a DB2 variable that can be accessed via COBOL program to find the number of rows returned in a cursor?
    Besides physically counting while fetching,

  2. #2
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    i don't know any other ways of doing it, but running a statement "select count(*) ..." with same 'where' clause before your cursor statement.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by jawalker View Post
    Is there a DB2 variable that can be accessed via COBOL program to find the number of rows returned in a cursor?
    Besides physically counting while fetching,
    Obviously, the number of rows returned in a cursor is not known until all of them are processed, so you might just as well count them.

  4. #4
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    I remember using one of the SQLCA variable to get no of rows effected by SQL execution. Just look at this variable SQLERR(3) from this cobol structure.


    01 SQLCA SYNC.
    05 SQLCAID PIC X(8) VALUE "SQLCA ".
    05 SQLCABC PIC S9(9) COMP-5 VALUE 136.
    05 SQLCODE PIC S9(9) COMP-5.
    05 SQLERRM.
    05 SQLERRP PIC X(8).
    05 SQLERRD OCCURS 6 TIMES PIC S9(9) COMP-5.
    05 SQLWARN.
    10 SQLWARN0 PIC X.
    10 SQLWARN1 PIC X.
    10 SQLWARN2 PIC X.
    10 SQLWARN3 PIC X.
    10 SQLWARN4 PIC X.
    10 SQLWARN5 PIC X.
    10 SQLWARN6 PIC X.
    10 SQLWARN7 PIC X.
    10 SQLWARN8 PIC X.
    10 SQLWARN9 PIC X.
    10 SQLWARNA PIC X.
    05 SQLSTATE PIC X(5).

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    "rows effected" only includes inserts, updates, or deletes.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    As Nick said, there is often no way for DB2 to know the number of rows ahead of time. The only option would be to materialize the result and then count it. It does not matter if DB2 does the counting or the application - the query has to be executed twice (which is really bad performance-wise) or you have to count while fetching the rows. So if you do not care about performance, do the SELECT COUNT(*) approach.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by schintala View Post
    I remember using one of the SQLCA variable to get no of rows effected by SQL execution. Just look at this variable SQLERR(3) from this cobol structure.
    Thats right. I've coded in the past:
    Code:
    perform cursor-open.
    perform SQLERR(3) times
       perform cursor-fetch
       perform process-cursor-data
    end-perform.
    perform cursor-close.
    Not the most robust example, but it works

    Concerning the rows-affected: be aware that cascade-deletes are not included.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •