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 csc343h"

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"

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.)