Hello,

Operating System: AIX v4.3.3.0
DB2: v7.1.0 02010105

I support a legacy application that uses the Call Level Interface library to insert/update/delete/select data from database tables. In one part of the application it executes a SELECT statement to open a cursor and then executes a COUNT(*) to determine the number of rows in the result set. To me this is double jeopardy and it is a big performance hit for me. Is there a way other than COUNT(*) to determine the number of rows in a result set?

I've searched the forum and didn't find anything helpful.

Thank you very much...

No, there is no other way.

Exactly. DB2 can not know exactly how many rows a query will return. Even if you do a COUNT(*) first, this result is out-dated right away unless you use the very restrictive REPEATABLE READ/SERIALIZABLE isolation level.

Usually, an application that needs the exact number of rows has a design problem. You should just run your query and fetch rows until you get SQLCODE +100.

It is not unusual for an application to show how many web pages (or items) the user can browse through before they actually get to the end, and it usually does not have to be exact.

One thing to remember is that at least when you start paging through the items for real, the smallest index is probably still in the bufferpool.

That's the point: the numbers are just estimates. I think we are fully in agreement here.

Thank you all for your replies. You pretty much confirmed what I had already suspected. It's just that I thought that if you have an order by clause then DB2 should know how many rows are in the result set, shouldn't it? Or am missing something?

DB2 does not always have to read all the rows and sort them to satisfy an order by. It is sometimes possible to use index access in lieu of a sort and only read the number of rows fetched. Indexes are always in exact order and if that is the order you requested, then DB2 may not have to materialize the answer set to fetch the first n rows.

It is true that when an answer set needs to be materialized in order to return the first row (because of a sort) then DB2 would know how many rows are in the answer set. But then you have the problem of getting two different results based on whether an index exists (or was dropped). This completely violates the principles of a relational database.