SQL Relay - Getting Started With IBM DB2

Getting Started With IBM DB2

Installation

Install pdksh
Install libncurses.so.4
Install RPM
Install Compatibility Packages
PATH Environment Variable
DB2 Installation
Restore unixODBC if Necessary
Ignore the Misinformation
Install the Documentation

Creating a Database
Accessing a Database

Accessing a Local Database
Accessing a Remote Database
Using the DB2 Client Program
Using the DB2 Information and Control Centers

Accessing a Database With SQL Relay

Installation

I've sucessfully installed DB2 V7.2 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 rpm. 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 libncurses.so.4

DB2 requires libncurses.so.4. Many older distributions come with libncurses.so.4. Many newer distributions come with libncurses.so.5 (or newer). Some distributions come with both. If your distribution does NOT have libncurses.so.4, create a symbolic link to the newer library as follows.

(These instructions assume that your system has ncurses version 5.2 and that libncurses.so.5.2 resides in /usr/lib. If your system has a different version of ncurses or if it resides somewhere else (for example, in /lib) then modify the instructions accordingly)

cd /usr/lib
ln -s libncurses.so.5.2 libncurses.so.4

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

Install Compatibility Packages

DB2 V7.2 for Linux requires glibc 2.1. Many newer Linux distributions come with glibc 2.2. On these distributions it is necessary to install some compatibility packages.

Distributions that come with glibc 2.1 include RedHat 6, Debian 2.2, Mandrake 7 and Slackware 7. Distributions that come with glibc 2.2 include RedHat 7, Mandrake 8 and Slackware 8.

RedHat provides compatability RPM's for RedHat 7 that can actually be used on any Linux that comes with glibc 2.2. These RPM's can be downloaded from rpmfind. You'll need the following RPM's.

compat-glibc-6.2-2.1.3.2.i386.rpm for RedHat Linux 7.2
compat-egcs-6.2-1.1.2.16.i386.rpm for RedHat Linux 7.2

You can install the compatibility RPM's using rpm -i.

PATH Environment Variable

On TurboLinux (and possibly other distributions as well), 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

IBM DB2 V7.2 Personal Developer's Edition is available from the IBM website as a tarball. As root, create a temporary directory, move the tarball into it and extract it using tar xf. The extraction process creates a directory called 018_EEE_LNX_NLV. Change directories to 018_EEE_LNX_NLV.

On Debian Linux and Slackware Linux, edit the db2_install script and comment out the line:

rpm -qa | grep rpm 1>/dev/null 2>/dev/null

Run the db2_install script.

When prompted to specify a keyword, type DB2.EENT and press return.

When the script completes, run rpm -qa and verify that the following RPM's were installed:

db2cnvc71-7.1.0-40
db2gs71-7.1.0-40
db2cucs71-7.1.0-40
db2engn71-7.1.0-40
db2cdrd71-7.1.0-40
db2dj71-7.1.0-40
db2pext71-7.1.0-40
db2cliv71-7.1.0-40
db2rte71-7.1.0-40
db2wcc71-7.1.0-40
db2hten71-7.1.0-40
db2cnvk71-7.1.0-40
db2cj71-7.1.0-40
db2conn71-7.1.0-40
db2smpl71-7.1.0-40
db2xlic71-7.1.0-40
db2cnvj71-7.1.0-40
db2crte71-7.1.0-40
db2cdb71-7.1.0-40
db2das71-7.1.0-40
db2cnvt71-7.1.0-40
db2repl71-7.1.0-40
db2adts71-7.1.0-40
db2jdbc71-7.1.0-40
db2adt71-7.1.0-40

Restore unixODBC if Necessary

The DB2 installation creates symbolic links from include files in /usr/IBMdb2/V7.1/include to /usr/include. If you had a unixODBC-devel RPM installed prior to installing DB2, the include files provided by the unixODBC-devel RPM will have been overwritten by these symbolic links. You should probably reinstall the unixODBC-devel RPM. SQL Relay looks in /usr/IBMdb2/V7.1/include for DB2 includes, so not having them in /usr/include won't hurt, but not having the proper unixODBC includes in /usr/include will cause the ODBC database connection build to fail.

Ignore the Misinformation

There are many, many instances where this DB2 distribution identifies itself as version 7.1 instead of 7.2. For example, the RPM's have a 7.1.0 version number and the installation process created the directory /usr/IBMdb2/V7.1. These clues might lead one to think that he or she has accidentally installed version 7.1 instead of version 7.2. Ignore the clues. Rest assured that you have installed version 7.2 despite the misinformation.

Install the Documentation

Once the RPM's are installed, you can install the documentation by running /usr/IBMdb2/V7.1/doc/db2insthtml en_US. To view the documentation, point your browser at /usr/IBMdb2/V7.1/doc/en_US/html.

Creating a Database

A DB2 installation consists of an administration server and one or more instances. Each instance contains one or more databases. The administration server coordinates the instances. The administration server is owned by an OS-level user. Each instance is owned by a seperate OS-level user as well.

To create an instance and an administration server, run /usr/IBMdb2/V7.1/install/db2setup, a text-based installation program.

Select [ Create... ]
Select Create a DB2 Instance
Accept the default user name (db2inst1).
Accept the default UID.
Accept the default group name (db2iadm1).
Accept the default GID.
Pick a password.
Select [ OK ]

A screen for creating the Fenced User (the user that will run user defined functions and stored procedures) will now appear.

Accept the default user name (db2fenc1).
Accept the default UID.
Accept the default group name (db2fadm1).
Accept the default GID.
Pick a password.
Select [ OK ] This will return you to Create DB2 Services screen.

Select Create the Administration Server
Accept the default user name (db2as).
Accept the default UID.
Accept the default group name (db2asgrp).
Accept the default GID.
Pick a password.
Select [ OK ]
If you get a DB2SYSTEM will be set to ... dialog, select [ OK ].

On the Create DB2 Services screen, select [ OK ]
Select [ Continue ]
Select [ OK ] in the This is your last chance to stop dialog.

Now the instance and administration server will be created.
If all went well, you'll get a Completed Sucessfully. notice.
To exit,
Select [ OK ]
Select [ OK ]
Select [ Close ]
Select [ OK ]

Should you need to create additional DB2 instances, follow this procedure again using a different user name and skipping the steps pertaining to the Administration Server. The /usr/IBMdb2/V.1/instance/db2ilist command lists all of the DB2 instances on the local computer.

If you need to drop an instance, run /usr/IBMdb2/V.1/instance/db2idrop instancename, substituting the name of the instance you want to drop for instancename.

The /usr/IBMdb2/V.1/instance/dasilist command lists all of the DB2 administration servers on the local computer.

If you need to drop an administration server, run /usr/IBMdb2/V.1/instance/dasidrop servername, substituting the name of the administration server you want to drop for servername.

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"

By default, a DB2 instance only accept connections from clients running on the local machine. To enable connections from clients running on a remote machine over TCP, execute commands like the following. These commands set up the db2inst1 instance on port 50000.

log in as root
view /etc/services and look for a line like:

db2cdb2inst1 50000/tcp # Connection port for DB2 instance db2inst1

if no such line exists, add it
log in as db2as
run the following commands:

. /home/db2inst1/sqllib/db2profile
db2 "update database manager configuration using svcename db2inst1"
db2stop
db2start

Now the db2inst1 instance should be accessible to remote clients on port 50000.

This should be enough you get you started. To set up more complex configurations, consult the documentation that came with your DB2 distribution.

Starting the Database at Boot Time

You can use the following script to start/stop the database at boot/shutdown time.

#!/bin/sh

case "$1" in
        start)
                /usr/IBMdb2/V7.1/instance/db2istrt
        ;;
        stop)
                /usr/IBMdb2/V7.1/instance/db2ishut
        ;;
        *)
        echo $"Usage: $0 {start|stop}"
        exit 1
esac

exit 0

Install this script and run it with the "start" option to start up the database. Running it with the "stop" option shuts the database down. To access a database, it must be running.

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

Hostname localhost

Instance db2inst1

remote machine

Hostname remotehost
Instance
testinst
	owned by user testinst with password testpass
	allows remote connections on port 50000
Database testdb

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 testpass

   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

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.

<!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="db2test" password="db2test"/>
                </users>
                <connections>
                        <connection connectionid="db2test" string="db=db2inst1" 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"