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

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.

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.

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.

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.