Database Design and Management

In this module, we want to create the database, schemas, tables and some user defined functions. Please follow the steps below exactly. Notice that you need to use db2 instance user.

Install db2 and db2 spatial extender, install universal fix packs.

  • The guide for the administrator to implement and initialize the PAIS database server on a single node without partitioning can be found here.
  • The guide for the administrator to implement and initialize the PAIS database server on two nodes with partitioning can be found here.
  • The guide for the administrator to implement and initialize the PAIS database server on multiple nodes with partitioning can be found here.

Customize the script createdb.sql for your case.

Alter the name and path of the database accordingly. Our database named pais and we put our database in /home/db2inst1/DB2/data/pais, you can change them. Or just keep them for convenience and make directories /home/db2inst1/DB2/data/pais for the database, /home/db2inst1/DB2/log/pais for the log, /home/db2inst1/DB2/data/pais/master for the master node space and /home/db2inst1/DB2/data/pais/part for the spatial table space. You also need to modify the size of table spaces to fit your application. In the script we set the size of MASTER table space to 5GB and SPATIALTBS32K table space to 15GB for test. You can set it as large as possible, like 20GB and 100GB.

e.g.: create folders:

 $mkdir -p /home/db2inst1/DB2/data/pais
 $mkdir -p /home/db2inst1/DB2/log/pais

Create database using the following command (db2se enable included)

 $db2 -tf createdb.sql

Restart database using

 $db2stop force
 $db2start

Connect to database using the command:

 $db2 connect to pais

Create tables of pais and pi using commands in the following:

 $db2 -tf table_pais.sql
 $db2 -tf table_pidb.sql

Create stored procedures and user defined functions using

 $db2 -td@ -f sp_histogram.sql

In some scripts errors will occur such as no specified database, table or stored procedure is found. That’s normal because we want to drop the database, table or stored procedure before creating them, which means there should always be a drop command before each create command. Just disregard this kind of error reports in the process.