Transcript Use DB2
PROJECT OVERVIEW
Hyun J. Moon and Carlo Zaniolo
University of California, Los Angeles
CS143, Fall 2004
Projects
Two Projects
Project1
Defining
and Querying Databases in SQL
Using IBM DB2
Techniques:
DB2, JDBC (Java Database Connectivity)
Project2
Web-based
Online Access of Databases: online
access/update of a movie database
Techniques:
DB2, JDBC, HTML, Java Servlet
Hot topics! Good for job hunting!
Start early, really early!
Project SEASNET Account
Each student is assigned a seas account for the
project (not your personal seas account)
Pick
it up in the break of discussion or office hours
The
login/passwd will be explicitly used in your project
Change
your passwd to anything except your private passwd
Change
your home directory permissions:chmod go-rxw dir
Project SEASNET Account (cont’d)
3 machines to login
landfair.seas.ucla.edu
lindbrook.seas.ucla.edu
westholme.seas.ucla.edu
To decide which machine you need to login:
LAST_2_DIGITS_OF_YOUR_STUDENT_ID mode 3
mod value
Machine name
Database name
0
landfair
studbl
1
lindbrook
studbn
2
westholme
studbw
Project DBMS: IBM DB2
IBM DB2 (also Universal Database Server)
Why DB2
Garner
IBM
2002 on RDBMS market share (new licenses)
34.6% ORCL 32.0% MSFT: 16.3% SYBASE: 2.6%
DB2
is cheap: at least 4 times cheaper than Oracle
DB2
is the standard
DB2
is free for education/research use
DB2
is small and simple
Setup DB2 Environments
DB2 environments like path for db2 libraries,
executable files, DB2 Java CLASSPATH
csh/tcsh:
source
or
/u/cs/class/cs143v/cs143vta/sqllib/db2cshrc
put it into .cshrc to run automatically after login
bash:
source
or
/u/cs/class/cs143v/cs143vta/sqllib/db2profile
put it into .bashrc to run automatically after login
Test DB2
Set your db2 environments first
connect to a database:
$
db2 connect to sample1 user yourlogin
type
$
your passwd
db2 “select * from cs143vta.staff”
Other
sample tables:
album,
cl_sched, department, emp_act, emp_photo,
emp_resume, employee, in_tray, org, project, sales, staff
Get help on DB2:
http://www.seas.ucla.edu/db2/
Use DB2
Many ways:
command
line
From
applications: embedded SQL, JDBC, Stored
Procedures, etc.
Command line:
DB2
shell: $db2; then input SQL commands dynamically
Through
SQL
OS command line: $db2 SQL command
script files: put SQL scripts in a script file, and run as:
$db2
–tf <filename>
Use DB2: DB2 Command Line
DB2 command line: enter $db2 <enter>
You
can enter SQL commands + DB2 system
commands
enter
? for help, or ? cmdname for a specific command
commonly
connect
used: (no semicolon at the end please)
to dbname (connect to database dbname)
list
tables (list tables of your schema)
list
database directory (list all database directory)
connect
reset (drop your connection to the database)
terminate
(terminate current session and exit db2 interactive
mode. Always leave your session this way!)
echo
abc (make comments to your codes)
Use DB2 (cont’d)
Similar as db2 command line except beginning with “db2”
$db2 connect to dbname
$db2 “select * from cs143vta.staff” (SQL needs to be quoted on UNIX)
SQL script file
put SQL scripts to a file query.sql seperated by semicolons:
connect to studbn user xxx using yyy;
echo test sql script;
create table emp(empno int, name varchar(32) );
insert into emp values(10, ‘Joe Doe’);
select * from emp;
connect reset
terminate;
Run as: $db2 –tf query.sql
Connect to DB2
DB2 has 1 or more instances: cs143vta
Each instance can have databases
3
project databases: studbl, studbn, studbw
To access a database, the user must have the
privilege to connect to that database
For
DB2, a user to a database must also be a user to the OS
Connect to a database named sample1:
connect
If
locally: you can ignore userid/passed
In
to sample1 [user userid] [using passwd]
our project, you will connect remotely
Always run “terminate” after you finish
Schema
Schema: a named collection of objects such as
tables. Such objects have to be unique inside the
schema
When you create an object (e.g., a table), a default
schema name is automatically created
e.g.,
create table test(a int); a schema named your login, e.g.,
cs143vxy is automatically created.
You
can refer to the table as cs143vxy.test, or test (schema
cs143vxy, your loginname is implied)
A Sample Table
A sample employee relation:
employee(id, firstname, lastname, gender, hiredate, deptno, title,
salary)
connect to studbn user xxx passwd yyy;
drop table employee;
create table employee (
id char(8),
firstname varchar(24),
lastname varchar(24),
sex char(1),
hiredate date,
deptno char(8),
title varchar(24)
not null,
salary integer);
insert into employee values(‘10001’, ‘Joe’, ‘Doe’, ‘F’, ’10/02/2003’, ‘d01’,
‘Engineer’, ‘50000’);
Run as: $db2 –tf query.sql
DB2 Built-in Data Types
Date/Time:
Date(10/02/2003), Time(17:12:25), Timestamp(2003-10-02-
17.12.32.963078)
String:
char
(fixed length), varchar(variable length), clob( character
LOB), blob(binary LOB). e.g., char(4): n <254. varchar(32):
n<4000 clob(2M)
Numeric:
REAL,
DOUBLE
SMALLINT(16bits),
INTEGER(32bits), BIGINT(64bits),
DECIMAL. e.g., DECIMAL(5,2): 5: scale, 2 is the # of digits after
the decimal point
Maximum length in a row: 4005 for 4KBytes pages
Load Data
Import example:
IMPORT FROM datafile OF DEL
RESTARTCOUNT 100
MESSAGES import.msg insert INTO employee;
Normally data files can be:
ASC
(non-delimited ASCII format)
DEL
(delimited ASCII format, default delimiter: ,)
IXF
(integrated exchange format)
Refresh data:
I.
drop tables + create tables + load data
II.
delete from emp + load data
DB2 Setup
You can work on your project on your own DB2 and copy it
over to seas account, but make sure it works!
DB2 download:
http://www.ibm.com/db2, download
For windows, when you setup, you need to create an
administration account, e.g., db2admin
To start up db2, run with an Administrator account:
c:>db2cmd (setup db2 environment)
c:>db2start (start up db2 instance)
c:>db2jstrt (start up java connection)
c:>db2admin start (startup admin server so you can run DB2 control
center)
c:>db2cc (Control Center, a GUI for administration)
c:>db2stop (stop db2)
c:>db2admin stop (stop db2 admin server)
Project 1
Defining and Querying Databases in SQL
Using IBM DB2
Due: Noon Tuesday, Nov 2
Tasks
Define
and create tables
Load
data into the tables
Write
queries as SQL scripts
Write
queries with JDBC
Start early!
JDBC
JDBC (Java Database Connectivity) API: a
standard SQL database access interface
same API
for all databases
Environment (already setup if you set db2 env)
Java
and javac
db2java.zip
(jdbc lib)
Sample files:
/w/class.01/cs/cs143v/cs143vta/sample-code
Establishing a Connection
Load the vendor specific driver:
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver")
Class.forName("COM.ibm.db2.jdbc.net.DB2Driver")
(on local)
(remote)
Make the connection
connection url: e.g., jdbc:db2://national.seas.ucla.edu/studbn
username, passwd
Connection con = DriverManager.getConnection(url, userid, passwd);
Creating JDBC Statements and Send Queries
A JDBC Statement object is used to send your
SQL statements to the DBMS
Statement
stmt = con.createStatement();
Execute the query:
For
select queries:
ResultSet
For
rs = stmt.executeQuery("SELECT * from employee");
update/insert/delete/or DDL queries:
rs = stmt.executeUpdate(“delete from employee
where empno=‘1’ ");
ResultSet
Accessing ResultSet
rs.next() returns false when there are no more
rows
while
(rs.next()) {
String
a = rs.getString(1);
String
str = rs.getString(2);
}
Help: http://java.sun.com/j2se/1.4.2/docs/api/
accessDatabase.java
Academic Honesty Policy
You are expected to submit your own original work
It is encouraged to discuss the project with peer
students, provided that you indicate their names in
your submission.
All code must be written up independently.
Any suspected academic dishonesty will be
reported to the Dean of Students for disciplinary
action, which may result in suspension or
dismissal from the University.