I am running DB2 V9.5 on AIX. We have a fairly small database (2
million records max on main table). We use COUNT(*) periodically. We
also schedule runstats and online reorg periodically. At times
COUNT(*) can be very slow and although a reorg/runstats speeds it up
we can have a window when the count is needed but reorg/runstats has
not yet run. Also, I cannot run reorg/runstats at the time a count is
requested since it would take longer to do it than it would be to just
wait for the count to return.

Any suggestions on what else can be used to get the count of a table?
The cardinality isnt accurante until a runstats is done so that is not
useful. Is there a quick "rowcount" type way to get this info? I
just need a speedy way to get number of records.

Thanks in advanced.

How long time is allowed to get number of 2 million records max on
main table?

On 15 Feb, 21:44, shorti <lbrya... (AT) juno (DOT) com> wrote:
If you want exact numbers I don't think theres much you can do ( I
assume mqt's is out of the question for this ). However, if you accept
approximative numbers you can use table samples as in

select 100*count(*) from T tablesample system(1)

There is also a Bernoulli sample which is more accurate, but slower.
See:

http://publib.boulder.ibm.com/infoce...74%65%6d%22%20

/Lennart

On Feb 15, 5:24*pm, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:
Yes we have indexes. If we have a somewhat fragmented database it can
take 45 secondsto get the count. After the reorg/runstats it takes
only a second or two.

On 2/16/10 9:03 AM, shorti wrote:
What is causing the fragmentation? Do you insert and delete lots of
rows in this table, or are you getting lots of overflow rows?
Because clearly you need to fix (or mitigate) what is causing this.

How frequently are you having to do the reorg?

getting high.
I don't think that scanning fragmented table/index take more than 10
times slower than reorged table/index.

After you took 45 seconds to get the count, if you repeat the count,
you may get the count in shorter time.

"Lennart" <erik.lennart.jonsson (AT) gmail (DOT) com> 写入消息
news:04c69e0c-619d-4f9a-a5a0-4dae105a9f80 (AT) b7g2000yqd (DOT) googlegroups.com...
No silver bullet for exact numbers.

for approximate size of tables, check output of db2pd -tcbstat.

On 2/17/10 7:29 AM, Tonkuma wrote:
Why not? If FPAGES > NPAGES * 10 (or 30 or 45) it's certainly possible.

The OP should do a runstats when they're getting the problem and post
the output of REORGCHK for that table.