PROJECT OVERVIEW

1. PROJECT OVERVIEW Hyun J. Moon and Carlo Zaniolo University of California, Los Angeles CS143, Fall 2004

2. Projects • Two Projects • Project1 • Defining and Querying Databases in SQLUsing 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!

3. 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

4. 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

5. 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

6. 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

7. 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/

8. 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>

9. 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)

10. 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

11. 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

12. 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)

13. 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

14. 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

15. 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;

16. 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)

17. Project 1 Defining and Querying Databases in SQLUsing IBM DB2Due: 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!

18. 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

19. 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);

20. 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’ ");

21. 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/

22. accessDatabase.java

23. 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.