Db/2 – Database – Create – Sample Script

Background

Wanted to provide and give a brief introduction on how to create a small footprint database on DB/2 LUW.

Basis

We will use a sample script provided by VMWare.

Here are the links :-

  1. vSphere 4.1 – ESX and vCenter > ESX and vCenter Server Installation Guide > vCenter Server Databases > Configure DB2 Databases
    • Use a Script to Create a DB2 Database
      Link

Script

WideWlrd

Outline

  1. Database
    • Create Database WideWrld
    • Code Set :- UTF-8
    • Page Size :- 4K
  2. Buffer Pools
    • 4K ( WideWrld_04KBP )
    • 8K ( WideWrld_08KBP )
    • 16K ( WideWrld_16KBP )
    • 32K ( WideWrld_32KBP )
  3. Table Spaces
    • Temporary
      • User
        • WideWrld_TMP_USER ( Page Size 32K )
    • Data
      • User
        • TS_WideWrld_REG04 ( Page Size 4K )
        • TS_WideWrld_REG08 ( Page Size 8K )
        • TS_WideWrld_REG16 ( Page Size 16K )

db2cmd Script


CREATE DATABASE WideWrld
AUTOMATIC STORAGE YES 
USING CODESET UTF-8 
TERRITORY DEFAULT
COLLATE USING SYSTEM PAGESIZE 4096;

CONNECT TO WideWrld;

CREATE BUFFERPOOL WideWrld_04KBP 
	IMMEDIATE SIZE 
	AUTOMATIC PAGESIZE 4K
	;

CREATE BUFFERPOOL WideWrld_08KBP 
	IMMEDIATE SIZE 
	AUTOMATIC PAGESIZE 8K
	;

CREATE BUFFERPOOL WideWrld_16KBP 
	IMMEDIATE SIZE 
	AUTOMATIC PAGESIZE 16K;

CREATE BUFFERPOOL WideWrld_32KBP 
	IMMEDIATE SIZE 
	AUTOMATIC PAGESIZE 32K;
	
CONNECT RESET;


CONNECT TO WideWrld;

CREATE  USER TEMPORARY  TABLESPACE WideWrld_TMP_USER
	PAGESIZE 32K  
	BUFFERPOOL  WideWrld_32KBP
	; 

CREATE REGULAR TABLESPACE TS_WideWrld_REG04 
	PAGESIZE 4K   
	BUFFERPOOL  WideWrld_04KBP 
	PREFETCHSIZE AUTOMATIC
	;
 
CREATE REGULAR TABLESPACE TS_WideWrld_REG08 
	PAGESIZE 8K   
	BUFFERPOOL  WideWrld_08KBP 
	PREFETCHSIZE AUTOMATIC
	;

CREATE REGULAR TABLESPACE TS_WideWrld_REG16  
	PAGESIZE 16K  
	BUFFERPOOL  
	WideWrld_16KBP 
	PREFETCHSIZE AUTOMATIC
	;

CREATE LARGE TABLESPACE TS_WideWrld_REG32  
	PAGESIZE 32K  
	BUFFERPOOL  
	WideWrld_32KBP 
	PREFETCHSIZE AUTOMATIC
	;
	
CONNECT RESET;

Batch file


setlocal

set "_file=WideWorldImportersDW.sql"

db2 -svtf %_file%

endlocal

Output

Image

WideWorldImportersDW_20180918_1222PM.

Drop Database

Preface

Once you are good with your test, please feel free to drop using steps that look like the one below.

Script


FORCE APPLICATION ALL;

DROP DATABASE WideWrld;

CONNECT RESET;

Summary

Standards

There are some standards that have to be followed.

Here are some of them:

  1. Database names
    • 8 Characters of less
      Link
  2. Other database Objects
    • 16 Characters of less
      SQL and XML Limits
      Link

One thought on “Db/2 – Database – Create – Sample Script

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s