Modifying database script packages

After you export a script package from the system, modify the necessary file to create the new custom database script package

Tuning database configurations

Certain DB2 configuration changes will prevent a database from working with the database pattern automation, introducing risk in consistent deployment of databases. Changes to configurations that reference tables spaces and bufferpools, backup facilities, the authentication server, containers, instance names, monitoring agents, and external tooling should be tested thoroughly before deployment in a live environment.

The following configuration parameters can be tuned with less risk:

ALIAS
CODESET and TERRITORY
COLLATE
PAGESIZE
CATALOG/USER/TEMPORARY TABLESPACE
AUTOCONFIGURE
PREFETCHSIZE

Script package details

A database script package is a compressed file containing the following first-level directories. Each subdirectory is self-contained and has an entry script that invokes other scripts or files under it when called. The create_db subdirectory and its entry script create_db.sh are mandatory, all other subdirectories are optional.

The scripts are invoked in the following order:

create_db.sh (mandatory)
tune_inst.sh
tune_db
init_db

Subdirectory Required? Entry script name Parameters Description

Table 1. List all script details.
Subdirectory	Required?	Entry script name	Parameters	Description
`create_db`	Mandatory	`create_db.sh` Sample script: # inst_name=$1 db_name=$2 sqltype=$3 codeset=$4 territory=$5 collate=$6 pagesize=$7 dataMountPoint=$8 if [[ ${sqltype} =="ORACLE" ]] ; then if [[ ${pagesize} -ne 32 ]] ; then echo "Forceoverwriting database page size to 32 K for Oracle compatibility..." pagesize=32 fi fi db2 "CREATE DATABASE ${db_name} ON ${dataMountPoint} USING CODESET ${codeset} TERRITORY ${territory} COLLATE USING ${collate} PAGESIZE ${pagesize} K" rc=$? if [[ ${rc} -ne 0 ]] ; then echo "Failed to create database." exit ${rc} fi	instance name database name Parameters are specified in the following format: `sqltype` `codeset` `territory` `collate` `pagesize` `dataMountPoint`	This directory includes the scripts to create the database. It is invoked to create the database after the db2 instance is created. The script returns "0" when successful. Other returned values, for example "-1", indicate failure. Check the log files (console.log, trace.log, error.log) for errors.
`tune_db`	Optional	`tune_db.sh` Sample script: `#!/bin/sh inst_name=$1 db_name=$2 default_user=$3 default_password=$4`	instance name database name default user name default user password	This directory includes scripts to tune the database. It is invoked to create tablespaces or grant privileges to after the database is created. The script returns "0" when successful. Other returned values, for example "-1", indicate failure.
`init_db`	Optional	`init_db.sh` Sample script: #!/bin/sh inst_name=$1 db_name=$2 default_user=$3 default_user_password=$4 db2 "CONNECT TO ${db_name}" if [ $? -ne 0 ] ; then echo "database connection failed aborting" exit -1 fi db2 "CREATE DATABASE PARTITION GROUP sdpg ON DBPARTITIONNUMS (0)" if [ $? -ne 0 ] ; then echo "creation of sdpg failed, aborting" exit -1 fi db2 "COMMIT WORK" if [ $? -ne 0 ] ; then echo "commit failed" exit -1 fi db2 "UPDATE DATABASE CONFIGURATION FOR ${db_name} USING LOCKLIST 16384 MAXLOCKS 10 PCKCACHESZ -1 LOGBUFSZ 2048 UTIL_HEAP_SZ 65536 STMTHEAP 10000 LOGFILSIZ 12800 LOGPRIMARY 50 LOGSECOND 0 WLM_COLLECT_INT 20 DFT_PREFETCH_SZ 384 CHNGPGS_THRESH 30 NUM_IOSERVERS 12 " if [ $? -ne 2 ] ; then echo "update DB CFG failed, aborting" exit -1 fi db2 "UPDATE DATABASE CONFIGURATION FOR ${db_name} DBPARTITIONNUM 0 USING NUM_IOCLEANERS 7 " if [ $? -ne 2 ] ; then echo "update DB CFG failed, aborting" exit -1 fi db2 "CONNECT RESET" db2 "CONNECT TO ${db_name}" if [ $? -ne 0 ] ; then echo "database connection failed, aborting" exit -1 fi db2 "CREATE BUFFERPOOL BP_16k ALL DBPARTITIONNUMS SIZE 60000 PAGESIZE 16K NUMBLOCKPAGES 20000 BLOCKSIZE 16" if [ $? -ne 0 ] ; then echo "creation of BP_16k failed, aborting" exit -1 fi db2 "CREATE TABLESPACE ibmdefaultgroup IN DATABASE PARTITION GROUP SDPG BUFFERPOOL BP_16K" if [ $? -ne 0 ] ; then echo "creation of IBMDEFAULTGROUP failed, aborting" exit -1 fi db2 "GRANT USE OF TABLESPACE IBMDEFAULTGROUP TO PUBLIC" if [ $? -ne 0 ] ; then echo "GRANT USE OF TABLESPACE IBMDEFAULTGROUP TO PUBLIC failed, aborting" exit -1 fi db2 "CREATE TEMPORARY TABLESPACE temp16k IN DATABASE PARTITION GROUP ibmtempgroup PAGESIZE 16K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 BUFFERPOOL BP_16k OVERHEAD 4.0 NO FILE SYSTEM CACHING TRANSFERRATE 0.04" if [ $? -ne 0 ] ; then echo "creation of TEMP16k failed aborting" exit -1 fi db2 "DROP TABLESPACE tempspace1" if [ $? -ne 0 ] ; then echo "drop tempspace1 failed, aborting" exit -1 fi db2 "CREATE DATABASE PARTITION GROUP DWECONTROLGROUP ON DBPARTITIONNUM (0)" if [ $? -ne 0 ] ; then echo "creation of dwecontrolgroup failed, aborting" exit -1 fi db2 "CREATE TABLESPACE dwedefaultcontrol IN DATABASE PARTITION GROUP dwecontrolgroup PAGESIZE 16K EXTENTSIZE 16 PREFETCHSIZE AUTOMATIC BUFFERPOOL BP_16k OVERHEAD 4.0 TRANSFERRATE 0.04 AUTORESIZE YES MAXSIZE 256G" if [ $? -ne 0 ] ; then echo "creation of dwedefaultcontrol failed, aborting" exit -1 fi db2 "CREATE TABLESPACE ts_monitoring OVERHEAD 4.0 TRANSFERRATE 0.04" if [ $? -ne 0 ] ; then echo "creation of ts_monitoring failed, aborting" exit -1 fi db2 "COMMIT WORK" if [ $? -ne 0 ] ; then echo "commit failed, aborting" exit -1 fi db2 "CREATE TABLESPACE ts_small IN DATABASE PARTITION GROUP sdpg BUFFERPOOL bp_16k OVERHEAD 4.0 TRANSFERRATE 0.04" if [ $? -ne 0 ] ; then echo "creation of ts_small failed, aborting" exit -1 fi db2 "CONNECT RESET"	instance name database name default user name default user password	This directory includes scripts or files to grant privileges and to create database objects such as schema, tables, views, procedures, and functions. It is invoked to create database objects or load data into database after the database is created. The script returns "0" when successful. Other returned values, for example "-1", indicate failure.

create_db

Mandatory

create_db.sh

Sample script:

#

inst_name=$1
db_name=$2
sqltype=$3
codeset=$4
territory=$5
collate=$6
pagesize=$7
dataMountPoint=$8

if [[ ${sqltype} =="ORACLE" ]] ; then
if [[ ${pagesize} -ne 32 ]] ; then
        echo "Forceoverwriting database page size to 32 K for Oracle compatibility..."
        pagesize=32
    fi
fi

db2 "CREATE DATABASE ${db_name} ON ${dataMountPoint} USING CODESET ${codeset} TERRITORY ${territory} COLLATE USING ${collate} PAGESIZE ${pagesize} K"
rc=$?
if [[ ${rc} -ne 0 ]] ; then
   echo "Failed to create database."
   exit ${rc}
fi

instance name

database name

Parameters are specified in the following format:

sqltype

codeset

territory

collate

pagesize

dataMountPoint

This directory includes the scripts to create the database.

It is invoked to create the database after the db2 instance is created.

The script returns "0" when successful. Other returned values, for example "-1", indicate failure. Check the log files (console.log, trace.log, error.log) for errors.

tune_db

Optional

tune_db.sh

Sample script:

#!/bin/sh

inst_name=$1
db_name=$2
default_user=$3
default_password=$4

instance name
database name
default user name
default user password

This directory includes scripts to tune the database.

It is invoked to create tablespaces or grant privileges to after the database is created.

The script returns "0" when successful. Other returned values, for example "-1", indicate failure.

init_db

Optional

init_db.sh

Sample script:

#!/bin/sh

inst_name=$1
db_name=$2
default_user=$3
default_user_password=$4

db2 "CONNECT TO ${db_name}"
if [ $? -ne 0 ] ; then
   echo "database connection failed aborting"
   exit -1
fi
db2 "CREATE DATABASE PARTITION GROUP sdpg ON DBPARTITIONNUMS (0)"
if [ $? -ne 0 ] ; then
   echo "creation of sdpg failed, aborting"
   exit -1
fi
db2 "COMMIT WORK"

if [ $? -ne 0 ] ; then
   echo "commit failed"
   exit -1
fi

db2 "UPDATE DATABASE CONFIGURATION FOR ${db_name} USING
LOCKLIST 16384
MAXLOCKS 10
PCKCACHESZ -1
LOGBUFSZ 2048
UTIL_HEAP_SZ 65536
STMTHEAP 10000
LOGFILSIZ 12800
LOGPRIMARY 50
LOGSECOND 0
WLM_COLLECT_INT 20
DFT_PREFETCH_SZ 384
CHNGPGS_THRESH 30
NUM_IOSERVERS 12
"

if [ $? -ne 2 ] ; then
  echo "update  DB CFG failed, aborting"
  exit -1
fi
db2 "UPDATE DATABASE CONFIGURATION FOR ${db_name} DBPARTITIONNUM 0 USING  NUM_IOCLEANERS 7
"

if [ $? -ne 2 ] ; then
   echo "update  DB CFG failed, aborting"
   exit -1
fi
db2 "CONNECT RESET"

db2 "CONNECT TO ${db_name}"
if [ $? -ne 0 ] ; then
   echo "database connection failed, aborting"
   exit -1
fi
db2 "CREATE BUFFERPOOL BP_16k ALL DBPARTITIONNUMS SIZE 60000 PAGESIZE 16K NUMBLOCKPAGES 20000 BLOCKSIZE 16"

if [ $? -ne 0 ] ; then
   echo "creation of BP_16k failed, aborting"
   exit -1
fi
db2 "CREATE TABLESPACE ibmdefaultgroup IN DATABASE PARTITION GROUP SDPG BUFFERPOOL BP_16K"
if [ $? -ne 0 ] ; then
   echo "creation of IBMDEFAULTGROUP failed, aborting"
   exit -1
fi
db2 "GRANT USE OF TABLESPACE IBMDEFAULTGROUP TO PUBLIC"
if [ $? -ne 0 ] ; then
   echo "GRANT USE OF TABLESPACE IBMDEFAULTGROUP TO PUBLIC failed, aborting"
   exit -1
fi

db2 "CREATE TEMPORARY TABLESPACE temp16k IN DATABASE PARTITION GROUP ibmtempgroup PAGESIZE 16K  
MANAGED BY AUTOMATIC STORAGE 
EXTENTSIZE 16 
BUFFERPOOL BP_16k
OVERHEAD 4.0
NO FILE SYSTEM CACHING 
TRANSFERRATE 0.04"

if [ $? -ne 0 ] ; then
   echo "creation of TEMP16k failed aborting"
   exit -1
fi
db2 "DROP TABLESPACE tempspace1"
if [ $? -ne 0 ] ; then
    echo "drop tempspace1 failed, aborting"
    exit -1
fi
db2 "CREATE DATABASE PARTITION GROUP DWECONTROLGROUP ON DBPARTITIONNUM (0)"
if [ $? -ne 0 ] ; then
    echo "creation of dwecontrolgroup failed, aborting"
    exit -1
fi

db2 "CREATE TABLESPACE dwedefaultcontrol IN DATABASE PARTITION GROUP dwecontrolgroup PAGESIZE 16K 
EXTENTSIZE 16
PREFETCHSIZE AUTOMATIC
BUFFERPOOL BP_16k
OVERHEAD 4.0 TRANSFERRATE 0.04 AUTORESIZE YES MAXSIZE 256G"
if [ $? -ne 0 ] ; then
   echo "creation of dwedefaultcontrol failed, aborting"
   exit -1
fi

db2 "CREATE TABLESPACE ts_monitoring OVERHEAD 4.0 TRANSFERRATE 0.04"
if [ $? -ne 0 ] ; then
   echo "creation of ts_monitoring failed, aborting"
   exit -1
fi


db2 "COMMIT WORK"

if [ $? -ne 0 ] ; then
   echo "commit failed, aborting"
   exit -1
fi

db2 "CREATE TABLESPACE ts_small IN DATABASE PARTITION GROUP sdpg BUFFERPOOL bp_16k OVERHEAD 4.0 TRANSFERRATE 0.04"
if [ $? -ne 0 ] ; then
   echo "creation of ts_small failed, aborting"
   exit -1
fi
db2 "CONNECT RESET"

instance name
database name
default user name
default user password

This directory includes scripts or files to grant privileges and to create database objects such as schema, tables, views, procedures, and functions.

It is invoked to create database objects or load data into database after the database is created.

The script returns "0" when successful. Other returned values, for example "-1", indicate failure.