Discussion Slides for DB2

basesprocketΔιαχείριση Δεδομένων

31 Οκτ 2013 (πριν από 3 χρόνια και 11 μήνες)

87 εμφανίσεις

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

studb
l

1

lindbrook

studb
n

2

westholme

studb
w

Project DBMS: IBM DB2


IBM DB2 (also
Universal Database Server
)


Why DB2


Garner 2002 on RDBMS market share (new licenses)


IBM 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 /u/cs/class/cs143v/cs143vta/sqllib/db2cshrc


or put it into .cshrc to run automatically after login


bash:


source /u/cs/class/cs143v/cs143vta/sqllib/db2profile


or 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 OS command line: $db2
SQL command


SQL 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 used: (no semicolon at the end please)


connect 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 to sample1 [user
userid
] [using
passwd
]


If locally: you can ignore userid/passed


In 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:




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


IMPORT FROM datafile OF DEL

RESTARTCOUNT 100

MESSAGES import.msg insert INTO employee;

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") (on local)


Class.forName("COM.ibm.db2.jdbc.
net
.DB2Driver") (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 rs = stmt.
executeQuery
("SELECT * from employee");


For update/insert/delete/or DDL queries:


ResultSet rs = stmt.
executeUpdate
(“delete from employee
where empno=‘1’ ");


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.