Is it a single db partition or multiple db partitions db server?
If it is EE, you can turn on INTRA_PARALLELISM.
Regards,
FRX

allenj (AT) ndr (DOT) com wrote:

On Thu, 21 Aug 2003 21:45:53 +0000, allen wrote:

Allen,

I'm not surprised it's taking some time : you're having to read through
the whole table to return a count of all the rows. All you can do is try
to make the count go as fast as possible by throwing as much hardware and
parallelism at it as possible.

I assume you're not considering such as statement for production ? If you
were then maybe time to reconsider !!!

If so, do you really need the count 100% accurate. If not, then pick up
the ballpark figure from the catalog (CARDF on SYSIBM.SYSTABLES I think).
This of course assumes that you've run RUNSTATS recently.

HTH

Phil

I don't think a table scan is needed. It's suffient to scan any index
(ideally the smallest)

Cheers
Serge

On Fri, 22 Aug 2003 12:54:39 GMT, Philip Nelson <teamdba (AT) scotdb (DOT) com>
wrote:

Shouldn't the "select count(*)" pick up the ballpark figure from the
catalog, rather than literally counting rows?

I'm doing count(*) queries over multi-million rows tables with response
times of a few seconds. For 100M rows, I'd expect around 1-2 minutes on our
system (W2K AS, 4x500MHz Xeon, 2.75GB RAM). There's no I/O involved.

You should examine DB2 I/O (use the performance monitoring tools). If it's
doing I/O to count the rows, that's the problem. Add RAM and increase the
buffer pool until it stops doing I/O (ours is around 1GB). Also enable
multi-page prefretching, add I/O agents, etc, to speed up the initial I/O.

If that doesn't help, you can always migrate from Linux to Windows 2000 /
2003 AS or DC - it has a better I/O model. (Now you can all flame me!)

<allenj (AT) ndr (DOT) com> wrote

Only if you do db2set DB2_THUMB_RULE = YES;
*tongue firmly in cheek*

Cheers
Serge

agreed - you could verify this with explain

On Fri, 22 Aug 2003 13:53:26 -0400, Serge Rielau wrote:

Would it not be possible to do something like this with the dynamic
statistics in DB2 for z/OS V8 ?

Just a thought.

count(*) is, if possible, modified by the optimizer with a column index.
(index scan)

SQL Statement:

select count(*)
from dboxs.lineitem
for read only


Section Code Page = 1252

Estimated Cost = 57986,378906
Estimated Cardinality = 1,000000

Access Table Name = DBOXS.LINEITEM ID = 6,11
End of section


create index
reorg
runstats
explain

SQL Statement:

select count(*)
from dboxs.lineitem
for read only


Section Code Page = 1252

Estimated Cost = 38675,507813
Estimated Cardinality = 1,000000

Access Table Name = DBOXS.LINEITEM ID = 6,11
End of section



PM

<allenj (AT) ndr (DOT) com> wrote

You could use an summary table to hold the total count (or some partitioned count)


Otherwise, in V8 this will give you some estimate of the number of rows

db2 select count(*) * 100 from <table> tablesample system(1)



Regards
Paul Vernon
Business Intelligence, IBM Global Services