db2 create database script

먼저, 아래의 스크립트를 create_db.sql 파일로 만듭니다.

옵션 1-데이터베이스를 생성하는 데이터베이스 사용자와 IBM®ControlCenter에서 사용할 데이터베이스 사용자가 같은 경우 이 스크립트를 사용합니다.
옵션 2-데이터베이스를 생성하는 데이터베이스 사용자와 IBM®ControlCenter에서 사용할 데이터베이스 사용자가 같지 않은 경우 이 스크립트를 사용합니다.

Option1

 
-- Script to create production, reporting and staging databases for 
 
-- IBM Control Center. 
 
-- The db user that creates the database automatically inherits the 
 
-- database administration privilege.
 
-- Edit this script and replace @DBNAME@ with your database name. 
 
 
-- 
CREATE DATABASE @DBNAME@ 
 
AUTOMATIC STORAGE YES 
 
USING CODESET UTF-8 TERRITORY DEFAULT 
 
COLLATE USING SYSTEM PAGESIZE 32768;
 
 
CONNECT TO @DBNAME@;
 
CREATE BUFFERPOOL @DBNAME@_04KBP IMMEDIATE SIZE AUTOMATIC PAGESIZE 4K;
 
CREATE BUFFERPOOL @DBNAME@_08KBP IMMEDIATE SIZE AUTOMATIC PAGESIZE 8K;
 
CREATE BUFFERPOOL @DBNAME@_16KBP IMMEDIATE SIZE AUTOMATIC PAGESIZE 16K;
 
CONNECT RESET;
 
 
CONNECT TO @DBNAME@;
 
CREATE  USER TEMPORARY  TABLESPACE SCCUSERTMP PAGESIZE 32K  BUFFERPOOL  IBMDEFAULTBP; 
 
CREATE REGULAR TABLESPACE TS_REG04_@DBNAME@  PAGESIZE 4K   BUFFERPOOL  @DBNAME@_04KBP PREFETCHSIZE AUTOMATIC;
 
CREATE REGULAR TABLESPACE TS_REG08_@DBNAME@  PAGESIZE 8K   BUFFERPOOL  @DBNAME@_08KBP PREFETCHSIZE AUTOMATIC;
 
CREATE REGULAR TABLESPACE TS_REG16_@DBNAME@  PAGESIZE 16K  BUFFERPOOL  @DBNAME@_16KBP PREFETCHSIZE AUTOMATIC;
 
CONNECT RESET;

Option2

-- Script to create production and reporting databases for 
 
-- IBM Control Center. 
 
-- The db user that creates the database automatically inherits the 
 
-- database administration privilege.
 
-- Edit this script and replace @DBNAME@ with your database name.
 
-- Replace @DBUSERID@ with your DB user ID.
 
 
CREATE DATABASE @DBNAME@ 
 
AUTOMATIC STORAGE YES 
 
USING CODESET UTF-8 TERRITORY DEFAULT 
 
COLLATE USING SYSTEM PAGESIZE 32768;
 
 
CONNECT TO @DBNAME@;
 
CREATE BUFFERPOOL @DBNAME@_04KBP IMMEDIATE SIZE AUTOMATIC PAGESIZE 4K;
 
CREATE BUFFERPOOL @DBNAME@_08KBP IMMEDIATE SIZE AUTOMATIC PAGESIZE 8K;
 
CREATE BUFFERPOOL @DBNAME@_16KBP IMMEDIATE SIZE AUTOMATIC PAGESIZE 16K;
 
CONNECT RESET;
 
 
CONNECT TO @DBNAME@;
 
CREATE  USER TEMPORARY  TABLESPACE SCCUSERTMP PAGESIZE 32K  BUFFERPOOL  IBMDEFAULTBP; 
 
CREATE REGULAR TABLESPACE TS_REG04_@DBNAME@  PAGESIZE 4K   BUFFERPOOL  @DBNAME@_04KBP PREFECTCHSIZE AUTOMATIC; 
 
CREATE REGULAR TABLESPACE TS_REG08_@DBNAME@  PAGESIZE 8K   BUFFERPOOL  @DBNAME@_08KBP PREFETCHSIZE AUTOMATIC;
 
CREATE REGULAR TABLESPACE TS_REG16_@DBNAME@  PAGESIZE 16K  BUFFERPOOL  @DBNAME@_16KBP PREFETCHSIZE AUTOMATIC;
 
CONNECT RESET;
 
 
CONNECT TO @DBNAME@;
 
GRANT CREATETAB,BINDADD,CONNECT,IMPLICIT_SCHEMA ON DATABASE  TO USER @DBUSERID@;
 
GRANT USE OF TABLESPACE SCCUSERTMP TO USER @DBUSERID@;
 
GRANT USE OF TABLESPACE USERSPACE1 TO USER @DBUSERID@;
 
GRANT USE OF TABLESPACE TS_REG04_@DBNAME@ TO USER @DBUSERID@;
 
GRANT USE OF TABLESPACE TS_REG08_@DBNAME@ TO USER @DBUSERID@;
 
GRANT USE OF TABLESPACE TS_REG16_@DBNAME@ TO USER @DBUSERID@;
 
CONNECT RESET;

그리고 아래 명령어를 실행해 줍니다.

. $HOME/sqllib/db2profile (For bash or Korn shell, this sets up the DB2 command line environment.)
 
cd $HOME/sqllib/bin
 
db2 -stvf create_db.sql

EUC-KR SAMPLE

CREATE DATABASE SURL AUTOMATIC STORAGE YES ON '/opt/ibm/db2/V11.1/Databases/SURL'
DBPATH ON '/opt/ibm/db2/V11.1/Databases/SURL'
USING CODESET EUC-KR TERRITORY KR COLLATE USING SYSTEM PAGESIZE 32768;
CONNECT TO SURL;
UPDATE DATABASE CONFIGURATION USING APPLHEAPSZ 1024 DEFERRED;
UPDATE DATABASE CONFIGURATION USING LOCKTIMEOUT 30 DEFERRED;
UPDATE DATABASE CONFIGURATION USING APP_CTL_HEAP_SZ 1024 DEFERRED;
UPDATE DATABASE CONFIGURATION USING LOGFILSIZ 32768 DEFERRED;
UPDATE DATABASE CONFIGURATION USING LOGSECOND 8 IMMEDIATE ;
CONNECT RESET;