1. 連線資料庫
db2 "CONNECT TO USER USING "
2. 建立緩衝池 (三種頁面)
db2 "CREATE BUFFERPOOL IBMDEFAULT8K IMMEDIATE SIZE 5000 PAGESIZE 8K"
db2 "CREATE BUFFERPOOL IBMDEFAULT16K IMMEDIATE SIZE 5000 PAGESIZE 16K"
db2 "CREATE BUFFERPOOL IBMDEFAULT32K IMMEDIATE SIZE 5000 PAGESIZE 32K"
3. 建立 SMS 正規表空間 (三種頁面)
db2 "CREATE TABLESPACE EXOATBS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8K MANAGED BY SYSTEM USING ('/home/exoa2/exoacontainer') EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT8K OVERHEAD 24.10 TRANSFERRATE 0.90 DROPPED TABLE RECOVERY OFF"
db2 "GRANT USE OF TABLESPACE EXOATBS TO PUBLIC"
-- 16K/32K 同理，修改 PAGESIZE、容器路徑、BUFFERPOOL
4. 建立系統臨時表空間 (三種頁面)
db2 "CREATE TEMPORARY TABLESPACE EXOASYSTMP IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 8K MANAGED BY SYSTEM USING ('/home/exoa2/exoasystmp') EXTENTSIZE 32 PREFETCHSIZE 16 BUFFERPOOL IBMDEFAULT8K OVERHEAD 24.10 TRANSFERRATE 0.90 DROPPED TABLE RECOVERY OFF"
-- 16K/32K 同理
5. 關鍵參數說明
| 參數 | 建議值/說明 |
|---|
| EXTENTSIZE | 32 頁 (1MB@32K)，條帶寬度 |
| PREFETCHSIZE | 16 頁，預取深度，通常 = EXTENTSIZE × 容器數 |
| OVERHEAD | 磁碟尋道+延遲 (ms)，SSD 約 0.1，HDD 約 10-20 |
| TRANSFERRATE | 傳輸速率 (ms/頁)，SSD 約 0.01，HDD 約 0.5-1 |
| DROPPED TABLE RECOVERY | OFF 節省空間，除非需 DROP TABLE 復原 |
6. AIX 環境單行指令批次執行
db2 -tvf create_tablespaces.sql
