Getting Started With IBM DB2
Installation

I've sucessfully installed DB2 7.2 and 8.1 on Linux. I've never had access to any other version of DB2 for Linux or any version of DB2 for other platforms.

Install pdksh

First and foremost, you need pdksh (Public Domain Korn Shell). If it did not come with your distribution, you'll need to download and install it.

For RPM based distributions, look for pdksh at rpmfind and install it using rpm -i.

For Debian Linux, on newer Debian releases, if you have configured apt as illustrated in the APT-HOWTO, run apt-get install pdksh. If your distribution came with pdksh, you may be prompted to enter a CD. If not, it will be downloaded from the internet.

For Slackware Linux, the pdksh package is available from ftp.slackware.com. You can install it using installpkg.

Install RPM

On non-RPM based systems, you'll need to install RPM.

For Slackware Linux, the RPM package is available from ftp.slackware.com. You can install it using installpkg. Once RPM is installed, log in as root and run rpm --initdb.

On newer Debian releases, if you have configured apt as illustrated in the APT-HOWTO, run apt-get install rpm. If your distribution came with RPM, you may be prompted to enter a CD. If not, it will be downloaded from the internet. Once RPM is installed, log in as root and run rpm --initdb.

On systems where the rpm command resides in /usr/bin, you need to create a symbolic link between /usr/bin/rpm and /bin/rpm as follows.

ln -s /usr/bin/rpm /bin/rpm
PATH Environment Variable

The DB2 installer tries to run some command line programs that are found in /sbin and /usr/sbin. Make sure that these directories are in the root user's PATH.

DB2 Installation

Each version of DB2 has a slightly different installation procedure. Follow the links below for the version of DB2 that you are installing.

Installing IBM DB2 V7.2
Installing IBM DB2 V8.1

Creating a Database

Now that you have created an instance, you must create a database within the instance. Log in as db2inst1 and run the following command to create a database named testdb.

db2 "create database testdb"

Should you need to drop a database, the following command drops a database named testdb.

db2 "drop database testdb"
Accessing a Database


Accessing a Local Database

Accessing a local DB2 database using the db2 client tool is simple. For example, to access a database called testdb in the db2inst1 instance, owned by the db2inst1 user on the local machine, log in as db2inst1 and run the db2 command. Then use the CONNECT command to connect to a particular database.

[db2inst1@localhost db2inst1]$ db2
c(c) Copyright IBM Corporation 1993,2001
Command Line Processor for DB2 SDK 7.2.0

You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to testdb

   Database Connection Information

 Database server        = DB2/LINUX 7.2.0
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTDB

Accessing a Remote Database

If you want to access a database on a remote machine, the process is more complex. DB2 or "DB2 Connect" must be installed on the local and remote machines and a DB2 or "DB2 Connect" instance must be created on the local machine. "DB2 Connect" is a lightweight version of the DB2 database system whose instances cannot support a local database, and can only provide connectivity to a remote instance. In the instance on the local machine, use the CATALOG command to create aliases for the remote instance and database. Once these aliases are created, you can use the database alias to connect to the remote database.

Take the following scenario:

local machine
Hostnamelocalhost
Instancedb2inst1
remote machine
Hostnameremotehost
Instancetestinst
	owned by user testinst with password testpassword
	allows remote connections on port 50000
Databasetestdb

On localhost, in the db2inst1 instance, to create an instance alias named remoteinst referring to testinst on remotehost, and a database alias named remotedb referring to testdb, follow this procedure.

log in as db2inst1 on localhost
create the instance alias using the following commands:
db2 "catalog tcpip node remoteinst remote remotehost server 50000"
db2 "terminate"
create the database alias using the following commands:
db2 "catalog database testdb as remotedb at node remoteinst"
db2 "terminate"

Now that the aliases have been created, you can connect to the database alias using the CONNECT comamnd. Note that you must supply the user name and password.

[db2inst1@localhost db2inst1]$ db2
c(c) Copyright IBM Corporation 1993,2001
Command Line Processor for DB2 SDK 7.2.0

You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to remotedb user testinst using testpassword

   Database Connection Information

 Database server        = DB2/LINUX 7.2.0
 SQL authorization ID   = TESTINST
 Local database alias   = REMOTEDB

If you need to get a list of instance aliases, you can use the LIST NODE DIRECTORY command as follows.

db2 "list node directory"

To list the database aliases, you can use the LIST DATABASE DIRECTORY command. Note that this command lists local databases as well as remote databases.

db2 "list database directory"

If you need to drop a database or instance alias, use the UNCATALOG command. The following commands remove the remotedb database alias and the remoteinst instance alias.

db2 "uncatalog database remotedb"
db2 "uncatalog node remoteinst"
db2 "terminate"
Using the DB2 Client Program

When run with no arguments, the db2 client program provides an interactive shell, prompting you to enter commands or an SQL queries. Commands or queries must be entered on a single line and will run when the Enter or Return key is pressed. To exit, type quit.

A sample db2 session follows.

[db2inst1@localhost db2inst1]$ db2
c(c) Copyright IBM Corporation 1993,2001
Command Line Processor for DB2 SDK 7.2.0

You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to testdb

   Database Connection Information

 Database server        = DB2/LINUX 7.2.0
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTDB

db2 => create table testtable (col1 char(40), col2 int)
DB20000I  The SQL command completed successfully.
db2 => list tables

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
TESTTABLE                       DB2INST1        T     2002-03-01-01.55.19.671629

  1 record(s) selected.

db2 => describe table testtable show detail

Column                         Type                                                                                        Column     Partitioning key    Code
name                           schema                          Type name                       Length    Scale    Nulls    number     sequence            page     Default
------------------------------ ------------------------------- ------------------------------- --------- -------- -------- ---------- ------------------- -------- ----------------------------------------
COL1                           SYSIBM                          CHARACTER                              40        0 Yes               0                   0      819                                                                                                                                                                                                                                                               
COL2                           SYSIBM                          INTEGER                                 4        0 Yes               1                   0        0                                                                                                                                                                                                                                                               

  2 record(s) selected.

db2 => insert into testtable values ('hello',50)
DB20000I  The SQL command completed successfully.
db2 => insert into testtable values ('hi',60)
DB20000I  The SQL command completed successfully.
db2 => insert into testtable values ('bye',70)
DB20000I  The SQL command completed successfully.
db2 => select * from testtable

COL1                                     COL2       
---------------------------------------- -----------
hello                                             50
hi                                                60
bye                                               70

  3 record(s) selected.

db2 => update testtable set col2=0 where col1='hi'
DB20000I  The SQL command completed successfully.
db2 => select * from testtable

COL1                                     COL2       
---------------------------------------- -----------
hello                                             50
hi                                                 0
bye                                               70

  3 record(s) selected.

db2 => delete from testtable where col2=50
DB20000I  The SQL command completed successfully.
db2 => select * from testtable

COL1                                     COL2       
---------------------------------------- -----------
hi                                                 0
bye                                               70

  2 record(s) selected.

db2 => drop table testtable
DB20000I  The SQL command completed successfully.
db2 => quit
DB20000I  The QUIT command completed successfully.
Using the DB2 Information and Control Centers

IBM DB2 comes with a set of user-friendly, Java-based GUI utilities for browsing and configuring database instances.

These utilities require the Java Runtime Enviroment version 1.1.8 or higher. For Linux, JRE's are available as RPM's or tarballs from Blackdown, Sun and IBM. Of course, IBM recommends that you use their JRE.

To run the DB2 Information Center or Control Center, you have to log in as a database instance or adminstrative server owner such as db2as or db2inst1.

Make sure that the jre command is in the PATH environment variable for that user. If it isn't, modify that user's .bashrc to include it.

If you are running a 2.4 (or higher) kernel, add the following line to the user's .bashrc as well.

export LD_ASSUME_KERNEL=2.2.5

If you are running a kernel with Native Posix Threading Library support (such as on Redhat 9) then you need to add this line instead:

export LD_ASSUME_KERNEL=2.4.1

This may be necessary on other systems too. It is definitely necessary on Redhat 9.

To run the Information Center or Control Center, you first have to run the DB2 Java Server and give it a port number as follows.

db2jstrt 6720

Once it's running, you can run the DB2 Information Center or Control Center, also supplying the port.

db2ic 6720

or

db2cc 6720

Both utilities will ask for the username and password of an instance owner. Once you supply these credentials, you can browse and/or configure the instance owned by that user.

Accessing a Database With SQL Relay

Accessing DB2 from SQL Relay requires an instance entry in your sqlrelay.conf file for the database that you want to access. Here is an example sqlrelay.conf which defines an SQL Relay instance called db2test. This SQL Relay instance connects to the testdb database in the db2inst1 DB2 instance.

Important Note: For DB2 connections, sqlr-start must be run as the user that owns the DB2 instance that it is running against. In this example, the db2inst1 user owns the db2inst1 instance where the testdb database resides, so sqlr-start must be run as db2inst1.

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>

        <instance id="db2test" port="9000" socket="/tmp/db2test.socket" dbase="db2" connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="db2inst1" runasgroup="db2inst1" cursors="5" authtier="listener" handoff="pass">
                <users>
                        <user user="testuser" password="testpassword"/>
                </users>
                <connections>
                        <connection connectionid="db2test" string="db=testdb" metric="1"/>
                </connections>
        </instance>

</instances>

Now you can start up this instance with the following command.

su - db2inst1 -c "sqlr-start -id db2test"

To connect to the instance and run queries, use the following command.

sqlrsh -id db2test

The following command shuts down the SQL Relay instance.

su - db2inst1 -c "sqlr-stop db2test"