469,266 Members | 2,069 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

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

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

Jun 27 '08 #1
3 30068
--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).
Jun 27 '08 #2
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
Jun 27 '08 #3
On Apr 30, 6:41 pm, --CELKO-- <jcelko...@earthlink.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_INSTANCE 10

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

1 record(s) selected.

[...]

/Lennart
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by LRW | last post: by
2 posts views Thread by Alan Zhong | last post: by
3 posts views Thread by Leo | last post: by
3 posts views Thread by Atif | last post: by
7 posts views Thread by Rory Campbell-Lange | last post: by
3 posts views Thread by shsandeep | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.