Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: to determine count of records

    Hi all

    Scenario :- The database will for sure return around say 500 records for a cursor query (select * from table)in the program.

    Requirement :- Now we require only the count of the records that will be returned by this query.
    actually say if the query returns more than 200 records (500 actually present), than we have to skip this query execution itself (that is no fetch of any records is required) and alert that there are "too many records".
    avoiding fetch cursor execution can we determine the count of records returned by the "select *" query coded in a cursor

    hope the requirement is clear
    thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can:
    SELECT COUNT(*) FROM table_name WHERE...
    to get the count.

    You can also add the following to the end of the query to limit the rows, but you will not know whether the rows are limited:
    FETCH FIRST 200 ROWS ONLY

    In some cases, DB2 does not know how many rows match the cursor because the rows are not acessed until needed.
    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
    Dec 2003
    Posts
    4

    determine number of rows returned by a cursor

    requirement is to determine number of rows returned by a cursor. this number of rows should be determined before executing the fetch cursor stmt itself.

    in oracle sql , there are attributes for cursors such %rowcount(), which will determine the number of rows retrieved when a open cursor is executed.

    example flow

    declare cursor c1 for select * from table

    open c1
    if c1%rowcount() > 200
    then exit.

    Is some similiar attributes present like this in DB2.

    thanks for your reply also.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No, that is not available on DB2. The reason is that sometimes DB2 does not know how many rows are in the cursor when it is opened because DB2 only accesses the needed rows when fetched by the program one at a time. This is a performance feature.
    Last edited by Marcus_A; 02-12-04 at 10:08.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: determine number of rows returned by a cursor

    Originally posted by lakshmi arvind
    requirement is to determine number of rows returned by a cursor. this number of rows should be determined before executing the fetch cursor stmt itself.

    in oracle sql , there are attributes for cursors such %rowcount(), which will determine the number of rows retrieved when a open cursor is executed.

    example flow

    declare cursor c1 for select * from table

    open c1
    if c1%rowcount() > 200
    then exit.

    Is some similiar attributes present like this in DB2.

    thanks for your reply also.
    First of all, I don't see what is wrong with executing FETCH. I believe it's OPEN that takes most time, not FETCH, so if you intend to save time/resources then I don't think avoiding the FETCH will help you a lot.

    However, if your database has relatively current statistics AND your query does not contain any WHERE conditions or groupings then you can use

    SELECT CARD FROM SYSCAT.TABLES WHERE TABNAME='table_name'

    to determine the total number of rows in the table.

Posting Permissions

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