DB2 DW 테이블스페이스 생성 (Manual)

Introduction

The DB2 Data Warehouse setup happens in multiple steps. All these steps are run part of the setup process happening during the JTS setup wizard or the manual invocation of:

repotools-jts -createWarehouse

https://jazz.net/wiki/bin/view/Main/MoreControlOverTheDB2DataWarehouseSetup

 

Step 1: Tablespace creation

This step will create the various tablespaces that will be used by the data warehouse. The location of the tablespace files is up to the user but the names cannot be changed.

CREATE BUFFERPOOL BPTEMP32K  SIZE 4000 PAGESIZE 32768;
CREATE BUFFERPOOL BP_32K  SIZE 8000 PAGESIZE 32768;

CREATE TEMPORARY TABLESPACE TEMPSPACE32K IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 32768 MANAGED BY SYSTEM 
USING ('C:\jazzdw\tempspace32k') BUFFERPOOL BPTEMP32K;

CREATE REGULAR TABLESPACE VNF_IDX IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY DATABASE 
USING (FILE 'C:\jazzdw\vnf_idx.1' 5600) BUFFERPOOL BP_32K AUTORESIZE YES INCREASESIZE 50 M MAXSIZE NONE;

CREATE REGULAR TABLESPACE VNF_32K IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY DATABASE 
USING (FILE 'C:\jazzdw\vnf_32k.1' 12000) BUFFERPOOL BP_32K AUTORESIZE YES INCREASESIZE 50 M MAXSIZE NONE;

CREATE REGULAR TABLESPACE VSTR_IDX IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY DATABASE 
USING (FILE 'C:\jazzdw\vstr_idx.1' 4000) BUFFERPOOL BP_32K AUTORESIZE YES INCREASESIZE 50 M MAXSIZE NONE;

CREATE REGULAR TABLESPACE VSTR_32K IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY DATABASE 
USING (FILE 'C:\jazzdw\vstr_32k.1' 7200) BUFFERPOOL BP_32K AUTORESIZE YES INCREASESIZE 50 M MAXSIZE NONE;

This step is implemented by the createDB/bufferpool.sql and createDB/tbspace.sql scripts.

Step 2: Schema, Table and View creation

This step will create all the tables, views, indices, triggers and constraints needed by the data warehouse. The RPTUSER user will be granted select privileges to the data warehouse views.
Here is sample SQL from this step:

CREATE TABLE ETL_INFO
(ETL_ID VARCHAR(36) NOT NULL,
   START_DATETIME TIMESTAMP DEFAULT CURRENT TIMESTAMP NOT NULL ,
   END_DATETIME TIMESTAMP,
   STATUS INTEGER,
   DATASOURCE_ID VARCHAR(36) NOT NULL,
   LOAD_TYPE CHAR(1) DEFAULT 'D' NOT NULL)
IN VNF_32K INDEX IN VNF_IDX;

.
.
.
GRANT SELECT ON RICALM.VW_RQST_HISTORY TO USER RPTUSER;

This step is implemented by the following scripts:

• ddl/createSTGCFG.ddl
• ddl/createNF.ddl
• ddl/createRIASSET.ddl
• ddl/createRISCHK.ddl
• ddl/createSTAR.ddl
• ddl/createSTARViews.ddl
• scripts/initCONFIG.sql
• scripts/initNF.sql
• scripts/initRIASSET.sql
• scripts/initRISCHK.sql
• scripts/initSTAR.sql
• ddl/createCALM.ddl
• ddl/createCALMViews.ddl
• scripts/initCALM.sql
• scripts/star_grants.sql
• scripts/calm_grants.sql

Dropping the Data Warehouse

If any of the above steps fail for a reason, the following script can be used to drop the partially created artifacts.

DROP TABLESPACE VNF_IDX INCLUDING CONTENTS;
DROP TABLESPACE VNF_32K INCLUDING CONTENTS;
DROP TABLESPACE VSTR_IDX INCLUDING CONTENTS;
DROP TABLESPACE VSTR_32K INCLUDING CONTENTS;
DROP TABLESPACE RIS_TEMP INCLUDING CONTENTS;

Modifying the Tablespace creation step

Only step 1 can be bypassed during the setup. To bypass step 1, the following property must be set to false in the teamserver.properties file of the JTS server prior to running the data warehouse setup:

com.ibm.team.datawarehouse.db.automatic.setup = false

Bypassing step 1, will require the user to manually run the SQL that creates the required tablespaces.

Scripts in the Server Data Warehouse folder

Scripts in the server data warehouse folder can be used to create the data warehouse but these will do an A to Z creation of the database, including changing the database configuration parameters. So the usage of these scripts is not recommended and is there just for historical/documentation reasons to give the users an idea about what the automated data warehouse creation process does.


DB2 DW 테이블스페이스 생성 정보 (자동)

- 테이블스페이스 ; TEMPSPACE32K (시스템 임시 / SMS / BPTEMP32K)
 페이지 크기(바이트)                      = 32768
 Extent 크기(페이지)                      = 32
 프리페치 크기(페이지)                    = 32
 컨테이너 수                              = 1
        컨테이너 ; C:\DB2\NODE0000\RIDWTBS\tempspace32k 폴더

- 테이블스페이스 ; VNF_IDX (일반 / DMS / 자동크기재조정 / BP_32K)
 전체 페이지 수                            = 20000
 사용된 페이지 수                         = 18464
 사용 가능한 페이지 수                    = 1504
 페이지 크기(바이트)                      = 32768
 Extent 크기(페이지)                      = 32
 프리페치 크기(페이지)                    = 32
 컨테이너 수                              = 1
        컨테이너 ; C:\DB2\NODE0000\RIDWTBS\vnf_idx.1 / 640MB

- 테이블스페이스 ; VNF_32K (일반 / DMS / 자동크기재조정 / BP_32K)
 전체 페이지 수                            = 20000
 사용된 페이지 수                         = 19488
 사용 가능한 페이지 수                    = 480
 페이지 크기(바이트)                      = 32768
 Extent 크기(페이지)                      = 32
 프리페치 크기(페이지)                    = 32
 컨테이너 수                              = 1
        컨테이너 ; C:\DB2\NODE0000\RIDWTBS\vnf_32k.1 / 640MB

- 테이블스페이스 ; VSTR_IDX (일반 / DMS / 자동크기재조정 / BP_32K) 
 전체 페이지 수                            = 24800
 사용된 페이지 수                         = 24096
 사용 가능한 페이지 수                    = 672
 페이지 크기(바이트)                      = 32768
 Extent 크기(페이지)                      = 32
 프리페치 크기(페이지)                    = 32
 컨테이너 수                              = 1
        컨테이너 ; C:\DB2\NODE0000\RIDWTBS\vstr_idx.1 / 740MB

- 테이블스페이스 ; VSTR_32K (일반 / DMS / 자동크기재조정 / BP_32K)
 전체 페이지 수                            = 24800
 사용된 페이지 수                         = 24160
 사용 가능한 페이지 수                    = 608
 페이지 크기(바이트)                      = 32768
 Extent 크기(페이지)                      = 32
 프리페치 크기(페이지)                    = 32
 컨테이너 수                              = 1
        컨테이너 ; C:\DB2\NODE0000\RIDWTBS\vstr_32k.1 / 740MB

- 버퍼 풀 ; BPTEMP32K (페이지크기 32 / 버퍼풀크기 4000)
- 버퍼 풀 ; BP_32K (페이지크기 32 / 버퍼풀크기 8000)