Db2 tutorial

1. DB2 Universal Database SSI-Hub Texas A&M

2. TEXAS A&M-SSI HUB


DB2 is a multi-platform scalable, object-relational database server
.

This tutorial is for people with little or no experience with DB2.

DB2 GUIS Over-View.

Step by Step approach of how to accomplish some of the most
important database tasks.
Database tasks can be achieved in various ways, in this tutorial, the
focus is on using the GUI tools
Glossary is provided at the end, for the terms used in DB2.

3. TEXAS A&M-SSI HUB

Key Capabilities and Benefits
Superior scalability
DB2 Universal Database can run on everything from laptops supporting mobile users to
massively parallel systems with terabytes of data and/or thousands of users.

Multimedia extensibility
It allows to extend the capabilities of the database to meet your specific organizational
requirements. This includes the ability to support more advancedapplications involving
multimedia data such as documents, images, audio, video, and spatial data.

Complete web-enablement
One of the key new application areas is e-business. DB2 Universal Database is fully integrated
with web technology so that data can be easily accessed from theInternet or from the company
intranet

Universal access
DB2 Universal database can be accessed from almost any client workstation over almost any
network.

Multi-platform support
DB2 Universal database is one of the most open database platforms available. It runs on the most
popular UNIX and Intel server platforms, including AIX, HP-UX, Solaris, Linux, NUMA-Q, OS/2,
and Windows. http://www-306.ibm.com/software/data/db2/udb/about.html

4. TEXAS A&M-SSI HUB

IBM DB2 -Menus
After you have installed DB2,these are various options you will see if you go to IBM DB2 in the
main programs. To reach the various GUIS, go to All ProgramsIBM DB2Various options.
Command Editor Development Center Control Center Health Center Configuration Assistant

5. TEXAS A&M-SSI HUB

The Command Editor
This tool allows the user to input SQL statements or DB2 commands in an interactive window and
see the results.
Type the SQL statements or DB2 commands here. The result can be seen in this part if the window

6. TEXAS A&M-SSI HUB

Development Center
Currently for class-projects you wont need to use the Development center as this is the tool that
the administrator uses it to create stored-procedure or UDF (User define function). It is used for
creating objects, the source code is copied on the server and the object is registered in the
catalog table. when a Java object is built, it is complied on the Client application.

7. TEXAS A&M-SSI HUB

Control Center
This is without a doubt the most important DB2 GUI tool. It provides you with a whole picture of your
instances and databases, and allows the user to perform most database operations in DB2.
T
R
E
E
S
T
R
U
C
T
U
R
E
Detailed information about the item selected. If you select
them you can further modify them.
More Wizard like help for the item selected.

8. TEXAS A&M-SSI HUB

Health Center
Use the Health Center GUI tool to set up thresholds that, when exceeded, will prompt alert
notifications, or even actions to relieve the situation. In other words, you can have the database
manage itself!

9. TEXAS A&M-SSI HUB

Configuration Assistant
If a user has DB2 UDB installed on the desktop in College Station, but would like to connect to a
DB2 Server in Germany. How can the user connect to this remote server? The Configuration
Assistant GUI tool can help with the set up and testing of the connection,
The Task Center

10. TEXAS A&M-SSI HUB

Creating a Database
To create a database there are
various options either one can create
a database from the Command
Centre or the Control Center. The
option from the Control center is easy
as it has a wizard that will guide
through the process.

Here the database creation through
the Control Center is explained.

Click on Databaseson the left
panel of the Control Center

Right click on Databasesand
choose create -> Standard.

Follow the panels from the
create database wizard.
Standard

11. TEXAS A&M-SSI HUB

Cont...
The wizard guides through the step by step process of creating the database, it asks for a
database name, location to store the database and the various options about the maintenance of
the data base.

12. TEXAS A&M-SSI HUB

Creating a Table
From the Control Center again we
would need three steps:
1.
Click on Tableson the left
panel of the Control Center.
2.
Right click on Tablesand
choose Create.
3.
Follow the panels from the
create table wizard.

13. TEXAS A&M-SSI HUB

Cont...
This is how the wizard for creating a table looks like, it will ask for information and the user just fills
in the blanks to create the columns in a table, long with the columns the user can also provide
information about the constraints, keys. Following this same procedure one can create aliases,
views, indexes, triggers, schemas, and so on.

14. TEXAS A&M-SSI HUB

Alter a table

To alter some of the characteristics of
a table, first specify which table
needs to altered. Thus, using the
Control center's right pane (contents
pane), follow these four steps:
1.
Click on Tableson the left panel
of the Control Center.
2.
Click on the table name that
needs to be altered.
3.
Right click on the table name
and choose Alter.
4.
Follow the 'Alter Table' panels.

With the Alter panel one can change
the data type, the keys , constraints
or the properties of the table.

15. TEXAS A&M-SSI HUB

Performing Queries

After creating a database and its table
the next step is to perform queries on
them. Queries like Insert, delete, update
or select and so on. Follow the steps
1.
In the Controlcenter select the
database from the left side panel 2.
Select table and it show all the
tables for the particular database
in the right side panel
3.
Select the table in the right hand
panel. 4.
Under the right panel there is an
option for Query, select it
Query

16. TEXAS A&M-SSI HUB

Cont...

This is the window that
comes up, you can type the
query in the top panel.

If you are using command
panel directly, remember to
connect to the database by
the command
connect to database
name

After typing the query press
the green arrow sign, to run
the query.

The result will be displayed
in a new tab window.

In the result set you can
delete or add an row in the
table, there are options of
committing and rollback
present too.
Type the query here
Result Window

17. TEXAS A&M-SSI HUB

Query Assist
Another easier way to frame
the queries is to use the SQL-
Assist.
1.
Click on the selected
tab, then click on SQL
Assist. 2.
This brings up an
interactive window
through which writing
queries becomes
easier.
SQL Assist
SQL Assist Window

18. TEXAS A&M-SSI HUB

Restricting Usage
To prevent unauthorized users
from messing up with the
database or tables, there is
provision for setting the
privileges granted to the users
1.
On the left panel of the
Control Center and within
the desired database tree,
click on User and Group
Objects -> DB Users.
2.
Click on the desired user on
the right panel of the Control
Center
3.
Right-click on the desired
user and choose change.
Here change the privileges

19. TEXAS A&M-SSI HUB

Accessing Data Through Java Applications
DB2 provides support for Sun Microsystems's Java Database connectivity (JDBC) API through a
JDBC driver that comes with DB2.

Include the
“import java.sql.*”
at the top of the source files.

For a Java Class, JDBC driver has to be loaded.
It is better to load it in the constructor.
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

After loading the driver connect to the specific database.
// connect to Database :database name, username n password to the database
// Here hub is the name of the database, for different users it could be different.
connection = DriverManager.getConnection("jdbc:db2:hub","db2admin","db2admin");

20. TEXAS A&M-SSI HUB

Example Java Class
import java.sql.*;
import java.util.*;
import java.lang.*;
public class Calendar
{
private String userName;
private Vector events;
public Calendar(Stringuser)
{
ResultSetrs;
Connection connection;
PreparedStatementgetRecords;
try
{
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
userName= user;
events = new Vector();
connection = DriverManager.getConnection("jdbc:db2:hub","db2admin","db2admin");
getRecords= connection.prepareStatement("Select* from administrator.Calendarwhere username = ?");
getRecords.setString(1,this.userName);
rs=getRecords.executeQuery();
while(rs.next())
{
CalendarEventtask =
newCalendarEvent(Integer.parseInt(rs.getString(1)),rs.getString(2),java.sql.Date.valueOf(rs.getString(3))
rs.getString(4),rs.getString(5),rs.getString(6));
events.addElement(task);
}
rs.close();
getRecords.close();
connection.close();
}
catch(Exceptione)
{
System.out.println(e.toString());
}
}
}

21. TEXAS A&M-SSI HUB

Glossary
Database:
A relational database presents data as
a collection of tables. The data in each table is
logically related, and relationships can be defined
between tables. Data can be viewed and
manipulated based on mathematical principles and
operations called relations (such as, INSERT,
SELECT, and UPDATE).

Tables:
A relational database presents data as a
collection of tables. A table consists of data logically
arranged in columns and rows (generally known as
records)

Table Space
: The physical space within a
databaseis organized into a collection of table
spaces. Each table space consists of a collection of
containers, each of which is an allocation of
physical storage (for example, a directory on a
machine, a physical file, or a device such as a hard
drive).

Schema:
A schema is a unique identifier used to
group a set of database objects. Most database
objects have a two-part object name, the first part
being the schema name and the second part is the
name of the object.

Index
: An index is a data access aid that can be
created on a table. It is an ordered set of pointers to
rows in a table. Each index is based on the values
of data in one or more columns in a table. An index
is an object that is separate from the data in the
table.

Instance:
An instance is a logical database
managerenvironment where you catalog databases
and set configuration parameters. Multiple
instances can be created on the same physical
server providing a unique database server
environment for each instance.

Alias:
An alias is an alternative name for a table,
view, or even another alias. These table-related
aliases are somewhat different from database
aliases. Assigning an alias to a database can avoid
potential client connection problems in
environments where different servers might have
databases with the same name.

Views:
A view is the result of a query on one or
more tables. A view looks like a real table, but is
actually just a representation of the data from one
or more tables. A view is a logical or virtual table
that does not exist in physical storage.

22. TEXAS A&M-SSI HUB

Conclusion
With this basic information, the users are ready to explore the more
complicated actions.
There are various automated tools for Database Maintenance, a
place to get help from could be

http://www106.ibm.com/developerworks/db2/library/techarticle/0 308chong/0308chong.html

To compile this tutorial, help was taken from the tutorial provided by
the IBM website.