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.