How can I create this report? 
Author Message
 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
 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
 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
 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
 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
 
 [ 5 post ] 

 Relevant Pages 

1. MDX : Canned Report or OLAP

2. Anyone know of some canned (cheap or free) DB performance testing software

3. canned code to get db on web quickly via perl or

4. Cans access2.0 engine access btrieve files?

5. bcp canned app

6. if you will promise Allahdad's swamp against cans, it will angrily depart the unit

7. Switching from inhouse to canned package.

8. Canned PARADOX scripts?

9. Creating report w/ Data Report in VB6

10. Infinite Report - Create Access reports of unlimited complexity

11. Reports, reports, reports, reports.....REPORTS!

12. Running a 'canned' Access Report from VB5


 
Powered by phpBB® Forum Software