SDT1

Getting Started with DB2 Express C v10.1

We are using DB2 Express C for the exercises and assignments.

This document is not a full documentation of DB2. It is just an introduction to a quick start. I will
introduce new functionalities as we need them. Documentation is available at the DB2 Information
Center (Bookmark this!): http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp

1. Basic DB2 Concepts

You need to understand the concept of database manager, instance and databases to get started.
Those concepts are in the DB2 environment section of the Getting Started with DB2 Express C
available on the web site (still focused on version 9.7 but most of it is still valid).










Briefly:
The database manager is DB2-express C. It runs on the Ubuntu VM image (DB2-10.1-
students) running on VirtualBox (version 4.2)
A DB2 instance is an independant environment where databases can be created. In the
following, I assume that the instance name is db2inst1.
A database is a collection of tables. In the following, I assume that two databases have been
created on the db2inbst1 instance: SAMPLE and tuning. We will only be using tuning here.

When db2 is running, a few processes are running in the background (db2sysc, db2acd, db2bp, ).
Look up the command db2_local_ps if you are interested. No need to go into details there for now
though.

Note that you can access a DB2 instance locally, on your Ubuntu VM. You can also access it
remotely from your laptop. For remote access, you need to install a client on your laptop (see
Section 3).
2. DB2 Setup

You are very much welcome to download DB2 express and install it yourself on your laptop, or on
a server at home. I recommend that you install it on a VM, e.g., VirtualBox, so that you avoid
impacting your laptop installation (e.g., DB2 creates OS users).









Database Manager





Instance
Database
Database

I have created a VirtualBox appliance that contains Ubuntu 12.04 and DB2 v10.1. It is pretty
straightforward to import an appliance into VirtualBox. Please follow the steps mentioned in the
VirtualBox documentation.
This VM contains a few users (login/password). In case you forget, they all have the same
password:
root: student/tuning
db2 instance owner: db2inst1/tuning
db2 adminstration server: dasusr1/tuning

You should rely on the db2inst1 user to access to the DB2 instance running on the VM (See Section
1 above). Note that by default the default shell for db2inst1 is /bin/sh on linux. I have changed it to
/bin/bash on the VM. Note that the installation adds a couple of lines to .bashrc (and .profile) to
setup all appropriate environment variables.

You can now start the database server with (note that it should autostart whenever you log into
db2inst1):
# db2start

And stop it with:
# db2stop

Note that whenever you open a connection to a database (see below), you need to close it (db2
connect reset) so that db2stop completes.

For the rest of the exercise, I assume that the db2 server is running:
# db2start

You can now launch the db2 client with the db2 command:
# db2

You get a command prompt that you can use to enter commands to the database manager, as well as
SQL commands once you are connected to a given database. Type quit to exit.

db2 => qulL
u820000l 1he Cul1 command compleLed successfully.
#

You can either type db2, enter the command editor, type commands and quit when you are finished,
or give a command as parameter to the db2 program (see below).

I created a database on the db2inst1 instance, called tuning. Before you can access a database, you
must establish a connection with it.:

# db2 connecL Lo Lunlng

You can then enter SQL commands using the same db2 program. Use db2 -L to enter multiline SQL
commands terminated by a semicolon.

# db2 -L

db2 => creaLe Lable r (a lnL),
u820000l 1he SCL command compleLed successfully.
db2 => selecL *
db2 (conL.) => from r,

A
-----------

0 record(s) selecLed.

It is very cumbersome to use db2 online command as there is no advanced editing feature. An
alternative is to write your SQL code into a file which you execute with db2 as follows:

# db2 -Lf yourflle.sql

You can create your own yourfile.sql. We will give an example in a second. When you are done
working you should terminate the connection:

# db2 connecL reseL

Now to a slightly more interesting exercise.

You can download airline-schema.sql and insert-airline.sql from the web site (still as db2inst1 user).

# db2 connecL Lo Lunlng

You can now load the SQL commands contained in the file airline-schema.sql

# db2 -Lf alrllne-schema.sql

This is an efficient alternative to typing in commands in an interactive way.

11 tables have been created. Use the list tables command to display their names:

# db2 llsL Lables

Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
AIRCRAFT DB2INST1 T 2010-01-27-11.19.16.576619
ASSIGNED_TO DB2INST1 T 2010-01-27-11.19.17.395250
BOOKED_ON DB2INST1 T 2010-01-27-11.19.17.137540
CAN_FLY DB2INST1 T 2010-01-27-11.19.17.717832
DEPARTURE DB2INST1 T 2010-01-27-11.19.16.904541
EMPLOYEE DB2INST1 T 2010-01-27-11.19.15.979429
EQUIPMENT DB2INST1 T 2010-01-27-11.19.18.017700
FLIGHT DB2INST1 T 2010-01-27-11.19.16.759633
PERSON DB2INST1 T 2010-01-27-11.19.15.537831
PILOT DB2INST1 T 2010-01-27-11.19.16.329816
PLANE DB2INST1 T 2010-01-27-11.19.16.447932
R DB2INST1 T 2010-01-27-10.02.44.354480

12 record(s) selected.

You can also use the describe command followed by an sql query to find out the attribute names of
the query result, e.g. Execute db2 -t to enter interactive mode:
db2 => descrlbe selecL * from alrcrafL,

Column lnformaLlon

number of columns: 2

SCL Lype 1ype lengLh Column name name lengLh
-------------------- ----------- ------------------------------ -----------
300 SMALLln1 2 SL8lAL_nC 9
448 vA8CPA8 13 MCuLL_nC 8


What are the attribute names for the tables Flight and Plane?

Step 2

Download the file insert-airline.sql.
Same procedure using the insert-airline.sql file that inserts rows into the tables you have just
created.

# db2 -Lf lnserL-alrllne.sql
# db2 commlL

Don't forget this last line (commit). You can now select all the tuples of a table using the following
SQL command:

# !"# %selecL * from AlrcrafL"


How many rows are there in table Flight?
What is the earlier arrival time in table Flight?

Step 3

The airline database contains information about airplanes, personel (including pilots) as well as
flight routes.

The following SQL query finds out the employee number and name of the pilots booked (as
passengers) on a flight they are assigned to (as pilots). Ideally, this should not happen in a
consistent database (a pilot cannot be a passenger in the plane she is flying). Is it however the case
for the database you have created? The way to find out is to run the following SQLquery that finds
the employee number, name of those employees who are booked and assigned to a same flight. The
result of this query should be the empty set. Use db2 -t:

select distinct Employee.Emp_No, Employee.name
from Employee, Booked_On, Assigned_To, Pilot
where Employee.Name = Booked_On.Name
and Employee.Emp_No = Pilot.Emp_No
and Assigned_To.Emp_No = Employee.Emp_No
and Assigned_To.Dep_date = Booked_On.Dep_Date;

What result do you get? Why?




3. Remote Access

This step is very optional, but recommended as it allows you to run experiments with a dedicated
computer for the database server while you run experiments from a (separate) client computer.

You should download the data server client from
https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?source=swg-idsc97
You will need to register to the IBM academic initiative, which is gratis and has no downside that I
can see.

Once you have downloaded the client, read the readmefirst.txt file and follow the instructions (they
are different on windows and linux). A GUI shows up, you should simply follow the steps. The
client comprises a set of tools both commands that you can run from a shell prompt and GUI for
database management. These tools are clients that connect to the database instance running on your
server. At the end of the process, a post installation message shows up that lets you know that you
should run the db2profile script before using the tools. You can also follow some first steps (I
skipped that so I cannot say much about it).

Now, you should have a working db2 client environment on your laptop. For example you can run
the db2 command:

your laptop $ db2 list db directory

This command should not return anything if you have just installed the DB2 client and there are no
local databases.

What we need to do is to register the remote instance and the remote database on our client. The
way it works in DB2 is that this registration is static (it is a separate step that must take place before
you can connect to the database). The concept is that of a local catalog that you update with
information about the remote instance and database. Run the command:

your laptop$ db2 catalog tcpip node dbms remote <YOUR INSTANCE IP> server <YOUR
INSTANCE PORT NUMBER> remote_instance db2inst1

Note that ts is the name I gave to the node. You can pick your own (but there are limitations on the
number of characters for that name).

You get the instance port number on the server by running
# db2 attach to db2inst1
# db2 get dbm cfg show detail
This command displays a set of lines. One of them gives the port number (that DB2 mixes with the
notion of service name, which I don't want to get into):
TCP/IP Service name (SVCENAME) = 50000

Note also that if your instance is running on the VM that I prepared (with a NAT network
interface), you will need to establish port forwarding so that the traffic incoming on the host is
transmitted to the guest VM. Assuming YOUR INSTANCE PORT NUMBER is 50000, and that
the VM name is DB2-10.1-student you will have to run on the host where your VM is located (not
within the VM, but in the host OS):
VBoxManage modifyvm "DB2-10.1-students" -natpf2 "db2server,tcp,,50000,,50000"

See http://www.virtualbox.org/manual/ch06.html#natforward for details.

Now, you need to configure your server firewall so that your client can connect to the DB2 instance
via port 50000.

Now back to the client on your laptop. To commit the change to the catalog you need to flush the
cache with the following command:
your laptop$ db2 terminate

Once you have registered the remote instance (node), you can register the remote database:

your laptop$ db2 catalog database tuning at node dbms
your laptop$ db2 terminate

After you have registered remote instance and database, you can connect to the database:
your laptop$ db2 connect to tuning user db2inst1 using tuning

Now that you are connected you can run any command on your remote database using db2:
your laptop$ db2 list tables