Using the DB2 Client on the IA-64 Cluster

Introduction

All SDSC Compute Resources (DataStar, BlueGene, and the IA-64 TeraGrid cluster) are part of the TeraGrid. The Common TeraGrid Software Stack (CTSS) now includes DB2 client software to allow users to access DB2 databases. This tutorial describes how users can use their DB2 client to set up and access databases from the TeraGrid clusters. Specifically, users will learn how to setup a user environment, access DB2 interactively, and access DB2 via JDBC. In addition, we will provide a list of requirements for hosting user databases on SDSC's DataStar.

Accessing a DB2 Database Interactively

In this section you will learn how to use DB2's interactive client, CLP (Command Line Processor). The main reasons to use a database interactively is to quickly retrieve small pieces of information from it, or to test/prototype SQL queries to be included in a program or web interface.

It is necessary to have an account on a machine that has a DB2 instance. At SDSC, the ds003 login-node on DataStar maintains the DB2 instance. Therefore, it is necessary to have a DataStar account to access the DB2 client at SDSC. Before you proceed, please make sure you have a DataStar account. Note, you must have an account and have database privileges granted by a database owner (or DBA).

If you do not have an account on DataStar, please refer to the DataStar User Guide.

In this tutorial we will be using a database called TGSAMPLE. Here are the specific steps:

First you need to login into a Teragrid cluster using your favorite SSH client. For example, to log into SDSC's clusters:
% ssh user@tg-login.sdsc.teragrid.org
Type your password after the prompt.
Validate that your environment is setup to use db2 client:
% echo $DB2INSTANCE
The command above should display the name of the instance under which db2 client is installed (e.g. db2clnt).If echo returns empty then you need to add the db2 softenv key to your environment:
% soft add +db2
You can also add it permanently by editing your ~/.soft file and adding +db2. You will then need to reinitialize your environment by issuing "resoft" command. More information about softenv is available in the TeraGrid user guide.
Now you are ready to use the DB2 Command Line Processor (CLP). CLP allows you to connect to the database of your choice, and execute SQL statements. In our example we will be using a database called TGSAMPLE.
To start CLP Type:

% db2
you will see a version number and general help information followed by db2 prompt:
db2=>
At this point you can type SQL statements and db2 commands. But first you need to verify that TGSAMPLE database has been cataloged. At the db2 prompt type:
db2=>list database directory
one of the entries should contain TGSAMPLE. If it does not, then you are going to have to catalog it. At the db2 prompt, type the following:
db2=>catalog tcpip node ds003 remote ds003.sdsc.edu server 60035 ostype AIX
The above command catalogs a node on which TGSAMPLE is running. It is possible that a node has already been cataloged, in which case you will get a warning from DB2; just ignore it. Now lets catalog actual database:
db2=>catalog database TGSAMPLE at node ds003.
Now do a listing (i.e. list database directory) and you should see an entry for TGSAMPLE.
Now we are ready to access our database. At db2 prompt, type:
db2=>connect to TGSAMPLE user username
where username is your username on DataStar. At the password prompt type your DataStar password. You should see something similar to the following:
Database Connection Information
Database server = DB2/6000 8.1.3
SQL authorization ID = VEYTSER
Local database alias = TGSAMPLE
TGSAMPLE is our demo database which contains a list of fictional employees. Let's try a few simple SQL statements. At the db2 prompt, type:
db2=> list tables for schema viswanat
The above command lists tables in our database (viswanat is a user who created this database). You can also try doing "list tables for all" to see all schemas and tables. Now let's list our employees:
db2=> select * from viswanat.staff
A little more complicated:
db2=> select lastname,salary from viswanat.employee where bonus = 500
After you are done with your DB2 session please remember to disconnect. At the db2 prompt, type:

db2=> terminate
It is possible to access db2's CLP in a non-interactive mode. That is, you can execute commands from steps 4 and 5 from your shell as long as you prefix them with "db2"; and use quotes around commands. For example:
% db2 "connect to TGSAMPLE user username";
% db2 "select * from viswanat.employee";
% db2 "terminate ";
You can also execute db2 commands in batch mode. To do that you will need to put all of your SQL statements into a file and then type:
% db2 –tvf filename
Please note that you need to connect to the database before executing the above command. That is, it is possible to connect to a database in the batch mode, but it will require you to store your DataStar login and password in the text file.

Accessing a DB2 Database from a Java Program

In this part of the tutorial we will learn how to access TGSAMPLE from a Java program. As stated previously, first we need to make sure that our environment is setup. From a shell prompt, type:

% echo $DB2INSTANCE

The command above should display the name of the instance under which db2 client is installed (e.g. db2clnt). Java JDBC driver is located in:

~$DB2INSTANCE/db2i001/sqllib/java

If you received nothing from the "echo"; command, you need to source db2 start-up script (see step 2 in previous part of the tutorial). This script will setup your CLASSPATH. However, you can also setup CLASSPATH manually.

To successfully access DB2 database from your java program you need to:

Import the appropriate Java packages and classes (java.sql.* and COM.ibm.db2.jdbc.app.*)
Load the appropriate JDBC driver:
- For type 2 JDBC, COM.ibm.db2.jdbc.app.DB2Driver
- For type 4 JDBC, com.ibm.db2.jcc.DB2Driver. We recommend using type 4 because you do not need a full client to be installed nor do you need to catalog a database.
Provide appropriate URL to JDBC connect call. For our TGSAMPLE database:
- For type 2 driver: "jdbc:db2:TGSAMPLE";
- For type 4 driver: "jdbc:db2j:net://ds003.sdsc.edu:60035/TGSAMPLE

To see an example, see our sample code.

Additional DB2 Information

If you would like to learn more about DB2 please visit IBM's DB2 help website:

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp

We also recommend taking a look at the following website for a quick beginner tutorial:

http://users.sdsc.edu/~jrowley/db2/howto.html

Hosting DB2 Databases on SDSC's DataStar

Users can host their own database on DataStar. To request database space, apply using the NSF-sponsored national program, Partnership Online Proposal System (POPS). For more information, see How To Apply.