Difference in record counts

Hi,
I am working on db2 and i have this table A
It has the following columns
TXN_KEY
TXN_DATE

there are some more but they are not required here

When i run a count(*)
db2 "select count(*) from A"

1
-----------
199854786

But suppose i run a query like

db2 "select count(*) from A where TXN_DATE<='20/06/2007'"

35208163

and

db2 "select count(*) from A where TXN_DATE<='20/06/2007'"

164438204

the sm of the last two queries doesnt sum up to the count(*) of the first query

I have run a REORG and a RUNSTATS as well..no solution yet

Please help

re: Difference in record counts

Hi,

you posted the same sql stametent two times. I guess this is copy-and-paste related and the 2nd one should be

The difference is 208419 rows. Is the column TXN_DATE nullable? Try the following:

Based upon the assumption that your table would not contain dates earlier than 1900, the results of these two statements should sum up to 199854786.

Besides, I assume that TXN_KEY is the primary key on your table. Then using COUNT(TXN_KEY) will significantly decrease i/o and therefore the time needed to execute the sql statement. ("Commandment #11: Thou shalt never use count(*)." :-)

Regards


Doc

re: Difference in record counts

yes i did that..
i did a count on the txn_key
i used the txn_key too as a pivot
i.e
i took the record counts <= a particular value and greater than it.
The record counts still fall short of the original value

re: Difference in record counts

Hi,

Can you post the db2level for your env. There is a related bug/APAR with count(*). Will check more once you provide me the info.

also send me a
desribe table <table_name>;


Cheers, Vijay

re: Difference in record counts

Hi ,
Sorry for such a long delay..but the issue was somehow sidelined
When i try to refresh my table i found that it is using a MQT that was created for reporting purposes.
Any MQT that is refresh deferred, but is made available for query optimisation needs to be kept in-line with the underlying data. Otherwise a query that is redirected to the MQT may give inconsistent results against queries that do not get redirected.
I want to switch this redirection off
How is it possible?

re: Difference in record counts

Hi,

Go through the below link and decide what do you need exactly. Depending on your need and business requirement you can modify one or other thing.

http://www.ibm.com/developerworks/da...kuznetsov.html

Cheers, Vijay