Setting up DB2 9.7 Express C on Ubuntu server 9.10 x64
This is based on an excellent blog-post for Ubuntu server 9.04.
The DB2 headless database setup is not very administrator friendly so this will hopefully save you some time.
Installing DB2
Download DB2 9.7 Express C for 64-bit Linux and install the software and it's dependencies:
$ tar xfvz db2exc_971_LNX_x86_64.tar.gz
$ cd expc
$ sudo su
# apt-get install libaio1 libstdc++6
# ./db2_install -f sysreq
# useradd -m db2inst1
# passwd db2inst1
# useradd -m db2fenc1
# passwd db2fenc1
# useradd -m db2das1
# passwd db2das1
# /opt/ibm/db2/V9.7/instance/dascrt -u db2das1
# /opt/ibm/db2/V9.7/instance/db2icrt -a server -u db2fenc1 db2inst1
# /opt/ibm/db2/V9.7/cfg/db2ln
The last command creates link to the different libraries, but does not work and you will see:
DBI1087E An attempt to create the link /usr/lib/libdb2.so.1 failed.
DBI1087E An attempt to create the link /usr/lib/libdb2.so.1.0 failed.
DBI1087E An attempt to create the link /usr/lib/libdb2e.so failed.
DBI1087E An attempt to create the link /usr/lib/libdb2e.so.1 failed.
DBI1087E An attempt to create the link /usr/lib/libdb2e.so.1.0 failed.
Correct these broken links:
# cd /usr/lib
# rm libdb2e.so.1.0
# ln -s libdb2e.so.1 libdb2e.so.1.0
# rm libdb2.so.1.0
# ln -s libdb2.so.1 libdb2.so.1.0
Enable TCP access to DB2:
# vim /etc/services
db2c_db2inst1 50000/tcp # DB2 connection service port
# su - db2inst1
$ db2stop
$ db2start
$ db2
update database manager configuration using svcename db2c_db2inst1
$ db2stop
$ db2start
$ db2
get database manager config
$ db2set DB2COMM=tcpip
$ db2stop
$ db2start
$ netstat -lnt | grep 50000
tcp 0 0 0.0.0.0:50000 0.0.0.0:* LISTEN
For some reason you cannot add links to the db2 init.d script form the regular rc?.d-directories so I used a quick fix for this:
# vim /etc/init.d/db2
#! /bin/sh
set -e
case "$1" in
start)
sudo su - db2inst1 -c 'db2start' >> /tmp/db2initd.log
sudo su - db2das1 -c 'db2admin start' >> /tmp/db2initd.log
;;
stop)
sudo su - db2das1 -c 'db2admin stop' >> /tmp/db2initd.log
sudo su - db2inst1 -c 'db2stop' >> /tmp/db2initd.log
;;
*)
exit 1
esac
exit 0
# chmod +x /etc/init.d/db2
# vim /etc/rc.local
...
/etc/init.d/db2
...
reboot now
When the server comes back up DB2 should have started and a TCP access should be available.
Create a database
Based on EJBCA's database howto:
$ su - db2inst1
$ db2 create database ejbca
$ db2 connect to ejbca user db2inst1
$ db2 CREATE BUFFERPOOL "BP16K" SIZE 2500 PAGESIZE 16384
$ db2 CONNECT RESET
$ db2 CONNECT TO EJBCA
$ db2 "CREATE REGULAR TABLESPACE EJBCADB_DATA_01 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 16384 MANAGED BY DATABASE USING (FILE '/home/db2inst1/db2inst1/EJBCA/ejbcadb_data_01.dbf'512000) EXTENTSIZE 32 PREFETCHSIZE 32 BUFFERPOOL BP16K OVERHEAD 7.500000 TRANSFERRATE 0.060000 FILE SYSTEM CACHING DROPPED TABLE RECOVERY ON"
Drop a database
$ su - db2inst1
$ db2 terminate
$ db2 drop database ejbca
Users
DB2 uses existing system users. We can use the database instance owner "db2inst1" for quick testing. A production system should have a dedicated "ejbca" user.
Useful commands
Run all these commands as the "db2inst1" user by using:
$ su - db2inst1
List tables:
$ db2 list tables
Show table data-types:
$ db2 describe table <table-name>
Re-organise a table after a column has been added or dropped:
$ db2 reorg table db2inst1.<table-name>
