DB2表空间与缓冲池

[size=medium]原则：
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;





http://www.ibm.com/developerworks/cn/data/library/techarticles/0212wieser/0212wieser.html[/size]