SEO

#1 (**permalink**) 05-02-2008, 05:04 AM

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

#2 (**permalink**) 05-02-2008, 05:04 AM

--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).

#3 (**permalink**) 05-02-2008, 05:04 AM

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

#4 (**permalink**) 05-02-2008, 05:04 AM

On Apr 30, 6:41 pm, --CELKO-- <jcelko...@earthlink.net> wrote:
> 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

#5 (**permalink**) 05-02-2008, 05:04 AM

On Apr 30, 3:05*pm, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
> On Apr 30, 6:41 pm, --CELKO-- <jcelko...@earthlink.net> wrote:
>
> > 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

#!/bin/sh

INSTANCE=$1
DB=$2

if [ -f /home/$INSTANCE/sqllib/db2profile ]; then
. /home/$INSTANCE/sqllib/db2profile
fi

db2 -t -x -n +p<<DB2>COUNTALL.tmp 2>&1

connect to $DB;

select distinct 'connect to $DB ;' from sysibm.sysversions ;

select distinct substr('select count(*) from '||rtrim(tabschema)||'.'||
rtrim(tabname)||' ;',1,80) from syscat.tables Where type = 'T' and
not tabschema like'SYS%' order by 1 asc ;

DB2

db2 -tvf COUNTALL.tmp

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

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

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