DB2 SQL Programming Notes


Index


General information

Complete DB2 manuals are available from the IBM Web site. SQL Reference and Command Reference probably will be the most useful documents. The version of the DB2 installed on rack40 is 7.2 (Enterprise Edition).

A good DB2 reference 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

db2 -t runs the command-line processor in the interactive mode. You can enter single- or multi-line SQL statements that end with the default statement termination character ;. However, the interactive command-line processor can be difficult to work with because it does not provide any advanced command-line editing features.

Another method is to follow db2 directly with the command you wish to run, e.g., db2 connect to cps216 or db2 "select * from beer". Here, you do not need to end the command with ; if you do not use the -t option of db2. Note that you may need to enclose SQL command strings in quotes to prevent the UNIX shell from interpreting them. This method of invoking db2 allows you to use the command-line editing features of the UNIX shell, and is also useful when writing shell scripts. You will can see an example of this usage in ~cps216/examples/db-beers/setup.sh.

My favorite trick is to start a shell in emacs (M-x shell) and run db2 -t inside this emacs shell (you might need to set up your DB2 environment in this emacs shell). Then, you can recall and edit SQL commands, browse and search the output, etc., using the standard emacs keys.

Finally, you can use db2 -tf <filename> to executed a file named <filename> containing multiple SQL commands. You will see an example of this usage in ~cps216/examples/db-beers/setup.sh as well.


Loading a database

You can find an example of loading a database from data files in ~cps216/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 <datafile> OF DEL MODIFIED BY COLDEL| MESSAGES <messagefile> INSERT INTO <table>.


Querying metadata

To see the schema of a table R, use the SQL 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.


Triggers

Syntax for creating triggers in DB2 differs slightly than the standard SQL syntax presented in lecture. Below is the syntax diagram.

>>-CREATE TRIGGER--trigger-name----+-NO CASCADE BEFORE-+-------->
                                   '-AFTER-------------'
 
>-----+-INSERT-----------------------------+--ON--table-name---->
      +-DELETE-----------------------------+
      '-UPDATE--+------------------------+-'
                |    .------ , -------.  |
                |    V                |  |
                '-OF----column-name---+--'
 
>--+----------------------------------------------------------------+--->
   |                                                                |
   |              .----------------------------------------------.  |
   |              |                                              |  |
   |              V                                              |  |
   '-REFERENCING--+---+-OLD-------+--+----+--transition-variable-+--'
                      |           |  |    |
                      +-NEW-------+  '-AS-'
                      |           |
                      +-OLD_TABLE-+
                      |           |
                      '-NEW_TABLE-'
 
>-----+-----FOR EACH ROW--------+--MODE DB2SQL--------------->
      |                         |
      '-----FOR EACH STATEMENT--'
 
>--+-------------------------------+------------------------->
   '-WHEN--(--search-condition--)--'
 
>-----+-triggered-SQL-statement--------------------------------+-|
      |               .-------------------------------.        |
      |               V                               |        |
      '-BEGIN ATOMIC-----triggered-SQL-statement--;---+---END--'
DB2 places the following restrictions on triggers:

By signaling an error in the trigger body, you can abort the transaction that causes the trigger to fire. For example, to prevent deletions from a table R, you can define a trigger as follows:

CREATE TRIGGER NoDeletionsFromR
NO CASCADE BEFORE DELETE ON R
FOR EACH ROW MODE DB2SQL
SIGNAL SQLSTATE 'Z0001' ('No deletions from R');
The state specified in a SIGNAL statement must be a string with exactly five characters. Certain states have been reserved by the SQL standard and/or DB2, but you should be fine as long as you choose a state whose first character is a digit between 7 and 9 or a letter between I and Z, inclusive.

If a trigger action contains multiple SQL statements, these statements should be enclosed by BEGIN ATOMIC and END, and each statement should be terminated by ;. In this case, you should use a statement termination character other than ; outside the trigger. For example,

CREATE TABLE S(A INT)@
CREATE TRIGGER STrigger
  AFTER INSERT ON S
  FOR EACH STATEMENT MODE DB2SQL
  BEGIN ATOMIC
  UPDATE S SET A=A+1;
  UPDATE S SET A=A+1;
  END@
INSERT INTO S VALUES(1)@
To use @ as the new statement termination character, you need to run either db2 -td@ (if you want to type in the above commands interactively) or db2 -td@ -f <filename> (if the above commands are stored in a file named <filename>). The result of running the above commands is a table with a single row with value 3.