db2查看表大小

可以通过查看一个表有多少个页的方式，然后乘以页大小就可以算出表所占用空间：

$ db2 connect to tkdwt
Database Connection Information
Database server = DB2/AIX64 9.7.4
SQL authorization ID = DB2INST1
Local database alias = TKDWT

[@more@]

收集统计信息
$ db2 "RUNSTATS ON TABLE DB2INST1.ETL_TEST10 ON ALL COLUMNS ALLOW WRITE ACCESS"
DB20000I The RUNSTATS command completed successfully.
16530
db2 "RUNSTATS ON TABLE DB2INST1.ETL_TEST10_1_20120824 ON ALL COLUMNS ALLOW WRITE ACCESS"
db2 "RUNSTATS ON TABLE DB2INST1.ETL_TEST100_1_20120824 ON ALL COLUMNS ALLOW WRITE ACCESS"
db2 "RUNSTATS ON TABLE DB2INST1.ETL_TEST1000_1_20120824 ON ALL COLUMNS ALLOW WRITE ACCESS"
db2 "RUNSTATS ON TABLE DB2INST1.ETL_TEST5000_1_20120824 ON ALL COLUMNS ALLOW WRITE ACCESS"
db2 "RUNSTATS ON TABLE DB2INST1.ETL_TEST10000_1_20120824 ON ALL COLUMNS ALLOW WRITE ACCESS"

查出表所占用页数和表所在的表空间：
语句如下：
SELECT npages,tbspace from syscat.tables where TABNAME='ETL_TEST1000_1_20120824'
16530 USERSPACE1

ETL_TEST10_1_20120824 166
ETL_TEST100_1_20120824 1653
ETL_TEST1000_1_20120824 16530
ETL_TEST5000_1_20120824 82650
ETL_TEST10000_1_20120824 165300

查看表空间的页大小
有两种方法：
1是通过命令查看：
SELECT pagesize/1024||'K' from syscat.tablespaces where tbspace='USERSPACE1'
2是登陆服务器查看
Db2 “LIST TABLESPACES SHOW DETAIL”
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 662528
Useable pages = 662496
Used pages = 582880
Free pages = 79616
High water mark (pages) = 582880
Page size (bytes) = 32768
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
当中的pagesize就是页大小，单位是B，但是表空间必须使用同样大小的缓冲区来创建
所以使用页大小然后乘以页数就是表的大小
UN
ETL_TEST10_1_20120824 166*32=5.18M
ETL_TEST100_1_20120824 1653*32=51.66M
ETL_TEST1000_1_20120824 16530*32=516.52M
ETL_TEST5000_1_20120824 82650*32=2.52G
ETL_TEST10000_1_20120824 165300*32=5.04G

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7490392/viewspace-1059431/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7490392/viewspace-1059431/