Getting started on DB2 at UCLA

• Overview
  
  
• Logging In to DB2
  
  
• Changing Your Password
  
  
• Creating a Table
  
  
• Inserting Tuples
  
  
• Getting the Value of a Relation
  
  
• Getting Rid of Your Tables
  
  
• Getting Information About Your Database (nothing yet)
  
  
• Quitting DB2
  
  
• Executing DB2 from a File: Using the Command Line Processor
  
  
• Editing Commands in the Buffer (nothing yet)
  
  
• Recording Your Session (nothing yet)
  
  
• Help Facilities
  
  

The DB2 Bulk Loader: Using the DB2 Bulk Loader

 Two examples are to be added soon!

Non-SQL2 Features of DB2

DB2 Dates and Times

All CS240A students should log in at least once and change their password for safety.

Logging into DB2

Right now the DB2 UDB server is running on koi.cs.ucla.edu. A sample session could be

• Log into koi.cs.ucla.edu as a UNIX user;
• Be sure the following environment variables are set as
  C shell:

  	setenv DB2INSTANCE db2inst1
  	setenv DB2OPTIONS -t
       

  Bourne shell and Korn shell:

  	export DB2INSTANCE=db2inst1
  	export DB2OPTIONS=-t
       

• Be sure the directory "/opt/IBMdb2/V6.1/bin" is included in your PATH;
• Run "db2" command, db2 prompt "db2 => " is shown.
  A user may input any valid DB2 command or DB2 SQL statement after prompt. However, a user must connect to a database before any query or command can be issued. Therefore, type "connect to sample;" at first, this command will lead you to the database "sample" which is created for the class.
  e.g., among DB2 commands "list tables;" is a useful command to display all existing tables in current database
• Now you can input any valid DB2 SQL statement after prompt. e.g.,

       CREATE TABLE test
  	    (name varchar(10) NOT NULL,
  	     PRIMARY KEY(name));
       INSERT INTO test VALUES('cs240A');
       SELECT * FROM test;
       

• Finally, issue "quit;" or "terminate;" command to exit from DB2.

Since tables are the basic objects that are used to store the information in V2, the most fundumental data
definition statement is the CREATE TABLE statement. The basic job of a CREATE TABLE statement is to
specify the name of the table to be created and the names and datatypes of all its columns. In addition, the statement
can optionally specify that certain columns of the table do not accept null values and that one or more columns
constitute the primary key of the table. Primary key columns never accept null values, and the values of the
primary key columns uniquely identify a row of the table. CREATE TABLE statement also has more advanced
features that are not discussed here.

Examples:

CREATE TABLE parts
    (partno Char(4) NOT NULL PRIMARY KEY,
     description Varchar(20),
     qonhand  Integer,
     qonorder Integer);
CREATE TABLE quoations
     (suppno Char(3) NOT NULL,
      partno  Char(4) NOT NULL,
      price Integer,
      responsetime Integer,
      PRIMARY KEY (suppno, partno) );

Inserting Tuples

The purpose of the INSERT statement is to insert one or more rows into a table or view. The rows to be inseted can be specified as a literal table or can be derived from the database by means of a query. Users of SQL are familiar with the use of a VALUES clause to insert a single row into a table, as in the following example:

 INSERT INTO quotations(suppno, partno, price, responsetime)
    VALUES ('S59', 'P227' , 175, 8);

INSERT INTO orders(suppno, partno, quantity, orderdate)
  VALUES ('S59', 'P227', 100, DEFAULT),
                  ('S59', 'P231', 250, DEFAULT);

INSERT INTO orders(suppno, partno, quantity)
  VALUES ('S59', 'P227', 100),
                  ('S59', 'P231', 250);

  INSERT INTO INACTIVE (suppno)
      SELECT suppno
      FROM suppliers
 EXCEPT
      SELECT suppno
      FROM quotations;

Getting the Value of a Relation

When a query is used asa top-level SQL statement, we call it a SELSECT statement. A select statement can be submitted to an interactive interface such as the CLP (Command Line Processor), which will execute it and display the result. In addition to the normal query clauses, a SELECT statement may have an additional clause that is meaningful only for top-level queries. That caluse is called an ORDER BY clause and is used to impose an ordering on the result set, controlling the order in which the result rows are displayed. The result set of a query can be ordered by the values of one or more of its columns, in either ascending or desending order. Each of the columns in the ORDER BY clause must correspond to one of the expressions in the SELECT clause. These expressions can be identified in several ways, as shown in the following examples. If a simple column name is selected, that column name can be used in the ORDER BY clause. The following query lists all the quotations for part number P231 that have a response time of less than ten days, in order by price from the least expensive to the most expensive.

    SELECT  suppno, price
    FROM  quotations
    WHERE partno = "p231"
    AND responsetime < 10
    ORDER BY price;

    SELECT s.name, q.partno, q.price, i.description
    FROM suppliers s, quotations q, parts i
    WHERE s.suppno = q.suppno
    AND q.partno = i.partno
    ORDER BY s.name, q.partno;

   SELECT partno, qonorder+qonhand AS totalq
   FROM parts
   ORDER BY totalq DESC;

  SELECT partno, avg(price)
  FROM quotations
  WHERE partno IN
       (SELECT partno
        FROM parts
        WHERE qonhand = 0)
 GROUP BY partno
 ORDER BY 2 DESC;

    SELECT suppno
    FROM orders
    WHERE partno = 'P221'
UNION ALL
    SELECT suppno
    FROM  quaotations
    WHERE partno = 'P221'
    AND responsetime <= 10
ORDER BY suppno;

Getting Rid of Your Tables

Tables, views, and other object can be removed from the database by means of a DROP statement. Examples:

     DROP TABLE accounts.receivable;
     DROP VIEW overdue;
     DROP PACKAGE  payroll;

Getting Information About Your Database

Quitting DB2

Just issue "quit;" (or "terminate;") command.

Executing DB2 from a File: Using the Command Line Processor

CLP is the basic tool provided by V2 for interactively executing SQL statements and system commands. The CLP can take its input either from the keyboard of from a file, and can direct its output either to your display or to a file. The CLP is invoked by typing the command db2 at your operating system prompt or, if your operator has a GUI, by double-clicking on the "Command Line Processor" icon in the "DB2" group. The three forms of the db2 command are as follows:

1. If you follow the word db2 with an SQL statement or system command, the CPL will execute that statement or command and display the result.
2. If you follow the word db2 with the option -f filename, the CLP will take its input from the named file, processing all the commands in the file one after another. For example, the following command directs the CLP to process all the statements in the file named monday.clp:

      db2 -f monday.clp
   

3. If you simply type db2 followed by neither the -f option nor a command, the CLP will go into interactive mode and will prompt you to enter SQL statements or commnads, one at a time, at the keyboard. The simplest form of the db2 command is as follows:

  db2

  db2 =>

Editing Commands in the Buffer

Recording Your Session

Help Facilities

At any time during a CLP session, you can use the "?" command to browse the online documentation that is provided with your V2 system. To obtain help about a given subject, simply type HELP followed by a word or phrase, such as the following:

  ? CREATE TRIGGER;

Basic SQL Features

Indexes

An index is an access aid that can be created on a table, using one or more columns of the table as the key columns of the index. You can create as many indexes on the table as you like, using the various combinations of columns as keys. Finding an optimum set of indexes to mainatain in your database is an art that you will learn by experience. Indexes are created by the CREATE INDEX statement, as the following examples illustrate:

     CREATE INDEX  i1 ON suppliers(name);
     CREATE UNIQUE INDEX i2 ON quotations(partno, suppno);
     CREATE INDEX i3 ON quotations(partno ASC, price DESC);

Data Types

Basic Single-byte SQL Datatypes: Smallint, Integer, Decimal(p,s), Double, 
                  Char(n), Varchar(n), Date, Time, Timestamp.

Basic Double-byte SQL Datatypes:  Graphic(n), Vargraphic(n).

Large Objects: Blob(Binary Large Object), Clob(Character Large Object), 
               Dbclob(Double-Byte Character Large Object).

Constraints

In V2, each constraint is associated with a specific table in the database and protects the validity of data values in that table. Name of the constraint is used by the system in error messages whenever the constraint is violated. If you create a constraint and do not give it a name, the system will generate a name for the constraint automatically.

Embedded SQL

The host programming languages supported by V2 are C, C++, COBOL, FORTRAN, REXX.

Dates datatype

DB2 DATE datatypes are very similar to the SQL-92 standard:

Date             Consists of a year, month, and day.
Time             Consists of an hour, minute, and second.
Timestamp        Consists of a year, month,day, hour,minute,second, and microsecond.