******************* Setting up your db2 environment ******************** You can only run db2 on the machine fissure for the time being. An instance named cscd34 is currently created for the course. Before running db2, you need to source the file ~db2d34/sqllib/db2cshrc. You can do that creating a new .mycshrc file and adding the following line to that file: source ~db2d34/sqllib/db2cshrc This will enable you to access both db2 and the online help using db2help. ************************** 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 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 %db2 "connect to cscd34" Having connected to a database instance, you can type 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" 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) 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 quit to exit the db2 interactive interpreter. - Make sure you "connect reset" before quit, otherwise it causes a dirty exit. ******************** A sample session in interactive mode ******************** %db2 db2 => connect to cscd34 Database Connection Information Database product = DB2/SUN 2.1.1 SQL authorization ID = RAFIEI Local database alias = CSCD34 db2 => create table emp ( emp_no char(4) not null, \ db2 (cont.) => name char(15), \ db2 (cont.) => salary int, \ db2 (cont.) => address varchar(20)) DB20000I The SQL command completed successfully. db2 => insert into emp values ('100','John Smith',40000, \ '200 College street') DB20000I The SQL command completed successfully. db2 => insert into emp values ('200','Mary Smith',35000, \ '8 King College Road') DB20000I The SQL command completed successfully. db2 => select * from emp EMP_NO NAME SALARY ADDRESS ------ --------------- ----------- -------------------- 100 John Smith 40000 200 College street 200 Mary Smith 35000 8 King College Road 2 record(s) selected. db2 => -- type your SQL commans db2 => connect reset DB20000I The SQL command completed successfully. db2 => quit DB20000I The QUIT command completed successfully. ************************* Some SQL Examples ********************************** Here is a list of SQL statements that create a table called mytable and insert two tuples into it. create table mytable (name char(20), age int, title char(20)) insert into mytable values ('Bill', 95, 'manager') insert into mytable values ('mark', 45, 'director') To select all tuples in mytable select * from mytable To select the tuple(s) with the title manager, we say select * from mytable where title = 'manager' To examine the structure of mytable, you can use the describe statement as in the following: describe select * from mytable To delete all the rows in the table, we can use the following delete from mytable To drop the table mytable entirely, we say drop table mytable Note the following: 1- You must enclose all SQL string constants in single quotes 2-Strings are case-sensitive, i.e. 'Th' is not equal to 'th' 3-If You want to use regular expressions in an SQL statement, e.g. you can use the like operator. For example, if you wish to search for all name which start with the 'Thod' prefix, you type Select * from emp where name like 'Thod%' Notice the use of the percent sign (%) to indicate zero or more characters. 4-If you disallow null values in the columns, you must specify this by using the condition. The default is to allow nulls. ************************* Bulk Loading in DB2 ********************************** To import data into your db2 tables from an ASCII file, you issue the following: import from data of DEL messages msg.txt \ insert into mytable Where, - data is the name of ASCI file. - The DEL refers to a delimited ASCI file, i.e. fields are separated by commas. - msg.txt is the file where system messages will be written to. It is optional, so you can eliminate it. - Example, suppose we have table people(name char(20), age integer) - Then our data file may look like this: aaa,75 bb,85 cccc,15 ddd,120 dfff, 16 -Note you should not have any blank lines; otherwise you will have null columns in your table.