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]

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

--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]

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

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]

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

On Apr 30, 6:41 pm, --CELKO-- <jcelko...@eart hlink.netwrote:

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_INSTANC E 10

1 record(s) selected.


1 2
----------------- -----------
EXPLAIN_STATEME NT 20

1 record(s) selected.

[...]

/Lennart