A Guide to DB2 on CDF

                 by A. Shrufi, A. Bonner and D. Rafiei
                            6 March 2000

                 with minor modifications by L. Libkin
                          12 February 2001

             updated by Rama Natarajan for CSC 2509S - 2004
                          10 February 2004

          further updated by Kevin Brewer for csc2509h - 2006
                           5 February 2006

******************* Setting up your db2 environment ********************

When you log in, you must set the environment variable DB2INSTANCE to
"db2inst1".  So you should add the following line to your .cshrc file:

		setenv DB2INSTANCE db2inst1



The CDF administrators have set up the following db2 account for this course:

        db2srv1.cdf.toronto.edu

Each student in this course has been given permission to connect an individual
database called "c2509hXX".  To connect to the database, logon to db2srv1. 
For example, my db is c2509h25.

skywolf:~% ssh db2srv1.cdf.toronto.edu
The authenticity of host 'db2srv3.cdf.toronto.edu (128.100.31.247)' can't be established.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'db2srv3.cdf.toronto.edu,128.100.31.247' (RSA) to the list of known hosts.

db2srv3:~% db2 "connect to c2509h25"

   Database Connection Information

 Database server        = DB2/LINUX 7.1.0
 SQL authorization ID   = T3BREWES
 Local database alias   = C2509H25

************************** Running db2 **************************************

You can run db2 in 2 modes: 1) system-level mode 2) interactive mode

Let us look at the system-level mode first.
You can run db2 commands from the unix system prompt by prefixing the
commands with db2. The general format is: (assuming % is your system
prompt)

      %db2 "sql statement"
      %db2 'db2 interpreter commands'

Note that you can enclose the commands in either double or single
quotes.  If a command succeeds, db2 will display a message to that
effect.  The first command you give should be to connect to a
particular database:

       %db2 "connect to c2509hXX"

Having connected to a database, you can give DB2 commands.  For
example, to create a table emp with two attributes name and age, you
type

       %db2 "create table emp (name char(20), age int)"

To drop the table emp you type

       %db2 "drop table emp"

To list all the tables you created so far, you type a db2 intrepreter command
as in:
       %db2 "list tables"

To disconnect from the database once you're done(not generally explicitly needed, 
but not a bad idea for the sake of safety) type:

	 %db2 "disconnect c2509hxx"

The allowable datatypes include:

  Data Type                 Byte Count                  Comments
  =========                 ==========                  ========
  integer                      4
  smallint                     2
  double                       8
  decimal(p,s)               (p/2)+1, p is precision    (1<= p <=31)
  char(n)                     n                         (1<= n <=254)
  varchar(n)                  4 to n+4                  (1<= n <=4000-4)
  date                        4                         in packed decimal
  time                        3                         in packed decimal
  timestamp                   10        (date time msec)in packed decimal

You can get help on the db2 interpreter commands by using the ? command,
for example to see a list of all db interpreter commands, you type

       %db2 "?"

To get help on the help command you type;

       %db2 "? help"

Please pay attention to the space between ? and help. This space is NECESSARY.

Finally, an important command is the one you use to run your SQL
statements from a script and dump the output into a file. To do this
you type:

      %db2 -l history -f input_file -r out_file

This will instruct db2 to read and execute your SQL statements from
the file named input_file and write the output to a file named out_file.
It will also write db2 system messages to the file named history.
For your assignments, you may need to submit all 3 files as part of your
hand-in.

To get help on the current option settings, you type

     %db2 "? options"

Note the following things:

(1) The files history and out_file can be used in multiple runs of db2
since they are always appended to.

(2) When you prepare an input_file (containing the SQL and db2
interpreter statements), you can use -- (double hyphen) to prefix your
comment lines. For example, you can have something like this:

     -- this query select all employees from emp table
        select * from emp

(3) You can use the backslash (\) character for line continuation in
all db2 SQL statements. For example, the above select can written as:

         select * \
         from emp

Again please note the space before the backslash. It must be there!


***************************** Interactive Mode *******************************

You can run db2 in interactive mode by typing db2 from the system prompt

             %db2
             db2=>

Note that db2 display db2=> as its prompt.

Once in interactive mode, you can execute db2 interpreter commands and
SQL statements in the same way you do in system-level mode.

Note the following
- Lines are continued with the backslash as well.
- lines starting with -- are treated as comments
- Type terminate to exit the db2 interactive interpreter.
  (Typing quit also works, but please do not use it,
   as it causes a "dirty" exit that can clutter up the
   operating system with background processes.)
- Though interactive mode offers less typing per line, 
  it's less keyboard-friendly for many; you can't access 
  previous commands using the arrow keys, and backspace has
  its problems as well.