Db2 reference

The following table lists the configurable parameters and default values for Db2 Community Edition Version 11.5.4.0.

When setting up a Db2 database for the application discovery service on OCP, you have to copy and paste the following code into the create_database.sh script.

#!/bin/sh

#/*******************************************************************************
#*
#*  Copyright © Aricent Holdings Luxembourg S.a.r.l. 2020.  All rights reserved.
#*
#******************************************************************************/
#// 
#////////////////////////////// End Standard Header /////////////////////////////*/  

############### Begin Standard Header - Do not add comments here ###############
# 
# File:     %W%
# Version:  %I%
# Modified: %G% %U%
# Build:    %R% %L%
# 
# Licensed Materials - Property of IBM
# 
# Restricted Materials of IBM
# 
# 5724-N55
# 
# (C) COPYRIGHT IBM CORP. 2007, 2011.  All Rights Reserved.
# 
# US Government Users Restricted Rights - Use, duplication or
# disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
# 
############################# End Standard Header ############################## 

# set -x

if test -z "$1"
then 
    echo "Usage: $0 <database_name>"
    exit 1
fi


tune_db_noauto() {
echo "SUCCESS: Create database $1 completed."
db2 <<EOF
connect to $1
alter bufferpool IBMDEFAULTBP SIZE 240000
create bufferpool BUF8K immediate size 384 pagesize 8k
create bufferpool BUF32K immediate size 384 pagesize 32k
connect reset

connect to $1
create tablespace userspace2 pagesize 32k managed by system using ('usercont2') bufferpool BUF32K
create large tablespace largespace2 pagesize 32k managed by database using (file 'largedata' 2560) bufferpool BUF32K
create system temporary tablespace largetemp2 pagesize 32k managed by system using ('systemp2') bufferpool BUF32K

create tablespace USERSPACE3 pagesize 8k managed by system using ('usercont3') bufferpool BUF8K

alter bufferpool BUF8K SIZE 40000
alter bufferpool BUF32K SIZE 8000

update db cfg using LOGFILSIZ 8192
update db cfg using LOGBUFSZ 1024
update db cfg using LOGPRIMARY 12
update db cfg using LOGSECOND 40
update db cfg using CATALOGCACHE_SZ 4000
update db cfg using PCKCACHESZ 4096
update db cfg using AVG_APPLS 3
update db cfg using NUM_IOCLEANERS automatic
update db cfg using NUM_IOSERVERS 10
update db cfg using DFT_DEGREE ANY
update db cfg using APP_CTL_HEAP_SZ 4000
update db cfg using MAXLOCKS 35 LOCKLIST 20000
update db cfg using SHEAPTHRES_SHR MANUAL SORTHEAP 8000
update db cfg using AUTO_MAINT OFF
update db cfg using UTIL_HEAP_SZ 8000
update db cfg using DBHEAP 8000
update db cfg using APPLHEAPSZ 2000
disconnect $1
quit
EOF
db2set DB2_EVALUNCOMMITTED=YES
db2set DB2_SELECTIVITY=DSCC
}

tune_db_auto() {
echo "SUCCESS: Create database $1 completed."
if test "$version" -ne "9" 
then 
  USE_AUTOMATIC=YES
  PARAM_VAL=AUTOMATIC
else 
  USE_AUTOMATIC=
fi

db2 connect to $1

if [ -z "$USE_AUTOMATIC" ]; then PARAM_VAL=240000; fi
db2 alter bufferpool IBMDEFAULTBP SIZE $PARAM_VAL

db2 <<EOF
create bufferpool BUF8K immediate size 384 pagesize 8k
create bufferpool BUF32K immediate size 384 pagesize 32k
connect reset

connect to $1
create tablespace userspace2 pagesize 32k managed by automatic storage bufferpool BUF32K
create large tablespace largespace2 pagesize 32k managed by automatic storage  bufferpool BUF32K
create system temporary tablespace largetemp2 pagesize 32k managed by automatic storage bufferpool BUF32K
create tablespace USERSPACE3 pagesize 8k managed by automatic storage bufferpool BUF8K

update db cfg using LOGFILSIZ 8192
update db cfg using LOGBUFSZ 1024
update db cfg using LOGPRIMARY 12
update db cfg using LOGSECOND 40
update db cfg using CATALOGCACHE_SZ 4000
update db cfg using PCKCACHESZ 4096
update db cfg using AVG_APPLS 3
update db cfg using NUM_IOCLEANERS automatic
update db cfg using NUM_IOSERVERS 10
update db cfg using DFT_DEGREE ANY
update db cfg using APP_CTL_HEAP_SZ 4000
update db cfg using MAXLOCKS 35 LOCKLIST 20000
update db cfg using AUTO_MAINT OFF

EOF

if [ -z "$USE_AUTOMATIC" ]; then PARAM_VAL=40000; fi
db2 alter bufferpool BUF8K SIZE $PARAM_VAL

if [ -z "$USE_AUTOMATIC" ]; then PARAM_VAL=8000; fi
db2 alter bufferpool BUF32K SIZE $PARAM_VAL 

if [ -z "$USE_AUTOMATIC" ]
then 
  db2 update db cfg using SHEAPTHRES_SHR MANUAL SORTHEAP 8000
else
  db2 update db cfg using SHEAPTHRES_SHR AUTOMATIC SORTHEAP AUTOMATIC
fi

if [ -z "$USE_AUTOMATIC" ]; then PARAM_VAL=8000; fi
db2 update db cfg using UTIL_HEAP_SZ $PARAM_VAL

if [ -z "$USE_AUTOMATIC" ]; then PARAM_VAL=8000; fi
db2 update db cfg using DBHEAP $PARAM_VAL

if [ -z "$USE_AUTOMATIC" ]; then PARAM_VAL=2000; fi
db2 update db cfg using APPLHEAPSZ $PARAM_VAL

db2 disconnect $1
db2 quit

echo ""
echo "-------------------$1 Instance Created Successfully----------------------"
echo ""

db2set DB2_EVALUNCOMMITTED=YES
db2set -immediate DB2_SELECTIVITY=DSCC 2>&1 >/dev/null || db2set DB2_SELECTIVITY=DSCC
}

db2cfexp /tmp/db2cfg.$$ maintain
output=`cat /tmp/db2cfg.$$ | grep -i "^\[DB>.*:$1\]"`
rm -f /tmp/db2cfg.$$

# Get db2 version from db2cfexp command. Donot use db2level because
# output could be translated
db2cfexp /tmp/db2cfg.$$ maintain
version_str=`cat /tmp/db2cfg.$$ | grep "APPLICATION=" | awk '{print $2}'`
echo "DB2 version $version_str"

# Check if db already exists
dbName=`db2 list db directory | grep "Database name" | awk '{print $4}' | grep -w -i $1`
dbName=`echo $dbName | tr '[:upper:]' '[:lower:]'`
userDbName=`echo $1 | tr '[:upper:]' '[:lower:]'`
if [ "$userDbName" = "$dbName" ]
then
  echo ""
  echo "INFO: Database with same name $1 already exists."
  exit 3
fi

release=`echo $version_str | cut -c 3`
version=`echo $version_str | cut -c 1`
rm -f /tmp/db2cfg.$$

if test "$version" -eq 9 -a "$release" -eq 8 -o "$version" -eq 0 -a "$release" -eq 1 -o "$version" -eq 0 -a "$release" -eq 5 -o "$version" -eq 1 -a "$release" -eq 1 -o "$version" -eq 1 -a "$release" -eq  5
then
   db2 create db $1 using codeset UTF8 territory US
   if test "$?" -eq 0
   then
      tune_db_auto $1
   else
     echo "ERROR: Create database $1 failed."
     exit 2
   fi
else
   if test "$version" -eq 9
   then
      db2 create db $1 automatic storage no using codeset UTF8 territory US
      if test "$?" -eq 0
      then
         tune_db_noauto $1
      else
        echo "ERROR: Create database $1 failed."
        exit 2
      fi
   else
      echo "Unsupported DB2 version $version_str"
      exit 1
   fi
fi