DB2 tablespace and Buffer Pool

DB2 tablespace and buffer pool principles: 1. the space size settings of the intermediate table and target fact table are based on the space settings of the source table. (Calculate the data volume of the last year) 2. The data volume of the intermediate table and target fact table is planned to be 5 years. 3. According to the previous requirements, the document will increase the data volume by 20% per year/* the default IBMDEFAULTGROUP db2 create database partition group pg01 on dbpartitionnum (0 to 1) of the current application) db2 list database partition groups */-- create bufferpool "BP32K" SIZE 81920 PAGESIZE 32768; -- create regular tablespace "TBS_IDX_32K" in database partition group ibmdefaultgroup pagesize 32 k managed by database using (file 'd: \ rep \ vgidx01 \ rTBS_IDX_32K '64000) -- the original version is 64000. According to the previous requirements, the document will add 20% EXTENTSIZE 32 PREFETCHSIZE 192 BUFFERPOOL BP32K OVERHEAD 12.670000 TRANSFERRATE 0.180000 file system caching dropped table recovery on; -- Note: the page size of the tablespace is different from the page size of the specified buffer pool, an error will also be reported -- create regular tablespace "TBS_DATA03" in database partition group ibmdefaultgroup pagesize 32 k managed by database using (file 'd: \ vgdata01 \ rTBS_DATA03 '19200) -- the original version is 19200. According to the previous requirements, the document will add 20% EXTENTSIZE 32 PREFETCHSIZE 192 BUFFERPOOL BP32K OVERHEAD 12.670000 TRANSFERRATE 0.180000 file system caching dropped table recovery on; -- create a TABLE, create table "DB2ADMIN" on the tablespace and index in the index space ". "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 -- buffer pool attribute list tablespaces -- LIST the space of the table TO which the tablespace is changed: # 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 "specified oolspace" 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;