DB2, How can I create this report?

gdelamoth
#1 / 5

How can I create this report?

I have been asked to produce some SQL to produce a report which shows the
name of each table on the database and the number of rows on them so it
should look something like:-

Tablename No of Rows
xxx.aaa_bbb 13
xxx.aaa_ccc 15
etc.

Is there any way that this can be done? I can get the table names from
SYSIBM.SYSTABLES but can't think of way of then putting them into a query to
do a COUNT(*) each table in turn.

Thanks for any help anyone can give.

Gordon.

Tue, 10 Aug 2004 04:56:01 GMT

Spark
#2 / 5

How can I create this report?

If you perform runstats on a regular basis then the CARD column on
syscat.tables will have the information you're looking for. No count(*)
would be necessary.

Quote:

> I have been asked to produce some SQL to produce a report which shows the
> name of each table on the database and the number of rows on them so it
> should look something like:-

> Tablename No of Rows
> xxx.aaa_bbb 13
> xxx.aaa_ccc 15
> etc.

> Is there any way that this can be done? I can get the table names from
> SYSIBM.SYSTABLES but can't think of way of then putting them into a query
to
> do a COUNT(*) each table in turn.

> Thanks for any help anyone can give.

> Gordon.

Tue, 10 Aug 2004 06:08:23 GMT

Serge Riela
#3 / 5

How can I create this report?

Hi,

The easiest way would be to write an SQL Procedure with dynamic SQL in its body.

1) Decalare a global temp table
2. Open a FOR loop over SYSCAT.TABLES.
2.a Glue together an INSERT INTO temp SELECT count(*) FROM <tablename>
2.b prepare and execute it.
3 Open a cursor with return on teh temp table.

Cheers
Serge
---
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada

Tue, 10 Aug 2004 06:43:16 GMT

Allan Week
#4 / 5

How can I create this report?

Take a look at the reorgchk utility. The report created from this
utility will show the cardinality of the table. I run this utility
regularly to check if I need to do any reorgs and to update
statistics(runstats).

Quote:

>I have been asked to produce some SQL to produce a report which shows the
>name of each table on the database and the number of rows on them so it
>should look something like:-

>Tablename No of Rows
>xxx.aaa_bbb 13
>xxx.aaa_ccc 15
>etc.

>Is there any way that this can be done? I can get the table names from
>SYSIBM.SYSTABLES but can't think of way of then putting them into a query to
>do a COUNT(*) each table in turn.

>Thanks for any help anyone can give.

>Gordon.

Tue, 10 Aug 2004 08:35:43 GMT

Allan Week
#5 / 5

How can I create this report?

Take a look at the reorgchk utility. The report created from this
utility will show the cardinality of the table. I run this utility
regularly to check if I need to do any reorgs and to update
statistics(runstats).

Quote:

>I have been asked to produce some SQL to produce a report which shows the
>name of each table on the database and the number of rows on them so it
>should look something like:-

>Tablename No of Rows
>xxx.aaa_bbb 13
>xxx.aaa_ccc 15
>etc.

>Is there any way that this can be done? I can get the table names from
>SYSIBM.SYSTABLES but can't think of way of then putting them into a query to
>do a COUNT(*) each table in turn.

>Thanks for any help anyone can give.

>Gordon.

Tue, 10 Aug 2004 08:36:05 GMT