CS%20433%20DB2%20Overview

1
CS 433DB2 Overview

• Client Access, Queries, Stored Procedures, JDBC

9/12/01 Jeff Derstadt Megha Batra
2
Administration

• Project Proposals
• Due 09/14/01 by 5PM
• Email them to TAs
• Please register your group on the web
• http//www.cs.cornell.edu/courses/cs433/2001fa/

3
Client Configuration

• Must setup a client-server connection
• Server name
• egret.csuglab.cornell.edu
• Port
• 50000
• Database name
• Your group (g7, g23, etc.)

4
Client Configuration

• Lets configure our client!

5
Command Line Queries

• Connect to database
• Connect to ltdatabase_namegt user ltuser_namegt using
  ltpasswordgt
• Issue queries
• CREATE TABLE lttablegt (ltcolumn_namegt ltdatatypegt,
  )
• INSERT INTO lttablegt values ()
• SELECT ltcolumnsgt from lttablegt

6
Command Line Queries

• Lets issue some queries!

7
Stored Procedures

• Definition
• A set of SQL and programming language (C/Java)
  statements that are compiled into a named
  database object that can be invoked by a user
  application
• Examples
• For each sailor with a rating less than 5, insert
  her into the NeedsTraining table, and email her
  requesting that she sign up for additional
  instruction
• Converting relational tables to Xml

8
Stored Procedures (2)

• Advantages
• Can embed SQL in a native programming language
• Executed on the database server no network delay
• Compiled procedures can be invoked by any user
  application
• Provides consistent functionality (dont need to
  rewrite SQL inserts, can just call a stored
  procedure)

9
Java

• DB2 allows you to write stored procedures in Java
• Java is an easy language
• - Must use some sort of bridge between the
  native DB2 code and the Java Virtual Machine to
  transfer data from a relational table to the
  stored procedure
• /- JDBC provides this bridge extra copy, but
  easy access

10
DB2 to Java
DB2 (C)
11
JDBC java.sql.

• Using JDBC adds additional costs
• Copy from DB2 to JDBC ResultSet object (2)
• Copy from ResultSet object to another Java
  variable (sometimes at 3)
• However
• JDBC makes issuing SQL queries and gathering
  results very easy

12
JDBC ResultSet

• Provides a simple cursor object
• A cursor is a set-of-records abstraction that
  allows you to move from one row to the next and
  access the columns

SID
NAME
rs.next()
rs.getString(2)
12
Sarah
13
Joe
15
Megha
13
Stored Procedures

• Lets write a stored procedure that converts a
  table to Xml!

14
Questions ??