Db2onus

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>
page_revision: 1, last_edited: 1264081892|%e %b %Y, %H:%M %Z (%O ago)
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License