Setting up the DB2 database on Ubuntu and testing JDBC connectivity

I got my DB2 database installation running on a Ubuntu 11.10 machine recently, and have documented the steps I carried out below.

Downloading DB2

The express edition is free, and can be downloaded from Download DB2 Express-C. You’ll need to register for an IBM id if you don’t already have one.

Extracting and installing

Use the command tar -zvxf db2exc_975_LNX_x86.tar.gz to extract the gzipped file. Run the installer using sudo ./db2setup, and choose “Install a Product” on the left menu. The installation process is quite straightforward after this point.

Troubleshooting libaio.so.1 'not found' when running db2setup

I faced the following problem when I ran the setup script:

$ sudo ./db2setup
ERROR: 
   The required library file libaio.so.1 is not found on the system. 
   Check the following web site for the up-to-date system requirements
   of IBM DB2 9.7
   http://www.ibm.com/software/data/db2/udb/sysreqs.html
   http://www.software.ibm.com/data/db2/linux/validate  
  Aborting the current installation ...
  Run installation with the option "-f sysreq" parameter to force the installation.

To fix it, I had to run sudo apt-get install libaio-dev to install the missing package.

Verifying the Installation

The following command verifies the db installation and configured instances:

sudo /opt/ibm/db2/V9.7/bin/db2val -a

Creating a database

Switch to the user account that is the owner of the instance (db2inst1 is the default).

$ su db2inst1

Switch to the bash shell if necessary:

$ bash

Under this user account, you can run DB2 commands as illustrated below. Note that the create database command takes time - it took several minutes on my machine.

$ db2 create database test
DB20000I  The CREATE DATABASE command completed successfully.
$ db2 connect to test

   Database Connection Information

 Database server        = DB2/LINUX 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = TEST

$ db2 "create table test.technonstop(id int, username varchar(200))"
DB20000I  The SQL command completed successfully.
$ db2 "INSERT INTO test.technonstop VALUES(1, 'abdullah')"
DB20000I  The SQL command completed successfully.

Troubleshooting

If you're unable to run any db2 command, the db2 environment variables may not have been sourced. To do it, run the following command at the terminal, replacing db2inst1 with the instance owner.

. /home/db2inst1/sqllib/db2profile

Java Program to Test JDBC Connectivity

I use the following Java program to test to see if JDBC connectivity works from a Java program, after assigning appropriate values to the constants at the beginning:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;


public class PrintSQLTable {

	final static String dbdriver = "com.ibm.db2.jcc.DB2Driver";
	final static String dburl = "jdbc:db2://localhost:50000/test";
	final static String dbuser = "db2inst1";
	final static String dbpassword = "myPassword";
	final static String dbtable = "test.technonstop";
	
	public static void main(String[] args) throws ClassNotFoundException,
			SQLException {
		Class.forName(dbdriver);
		Connection connection = DriverManager.getConnection(dburl, dbuser,
				dbpassword);
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement
				.executeQuery("SELECT * FROM " + dbtable);

		StringBuilder tableContents = new StringBuilder("");
		ResultSetMetaData metaData = resultSet.getMetaData();
		int noOfColumns = metaData.getColumnCount();

		for (int i = 1; i <= noOfColumns; i++) {
			tableContents.append(metaData.getColumnName(i) + " \t ");
		}

		tableContents.append("\n");
		int sbLength = tableContents.length();
		for (int i = 0; i < sbLength; i++)
			tableContents.append("-");
		tableContents.append("\n");

		while (resultSet.next()) {
			for (int i = 1; i <= noOfColumns; i++) {
				tableContents.append(resultSet.getString(i) + " \t ");
			}
			tableContents.append("\n");
		}

		if (tableContents.length() == 0)
			tableContents.append("No data found");

		System.out.println(tableContents);
	}


}

Run the program with the db2jcc.jar file in the classpath, as shown below:

$ java -cp /opt/ibm/db2/V9.7/java/db2jcc.jar:. PrintSQLTable
ID 	 USERNAME 	 
-----------------
1 	 abdullah