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.

Download DB2 Express C Using HTTP

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.

DB2 Setup Launchpad - Install a Product as root

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 	



Comments

I am getting this after running this program...

[-]#java -cp /usr/share/java/db2jcc.jar:. CheckConnect

Exception in thread "main" com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=SAMPLE.STAFF, DRIVER=3.62.56
at com.ibm.db2.jcc.am.fd.a(fd.java:676)
at com.ibm.db2.jcc.am.fd.a(fd.java:60)
at com.ibm.db2.jcc.am.fd.a(fd.java:127)
at com.ibm.db2.jcc.am.jn.c(jn.java:2614)
at com.ibm.db2.jcc.am.jn.d(jn.java:2602)
at com.ibm.db2.jcc.am.jn.a(jn.java:2094)
at com.ibm.db2.jcc.t4.cb.g(cb.java:141)
at com.ibm.db2.jcc.t4.cb.a(cb.java:41)
at com.ibm.db2.jcc.t4.q.a(q.java:32)
at com.ibm.db2.jcc.t4.rb.i(rb.java:135)
at com.ibm.db2.jcc.am.jn.gb(jn.java:2064)
at com.ibm.db2.jcc.am.jn.a(jn.java:3089)
at com.ibm.db2.jcc.am.jn.a(jn.java:679)
at com.ibm.db2.jcc.am.jn.executeQuery(jn.java:663)
at CheckConnect.main(CheckConnect.java:23)

Is it because I have a different classpath then yours?
Please help me here...
and I am running this on Fedora 14 and db2 V9.7

By Anirudh

Looking up the reference for SQLState Messages, 42704 means "An undefined object or constraint name was detected." Could you double check that the SAMPLE.STAFF table exists?

Note that you may need to specify the current schema (in this case, SAMPLE) in your code. This can be done by appending it to your URL, or by prefixing it to every table name. A url to which the schema name is appended looks like this: jdbc:db2://localhost:50000/test:currentSchema=sample;

By Abdullah Chougle

Simply removing "SAMPLE" from "SAMPLE.STAFF", the query succeeded(i got the idea by simply running it through the terminal..:)..)

But now i got a new problem...
I'm trying to run a simple java program, it gets compiled but giving errors while running it...

[-]#javac OurFunction.java
[-]#java OurFunction
Exception in thread "main" java.lang.NoClassDefFoundError: OurFunction/class
Caused by: java.lang.ClassNotFoundException: OurFunction.class
at java.net.URLClassLoader$1.run(URLClassLoader.java:217)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:205)
at java.lang.ClassLoader.loadClass(ClassLoader.java:321)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:294)
at java.lang.ClassLoader.loadClass(ClassLoader.java:266)
Could not find the main class: OurFunction.class. Program will exit.


What I am doing here???

By Anirudh

It looks like the current directory is not in the classpath. Try running it using

java -cp . OurFunction

By Abdullah Chougle

I have made a table using the Linux command line and now I want to access it using the Java program but I am getting error.

This could be the program of unknown password of the db2inst3.
I have tried for default password of db2inst3 but I am unable to log in with that account.

Is there any other problem.

Add new comment