CPS 116 (Fall 2007):
Introduction to Database Systems

Index

• General information
• db2 command-line processor
• Loading a database
• Querying metadata
• Bag deletion

General information

The version of the DB2 installed on rack040 is 9.1 (Enterprise Edition). Complete DB2 manuals (PDF and HTML versions) are available from the IBM Web site. SQL Reference (Vol. 1 and Vol. 2) and Command Reference are probably the most useful SQL references.

A good DB2 book is A Complete Guide to DB2 Universal Database, by Don Chamberlin, one of the creators of SQL and, more recently, XQuery.

db2 command-line processor

Interactive mode

After logging onto rack040, you can start the DB2 command-line processor in the interactive mode by entering the following command in your login shell:

  db2 -t

When you are inside the DB2 command-line processor, you will get a prompt that looks like the following:

  db2 =>

To connect to our database (which is named dbcourse), type:

  connect to dbcourse;

You can now use any SQL commands (CREATE, DROP, SELECT, INSERT, DELETE) to manipulate the database. Another useful DB2 command is "list tables;", which prints out a list of all tables you have created.

When you are done, type the following to disconnect from our database:

  disconnect dbcourse;

  quit;

The command-line processor does not provide any advanced command-line history and editing features. You can work around this problem in a number of different ways:

• You can start the command-line processor from your login shell using the following command instead:

    rlwrap db2 -t
  

  rlwrap is a nice tool that provides advanced command-line history and editing support to any command following it.
• If you are an avid emacs (a very powerful and extensible open-source editor) user, you can start a shell inside emacs (by issuing M-x shell inside emacs) and then run db2 -t inside this emacs shell. Then, you can recall and edit SQL commands, browse and search the output, save the content of the shell buffer, etc., using standard emacs commands.
• You can also use db2 to run a single SQL command directly from your login shell, as described below. This method of using db2 allows you to use the command-line history and editing features of your Unix login shell (bash by default).

Immediate mode

You can use db2 to execute a DB2 command or SQL statement directly from your Unix login shell. You specify the command/statement on the same command-line as db2, e.g.:

  db2 connect to dbcourse
  db2 "select * from beer"
  db2 disconnect dbcourse

This method of invoking db2 is also very useful when writing shell scripts. You can see some examples of this usage in /home/dbcourse/examples/db-beers/setup.sh. Sometimes you might want to use an extra option -v to have db2 echo the command/statement back, which is helpful in debugging.

Batch mode

You can write multiple DB2 commands and SQL statements in one text file (say, named FILE), and then use the command db2 -tvf FILE to execute them all. Use ";" to signal the end of each command/statement in the file. You will see examples of this usage in /home/dbcourse/examples/db-beers/setup.sh, where files create.sql and drop.sql are executed in the batch mode.

Loading a database

You can find an example of loading a database from data files in /home/dbcourse/examples/db-beers/. First, you need to prepare the data files. The example data files in data subdirectory use the delimited ASCII format (or DEL, in DB2 terms). Each line of the file represents a row, with columns of the row separated by commas. Then, use the IMPORT command to load tables from data (see setup.sh for syntax). Errors will be written to a specified message file. You should always check the message file after loading.

What if some of the data values contain commas? In this case, you should use a different column delimiter, e.g., |. The appropriate command would be "IMPORT FROM DATA_FILE OF DEL MODIFIED BY COLDEL| MESSAGES MESSAGE_FILE INSERT INTO TABLE_NAME".

Querying metadata

To see a list of all tables you have created, use the DB2 command "list tables". To see the schema of a particular table R, use the DB2 command "DESCRIBE SELECT * FROM R".

DB2 defines a set of catalog views that provide schema information about a database. For example, "SELECT tabname FROM syscat.tables WHERE tabschema = 'JUNYANG'" returns names of tables in schema JUNYANG, which is the default schema for user junyang. Note that by default, all schema object names are capitalized. Besides syscat.tables, there are other catalog views that allow you to keep track of all your constraints, triggers, views, indexes, stored procedures, or any other schema objects. For more information, check out the appendix in SQL Reference titled "Catalog Views", as part of the DB2 manual collection.

Bag deletion

Although standard SQL supports bag difference (EXCEPT ALL), there is no convenient way to delete rows from a table with bag semantics. For example, suppose that table R(A,B) has four identical rows (1,1). How do we delete two copies of (1,1) from R? The following statement

DELETE FROM R WHERE A=1 AND B=1;

DELETE FROM
  (SELECT * FROM R WHERE A=1 AND B=1
   FETCH FIRST 2 ROWS ONLY);

CREATE VIEW RR(A,B,X) AS
  SELECT A, B, ROW_NUMBER() OVER (PARTITION BY A, B)
  FROM R;
DELETE FROM RR
  WHERE A=1 AND B=1 AND X<=2;