DB2 Command Line Interface (CLI) Installation on Linux

_images/damico-bw_normal.jpg

José Ricardo de Olveira Damico

_images/julianom_normal.jpg

Juliano Marcos Martins

1. Installation

1.1. The target machine for this documentation is a Debian 5 (Etch). However this tutorial will works for who is using Ubuntu, Fedora, OpenSuse, Suse and RedHat

1.2. Create a folder for DB2 compressed binary files and copy it that folder (in this tutorial we used DB2_ESE_97_Linux_x86.tar.gz);

debian-droid:/#  mkdir /opt/db2bin
debian-droid:/#  cd /opt/db2bin
debian-droid:/opt/db2bin# ls -la
total 657928
drwxr-xr-x 2 root    root         4096 2011-03-15 17:37 .
drwxr-xr-x 4 root    root         4096 2011-03-15 17:44 ..
-rw-r--r-- 1 jdamico jdamico 673044532 2011-03-15 17:33 DB2_ESE_97_Linux_x86.tar.gz

1.3. Uncompress DB2 compressed binary files (in this tutorial we used DB2_ESE_97_Linux_x86.tar.gz);

debian-droid:/opt/db2bin# gunzip DB2_ESE_97_Linux_x86.tar.gz
debian-droid:/opt/db2bin# tar xvf DB2_ESE_97_Linux_x86.tar

1.4. Files & Libraries installation:

debian-droid:/opt/db2bin# cd server
debian-droid:/opt/db2bin/server# ./db2_install

Default directory for installation of products - /opt/ibm/db2/V9.7

***********************************************************
Do you want to choose a different directory to install [yes/no] ?
no

1.4.1 Answer “no”;

Specify one of the following keywords to install DB2 products.

  ESE
  CONSV
  WSE
  EXP
  PE
  CLIENT
  RTCL

Enter "help" to redisplay product names.

Enter "quit" to exit.

***********************************************************
ese

1.4.2 Answer “ese”;

1.5 License installation:

debian-droid:/opt/db2bin/server# /opt/ibm/db2/V9.7/adm/db2licm -a db2ese.lic

1.6 Create DB2 Instance

1.6.1 Create Essential User IDs and Groups

debian-droid:/opt/db2bin/server# groupadd db2grp1
debian-droid:/opt/db2bin/server# groupadd dasadm1
debian-droid:/opt/db2bin/server# groupadd db2fgrp1
debian-droid:/opt/db2bin/server# useradd -g db2grp1 -G dasadm1 -m db2inst1
debian-droid:/opt/db2bin/server# passwd db2inst1
debian-droid:/opt/db2bin/server# useradd -g dasadm1 -G db2grp1 -m dasusr1
debian-droid:/opt/db2bin/server# passwd dasusr1
debian-droid:/opt/db2bin/server# useradd -g db2fgrp1 -m db2fenc1
debian-droid:/opt/db2bin/server# passwd db2fenc1

1.6.2 Create DAS

debian-droid:/opt/db2bin/server# cd /opt/IBM/db2/V9.7/instance
debian-droid:/opt/ibm/db2/V9.7/instance# ./dascrt -u dasusr1
SQL4406W  The DB2 Administration Server was started successfully.
DBI1070I  Program dascrt completed successfully.

1.6.3 Create DB2 Instance

debian-droid:/opt/ibm/db2/V9.7/instance# cd /opt/IBM/db2/V9.7/instance
debian-droid:/opt/ibm/db2/V9.7/instance# ./db2icrt -u db2fenc1 db2inst1
DBI1070I  Program db2icrt completed successfully.

1.7 Set TCP/IP communication for the connectivity of client

debian-droid:/opt/ibm/db2/V9.7/instance# su - db2inst1
db2inst1@debian-droid:~$ db2set DB2COMM=tcpip
db2inst1@debian-droid:~$ db2 update dbm cfg using SVCENAME 50000
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
db2inst1@debian-droid:~$ exit
logout

1.8 Start DB2:

debian-droid:/opt/ibm/db2/V9.7/instance# su - db2inst1
db2inst1@debian-droid:~$ db2start
03/15/2011 20:58:34     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

1.8.1 Check DB2 service:

db2inst1@debian-droid:~$ netstat -an | grep 50000
tcp        0      0 0.0.0.0:50000           0.0.0.0:*               LISTEN

1.8.2 Test CLP:

db2inst1@debian-droid:~$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.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 =>

1.8.3 Quit CLP:

db2 => quit
DB20000I  The QUIT command completed successfully.

1.9 Create Repository Database Folder.

db2inst1@debian-droid:~$ exit
logout
debian-droid:/opt/ibm/db2/V9.7/instance# mkdir /db2repo
debian-droid:/opt/ibm/db2/V9.7/instance# chown -R db2inst1:db2grp1 /db2repo

2. Database Creation

2.1 Prepare database:

debian-droid:/opt/ibm/db2/V9.7/instance# su - db2inst1
db2inst1@debian-droid:~$ db2
        (c) Copyright IBM Corporation 1993,2007
        Command Line Processor for DB2 Client 9.7.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 =>

2.2 Create database:

db2 => ATTACH TO db2inst1

   Instance Attachment Information

 Instance server        = DB2/LINUX 9.7.0
 Authorization ID       = DB2INST1
 Local instance alias   = DB2INST1

db2 => CREATE DATABASE TUT_DB USING CODESET UTF-8 TERRITORY US
DB20000I  The CREATE DATABASE command completed successfully.

db2 => CONNECT TO TUT_DB

   Database Connection Information

 Database server        = DB2/LINUX 9.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = TUT_DB

db2 => CREATE BUFFERPOOL tut_buffer PAGESIZE 4096
DB20000I  The SQL command completed successfully.

db2 => CREATE REGULAR TABLESPACE tut_data PAGESIZE 4096 MANAGED BY DATABASE USING (file '/db2repo/TUT_TBS' 19200) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.33 BUFFERPOOL tut_buffer DROPPED TABLE RECOVERY ON
DB20000I  The SQL command completed successfully.

2.3 Create table:

db2 => CREATE TABLE TUTORIAL.CONTACT ( TUTORIAL_ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL PRIMARY KEY, TUTORIAL_NAME VARCHAR(150) NOT NULL, TUTORIAL_EMAIL VARCHAR(150) NOT NULL, TUTORIAL_PHONE VARCHAR(150) NOT NULL) IN tut_data
DB20000I  The SQL command completed successfully.

db2 => quit
DB20000I  The QUIT command completed successfully.
db2inst1@debian-droid:~$ exit
logout
debian-droid:/opt/ibm/db2/V9.7/instance#

3. Database Test

3.1 Use a db2 client to connect to machine where db2 was installed. In this tutorial we will use dbjmin (http://dbjmin.googlecode.com) as client (You can use any other one).

3.1.1 SERVER SIDE: Be sure about the reachable network/ip address of db2 (in our case 192.168.56.10):

debian-droid:/opt/ibm/db2/V9.7/instance#  ifconfig eth1
eth1      Link encap:Ethernet  HWaddr 08:00:27:86:4b:5f
inet addr:192.168.56.10  Bcast:192.168.56.255  Mask:255.255.255.0
inet6 addr: fe80::a00:27ff:fe86:4b5f/64 Scope:Link
UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
RX packets:1448 errors:0 dropped:0 overruns:0 frame:0
TX packets:906 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:144130 (140.7 KiB)  TX bytes:148414 (144.9 KiB)
Interrupt:16 Base address:0xd240

3.1.1 CLIENT SIDE: Be sure that client machine “sees” the db2 service at server machine:

root@dev-note:~# nmap 192.168.56.10 | grep 50000
50000/tcp open  iiimsf

3.1.2 CLIENT SIDE: Open dbjmin and write connection parameters and hit button “D”:

3.1.2.1 Type: db2

3.1.2.2 Srv: 192.168.50.10 (in our tutorial)

3.1.2.3 Port: 50000

3.1.2.4 db: TUT_DB

3.1.2.5 User: db2inst1

3.1.2.6 Passwd: #####

_images/dbjmin-test.png

3.1.2.7 Try to find the database and table created, in Schemas select TUTORIAL hit “>” button then try to find CONTACT table in “Tables” dropdown

_images/dbjmin-test1.png

3.1.2.8 Hit “Structure” button

_images/dbjmin-test2.png

4. Throubleshooting

4.1 Missed libaio.so.1

4.1.1 Problem:

debian-droid:/opt/db2bin/server# ./db2_install
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.

4.1.2 Solution:

debian-droid:/opt/db2bin/server# apt-get install libaio1

Table Of Contents

This Page