************************** 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 to connect to a specific database, for example the database csc343h %db2 "connect to csc343h" Having connected to a database, you can type DB2 commands. For example, to create a table emp with two attributes name and age, you type %db2 "create table S1.emp (name char(20), age int)" Note that S1 is the schema name. To drop the table emp you type %db2 "drop table S1.emp" To list all the tables you created so far in schema S1, you type a db2 interpreter command as in: %db2 "list tables for schema S1" 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 S1.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 S1.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 csc343h Database Connection Information Database product = DB2/SUN 2.1.1 SQL authorization ID = AT343RAF Local database alias = csc343h db2 => create table s1.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 s1.emp values ('100','John Smith',40000, \ '200 College street') DB20000I The SQL command completed successfully. db2 => insert into s1.emp values ('200','Mary Smith',35000, \ '8 King College Road') DB20000I The SQL command completed successfully. db2 => select * from s1.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. Note that if no schema is specified (as in this example), the default schema with the same name as your user id will be used. 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') If my user id is jane, then I can see this table by issuing the command: list tables (or: list tables for schema jane) 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 s1.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. ******************* Bulk Loading Many Tuples in DB2**************************** To load a large data file, you may need to load the tuples in separate transactions to avoid overloading the log. You may use the COMMITCOUNT option of the IMPORT command, which performs a commit after a specified number of records are imported. For example: import from date of DEL commitcount 10000 insert into mytable would commit every 10,000 records. For smaller transaction logs, you may need to commit more frequently.