when we run fetch from the application program (C++) we get 18 recs, if I run an sql statement I get 200 records. From the top of your head what could be the problem.

We get no error messages. I've done reorg and runstats on all tables and indexes used in the query and it did not make any difference. db2diag did not detect any problems on the database at the time when program was run.

Thanks

We fixed it. I forgot abt rebind after all reorgs and runstats. So we did re-bind and it got fixed. Still have no idea what do all this have to do with the results of the query. I thought it would affect only perfomance, not the result.

I don't know what exactly you are doing and where you got your numbers from, but I could imagine that one number shows how many records you fetched (which is correct) and the other is an estimate of how many rows DB2 believes it will return for the query. The latter number is just an estimate and, thus, by no means required to be correct.

If your application relies on a correct count of rows before fetching the rows, you may have a design problem. Because: counting all rows before fetching executes the query twice, which is (a) slow, and (b) not guaranteed to be correct either, except on isolation level RR, which reduces concurrency.

p.s: It would be helpful to know which DB2 version you are using on which platform.

p.s: It would be helpful to know which DB2 version you are using on which platform.

Knut, it's in my signature after my nick

Well, I de-selected "Show Signature" in the profile, which is why I don't see those details. ;-)

sorry, Knut, it's
DB2 v8.2 ESE (FP10,64bits) on UNIX/AIX v5.3

That leaves the question what the 2 ways are that you use to get the row count.