Using example scripts to create DB2 databases

You can use scripts to create DB2® databases with required parameters on Linux, UNIX, and Windows operating systems.

There are two scripts you can use to create DB2 databases with required parameters on Linux, UNIX, and Windows operating systems:

Option 1 - Use this script if the database user creating the database and the database user that is going to be used in IBM® Control Center are the same
Option 2 - Use this script if the database user creating the database and the database user that is going to be used in IBM Control Center are not the same

Creating databases with the option 1 script

To create a database with the option 1 script, complete the following steps:

Copy the option 1 script commands to a file named create_scc_db_sql.
Edit the create_scc_db.sql file to replace @DBNAME@ with your database name.
Run the create_scc_db.sql script from the bin folder in the DB2 install (or using any other option).

On Windows, type the following:

C:\db2\BIN\DB2CW.BAT  (This sets up the DB2 command line environment.)

C:\db2\BIN> db2 -stvf create_scc_db.sql

On Linux and UNIX, type the following:

. $HOME/sqllib/db2profile (For bash or Korn shell, 
this sets up the DB2 command line environment.)

cd $HOME/sqllib/bin

db2 -stvf create_scc_db.sql

For more information on setting up the Linux and UNIX environments, see the DB2 Knowledge Center.

Repeat this procedure to create all the databases needed by IBM Control Center.

CAUTION:

A database with partitioned IBM Control Center tables is the preferred database setup for performance reasons.

Option 1 script example

-- 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;

Creating databases with the option 2 script

To create a database with the option 2 script, complete the following steps:

Copy the option 2 script commands to a file named create_scc_db_sql.
Edit the create_scc_db.sql file to replace @DBNAME@ with your database name and replace @USERID@ with your database user ID.
Run the create_scc_db.sql script from the bin folder in the DB2 install (or using any other option).

On Windows, type the following:

C:\db2\BIN\DB2CW.BAT  (This sets up the DB2 command line environment.)

C:\db2\BIN> db2 -stvf create_scc_db.sql

On Linux and UNIX, type the following:

. $HOME/sqllib/db2profile (For bash or Korn shell, 
this sets up the DB2 command line environment.)

cd $HOME/sqllib/bin

db2 -stvf create_scc_db.sql

Repeat this procedure to create all the databases needed by IBM Control Center.

Option 2 script example

-- 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;