Prev: using composite keys in an IN clause
Next: AUTHENTICATION=DATA_ENCRYPT
From: shorti on 15 Feb 2010 15:44
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.
From: Tonkuma on 15 Feb 2010 19:24
> ... a reorg/runstats speeds it up ...
Do you have indexes on tables?

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

From: Lennart on 15 Feb 2010 23:29
On 15 Feb, 21:44, shorti <lbrya...(a)juno.com> wrote:
> 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.
>

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/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0010970.html?resultof=%22%74%61%62%6c%65%73%61%6d%70%6c%65%22%20%22%73%79%73%74%65%6d%22%20

/Lennart

From: shorti on 16 Feb 2010 11:03
On Feb 15, 5:24 pm, Tonkuma <tonk...(a)fiberbit.net> wrote:
> > ... a reorg/runstats speeds it up ...
>
> Do you have indexes on tables?
>
> How long time is allowed to get number of 2 million records max on
> main table?

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.
From: Ian on 17 Feb 2010 06:21
On 2/16/10 9:03 AM, shorti wrote:
> On Feb 15, 5:24 pm, Tonkuma<tonk...(a)fiberbit.net> wrote:
>>> ... a reorg/runstats speeds it up ...
>>
>> Do you have indexes on tables?
>>
>> How long time is allowed to get number of 2 million records max on
>> main table?
>
> 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.

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?

 |  Next  |  Last
Pages: 1 2
Prev: using composite keys in an IN clause
Next: AUTHENTICATION=DATA_ENCRYPT