Get a COUNT(*) for all tables in a schema?

[--CELKO--]

Is there a simple way to get a COUNT(*) for all tables in a schema in
DB2 LUW 9.0?

[ChrisC]

--CELKO-- wrote:

Is there a simple way to get a COUNT(*) for all tables in a schema in
DB2 LUW 9.0?

Well, if the tables are mostly static and you have current statistics
OR your tables are very dynamic and you have sufficiently recent
statistics, selecting from the catalog works great:

select tabname, card from syscat.tables where tabschema =
'<schemaName>'

(noting that for very dynamic table sizes, the exact COUNT(*) isn't
often accurate in any case for long).

[Serge Rielau]

ChrisC wrote:

--CELKO-- wrote:

>Is there a simple way to get a COUNT(*) for all tables in a schema in
DB2 LUW 9.0?

Well, if the tables are mostly static and you have current statistics
OR your tables are very dynamic and you have sufficiently recent
statistics, selecting from the catalog works great:

select tabname, card from syscat.tables where tabschema =
'<schemaName>'

(noting that for very dynamic table sizes, the exact COUNT(*) isn't
often accurate in any case for long).

.... otherwise you need to scribble up a stored proc with dynamic SQL
inside..

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Lennart]

On Apr 30, 6:41 pm, --CELKO-- <[e-mail]:

Is there a simple way to get a COUNT(*) for all tables in a schema in
DB2 LUW 9.0?

Depends on what you mean by simple, but it is pretty straightforward
to do it from a shell.

[lelle@53dbd181 Documents]$ for t in `db2 -x "select tabname from
syscat.tables where tabschema = 'LELLE' and type = 'T'"`; do db2
"select '$t', count(1) from lelle.$t"; done

1 2
-- -----------
T1 196608

1 record(s) selected.
1 2
---------------- -----------
EXPLAIN_INSTANCE 10

1 record(s) selected.
1 2
----------------- -----------
EXPLAIN_STATEMENT 20

1 record(s) selected.

[...]

/Lennart