Difference in record counts

[lifzgud]

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

[docdiesel]

Hi,

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

select count(*) from A where TXN_DATE>'20/06/2007'

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

select count(*) from A where TXN_DATE is NULL
select count(*) from A where TXN_DATE>'1900-01-01'

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. ("Commandmen t #11: Thou shalt never use count(*)." :-)

Regards


Doc

[lifzgud]

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

[vijay2082]

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

[lifzgud]

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?

[vijay2082]

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/data/library/techarticle/0208kuznetsov/0208kuznetsov.html

Cheers, Vijay