Number of records fetched in a cursor

db2 sql interview questions

Moderator: Moderator Group

Post Reply
Posts: 1
Joined: Sat Apr 28, 2012 11:57 pm

Number of records fetched in a cursor

Post by Jagadesh_s » Sun Apr 29, 2012 12:23 am

Hi friends,

I need to know the number of records selected by a cursor before processing the first record fecthed by cursor. Does any variable of SQLCA will store the number of records by the cursor ?. Any other possible way apart from using select count(*) with the cursor where clause condition ?

User avatar
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Sun Apr 29, 2012 2:04 am

first of all, db2 has rows and columns. files have records and fields.

secondly, your terminology needs to improved.

what you want to know is:
is there a way to determine the size of a result set other than count(*)?


the sqlerrcd3 will provide you with the number of rows involved with an
for each invocation of SQL
if you have 3 rows in the result set and you do single FETCHs, each FETCH
(if sqlcode = +000)
will return +1 in sqlerrcd3.
by the way, sqlerrcd3 functions for both cursors and singleton selects.

i would question why one would need the count of the result set
prior to actually FETCHing rows.
sounds like poor/inexperienced programming

i am moving this thread to INTERVIEW QUESTIONS as the topic fits there better.

db2 application programming forum is for db2 problems for more advanced topics
than posed by beginners/INTERVIEW Questions.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Sun Apr 29, 2012 2:11 am

GET_DIAGNOSTICS, found here, here and here
has a ROW_COUNT parameter that returns
the number of rows affected by the last SQL statement,
which may be helpful.
Have a good one

Posts: 2
Joined: Tue Mar 03, 2015 1:22 pm

Post by Smolinedex » Tue Mar 03, 2015 1:48 pm

Have you read anything like this again, but it should be better.

Post Reply


Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Mainframe Interview questions

Other References
Mainframe Tools and others