欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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
相关标签: db2 IBM DB2