DB2表空間與緩沖池 原則： 1、中間表和目標事實表的空間大小設置依據於源始表的空間設置。（計算出最近一年的數據量） 2、中間表和目標事實表的數據量規劃為5年的數據量。 3、根據以前需求文檔會數據量每年遞增加20% /* 現在應用的默認的IBMDEFAULTGROUP db2 create database partition group pg01 on dbpartitionnum(0 to 1) db2 list database partition groups */ --建立緩沖池 CREATE BUFFERPOOL "BP32K" SIZE 81920 PAGESIZE 32768; --建立索引空間 CREATE REGULAR TABLESPACE "TBS_IDX_32K" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32k MANAGED BY DATABASE USING (file 'd:\rep\vgidx01\rTBS_IDX_32K' 64000) --原始是64000，根據以前需求文檔會增加20% EXTENTSIZE 32 PREFETCHSIZE 192 BUFFERPOOL BP32K OVERHEAD 12.670000 TRANSFERRATE 0.180000 FILE SYSTEM CACHING DROPPED TABLE RECOVERY ON; --注意：表空間的page size和所指定的buffer pool的page size大小不一樣，也會報錯 --建立表空間 CREATE REGULAR TABLESPACE "TBS_DATA03" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32k MANAGED BY DATABASE USING (file 'd:\vgdata01\rTBS_DATA03' 19200) --原始是19200，根據以前需求文檔會增加20% EXTENTSIZE 32 PREFETCHSIZE 192 BUFFERPOOL BP32K OVERHEAD 12.670000 TRANSFERRATE 0.180000 FILE SYSTEM CACHING DROPPED TABLE RECOVERY ON; --建立表，將它建在表空間上，索引在索引空間上 CREATE TABLE "DB2ADMIN"."UD8_INCEPT_FILE" ( "S_CREDENCECODE" CHAR(6) NOT NULL , "S_BANKCODE" CHAR(8) NOT NULL ) IN "TBS_DATA03" INDEX IN "TBS_IDX_32K" ; SELECT * FROM SYSCAT.BUFFERPOOLS --緩沖池屬性 LIST TABLESPACES --列出表空間 更改表所屬空間： #QSCOMMAND EXPORT TO '%DATA_PATH%\DB2INST1.M_DIM_BANK.358.1\M_DIM_BANK.ixf' OF IXF SELECT * FROM "DB2INST1"."M_DIM_BANK"; #SYNC 10; DROP TABLE "DB2INST1"."M_DIM_BANK"; #SYNC 20; CREATE TABLE "DB2INST1"."M_DIM_BANK" ("ibankid" BIGINT, "SBANKNAME" VARCHAR(200), "SBANKTYPE" VARCHAR(100) ) DATA CAPTURE NONE IN "SYSTOOLSPACE" INDEX IN "SYSCATSPACE"; #SYNC 30; ALTER TABLE "DB2INST1"."M_DIM_BANK" LOCKSIZE ROW APPEND OFF NOT VOLATILE LOG INDEX BUILD NULL; #SYNC 40; #QSCOMMAND IMPORT FROM '%DATA_PATH%\DB2INST1.M_DIM_BANK.358.1\M_DIM_BANK.ixf' OF IXF MODIFIED BY COMPOUND=5 NOCHECKLENGTHS COMMITCOUNT 1000 --RESTARTCOUNT xxx INSERT INTO "DB2INST1"."M_DIM_BANK"; #SYNC 50; RUNSTATS ON TABLE "DB2INST1"."M_DIM_BANK" AND INDEXES ALL SHRLEVEL REFERENCE; #SYNC 60;
