DB2表空间与缓冲池 博客分类: ibm重要生活 db2IBMDB2
程序员文章站
2024-03-23 19:34:10
...
原则:
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
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