1 / 23

PROJECT OVERVIEW

This project overview discusses two projects involving defining and querying databases in SQL using IBM DB2, as well as web-based online access and updates of a movie database. The DB2 environment setup and usage instructions are provided, along with sample SQL commands and information on connecting to DB2.

djasmine
Download Presentation

PROJECT OVERVIEW

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

More Related