We currently have DB2 v8 and are running it with SAP. Our data archiving department is requesting DB info from our database.


ALL TABLE NAMES:
Allocated Space of each table
Usuable Space of each table
Number rows

Using the db2 command prompt we determined we have 13,284 tables and 22,200 rows but when we are using control center to just view the tables under the database and it just hangs our pc and never returns anything. Is there a report or command line process that we can run to just view all the tables with the information needed above to place in a speadsheet for review?

Any help in regards would be greatly appreciated,

Jason

assuming your statistics are uptodate:

select rtrim(tabschema) ||'.'||rtrim(tabname) as table, card as cardinality
from syscat.tables

will return you tablenames and number of rows.

actual physical tablesize can be calculated additionally:
select rtrim(a.tabschema) ||'.'||rtrim(a.tabname) as table, a.card as cardinality, a.npages * b.pagesize
from syscat.tables a, syscat.tablespaces b
where a.tbspaceid = b.tbspaceid

this result is perfect, if you have one table per tablespace or your data is really good organized.
npages is the number of physical pages where data from a table exist, but on one page can be data from all tables in one tablespace. So a few might be counted double.
you can also calculate the theoretical used space by using something like the total number of rows/ number of rows per page * pagesize.

Usually databases and tablespaces ar backuped, not tables.

you can define your db from a windows db2 client as odbc source
open excel or any windows appl and select : get external data with sql
you can query the odbc resources with regular sql and the output will be present in your exc. sheet

Thanks for the replies I do have another question, I am really struggling with this data collection. I was able to configure the command center and I was able to open up the database to see the tables. It is reading 20,000+ is there a global SQL query I can run on that database if I am not using statistics atm.

I need total pages, used pages and # of rows on each table.


I appreciate any help that can give me assistants.


Sincerely,

Jason