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