linux查看db2表空间大小,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