Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    34

    Unanswered: DB2 Cursor Count

    Is there any way DB2 can tell you the number of records it is going to fetch when you open the cursor itself. Any field in SQLCA.

    We are running Db2 V10 in Mainframe Z/OS.

    Any help is appreciated.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There is usually no way for DB2 to know that, so it is not in SQLCA. Performance would be terrible if every time a cursor was opened, DB2 had to figure out the number of rows it would later fetch if the program fetched every row. Also, depending on locking and isolation level, the number or rows could change during the actual fetches of the rows.

    So if you want an estimate, you can do a single select without cursor that does select count(*) with same where clause as your cursor. But results could theoretically change unless you use RR isolation level.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Only the estimated number of rows in the SQLERRD(3) field of the SQLCA structure after a PREPARE statement.
    Regards,
    Mark.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by mark.b View Post
    Only the estimated number of rows in the SQLERRD(3) field of the SQLCA structure after a PREPARE statement.
    That's because during SQL statement compilation, it does a "mini-explain" in order for DB2 optimizer to determine the correct access path. This is based on table/index stats and default filter factors of the various operators (=. >, <, between, etc). In some cases, the numbers can be off by a large amount, since the estimate only needs to be accurate enough for DB2 to choose the right access path.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

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
  •