tables in dbspaces, some statistics

Based on recent discussion here of tables and dbspaces, I came up with the following queries which might be of use to other folks as well as to me: select trunc(partnum/16777216) dbspace, count(*) tables, sum(nrows) tot_rows, sum(nrows*rowsize) bytes from systables where tabtype = 'T' group by 1 order by 1; If you add the 'dbspaces' table to your database and load it with dbspace names taken from tbstat -D output as I have done, then you can use: select dbs_name[1,12] dbspace, count(*) tables, sum(nrows) tot_rows, sum(nrows*rowsize) bytes from systables, dbspaces where tabtype = 'T' and dbs_no = trunc(partnum/16777216) group by 1 order by 1; Sample output: dbspace tables tot_rows bytes mcs_aaaaa 28 51 3715 mcs_catalog 22 2695 114810 mcs_eeeee 25 224 45446 mcs_fffff 32 1412 201445 mcs_mmmmm 35 165 262599 mcs_wwwww 28 449 79385 ("bytes" is data bytes, and does not include indexes and other overhead.) I separated the mcs system catalog files from the data tables by doing: create database mcs in mcs_catalog; I created the data tables in the other five dbspaces by doing: create table whatever ( ... ) in mcs_xxxxx; Other than the trick to get the dbspace number (thanks to Joe Glidden and others), this is all pretty straight-forward stuff. However, I hope my posting it may save someone some time. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\