-- db2 -txf get_all_tables_name.sql > all_tables_name.lst -- select substr(tabschema,1,8) || ' ' || substr(tabname,1,24) from SYSCAT.TABLES where tabschema not in ('SYSIBM','ASN','SYSTOOLS') and type='T' order by tabschema;
TIP:
已排除系統相關的 Tables。
SELECT char(date(t.stats_time))||' '||char(time(t.stats_time)) as statstime , substr(t.tabschema,1,8)||'.'||substr(t.tabname,1,24) as tabname , card as rows_per_table , decimal(float(t.npages)/ ( 1024 / (b.pagesize/1024)),9,2) as used_mb , decimal(float(t.fpages)/ ( 1024 / (b.pagesize/1024)),9,2) as allocated_mb FROM syscat.tables t , syscat.tablespaces b WHERE t.tbspace=b.tbspace AND t.tabschema NOT IN ('SYSIBM','SYSTOOLS','ASN') WITH ur;
TIP:
Table 必須先經過 runstats 後,統計後的數值才會正確。
已排除系統用的 Table。
CREATE REGULAR TABLESPACE RPTTBS9 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY DATABASE USING (FILE '/home/STORAGE/devrpt/DEVRPTDB/rpttbs9-c1' 1024 M, FILE '/home/STORAGE/devrpt/DEVRPTDB/rpttbs9-c2' 1024 M) AUTORESIZE YES MAXSIZE 51200 M EXTENTSIZE 32 PREFETCHSIZE AUTOMATIC BUFFERPOOL RPT8KBP1 OVERHEAD 12.670000 TRANSFERRATE 0.180000 FILE SYSTEM CACHING DROPPED TABLE RECOVERY ON;
TIP:
AUTORESIZE YES MAXSIZE 51200 M ,每個 data file 可以自動擴充至 50GB