#!/bin/sh


if [ $# -ne 2 ]
then
   echo "Usage: schema_C.sh <database_name> <database dir>"
   exit 1
fi

DB=$1
DB_DIR=$2


##############################################
#  clean up tablespaces and
#  datafiles from  earlier attempts
##############################################
db2 -v connect to ${DB}
echo "Cleaning up old table spaces"
db2 -v DROP TABLESPACE C_cust_space
db2 -v DROP TABLESPACE C_supp_space
db2 -v DROP TABLESPACE C_site_space
db2 -v DROP TABLESPACE C_parts_space
db2 -v DROP TABLESPACE C_custinv_space
db2 -v connect reset



##############################################
# Create the required tablespaces
# ALLOCATED TO THE NEXT 100M 
##############################################
echo "Creating C table spaces and tables..."

db2 -v connect to ${DB}
db2 -v "CREATE TABLESPACE C_cust_space \
       managed by automatic storage \
       initialsize 14700M bufferpool C_cust_bp \
       extentsize 1024K prefetchsize 8K \
       no file system caching"

db2 -v "CREATE TABLESPACE C_c_idx_space \
       managed by automatic storage \
       initialsize 4700M bufferpool C_c_idx_bp \
       extentsize 1024K prefetchsize 8K \
       no file system caching"

db2 -v "CREATE TABLESPACE C_supp_space \
       managed by automatic storage \
       initialsize 8M bufferpool C_supp_bp \
       extentsize 1024K prefetchsize 8K \
       no file system caching"

db2 -v "CREATE TABLESPACE C_supp_idx_space \
       managed by automatic storage \
       initialsize 8M bufferpool C_supp_idx_bp \
       extentsize 1024K prefetchsize 8K \
       no file system caching"

db2 -v "CREATE TABLESPACE C_site_space \
       managed by automatic storage \
       initialsize 8M bufferpool C_site_bp \
       extentsize 1024K prefetchsize 8K \
       no file system caching"

db2 -v "CREATE TABLESPACE C_site_idx_space \
       managed by automatic storage \
       initialsize 8M bufferpool C_site_idx_bp \
       extentsize 1024K prefetchsize 8K \
       no file system caching"


db2 -v "CREATE TABLESPACE C_parts_space \
        managed by automatic storage \
        initialsize 2048M bufferpool C_parts_bp \
        extentsize 1024K prefetchsize 8K \
        no file system caching"

db2 -v "CREATE TABLESPACE C_p_idx_space \
        managed by automatic storage \
        initialsize 518M  bufferpool C_p_idx_bp \
        extentsize 1024K prefetchsize 8K \
        no file system caching"

db2 -v "CREATE TABLESPACE C_custinv_space \
	managed by automatic storage \
        initialsize 33412M bufferpool C_ci_bp \
        extentsize 1024K prefetchsize 8K \
        no file system caching"

db2 -v "CREATE TABLESPACE C_ci_idx_space \
	managed by automatic storage \
	initialsize 55000M bufferpool C_ci_idx_bp \
	extentsize 1024K prefetchsize 8K \
	no file system caching"

db2 -v connect reset


##############################################
# Running scripts to create schema and indexes
##############################################
db2 -v connect to ${DB}
db2 -tvf sql/schema_C.sql
db2 -v connect reset

