DB2 has been installed on the SEASnet machines, specifically on landfair.seas.ucla.edu, lindbrook.seas.ucla.edu, westholme.seas.ucla.edu.
To decide which machine you need to login:
LAST_2_DIGITS_OF_YOUR_STUDENT_ID mode 3. According to the
value, you will be assigned as follows:
|
mod value |
Machine name |
Database name |
|
|
0 |
landfair.seas.ucla.edu |
studbl |
|
|
1 |
lindbrook.seas.ucla.edu |
studbn |
|
|
2 |
westholme.seas.ucla.edu |
studbw |
|
If you choose the wrong machine, the grader may not be able to correctly grade your projects.
DB2 has to know some details each time you get underway. You have to set certain environment variables to certain values in order to pass DB2 this information.
If you are using the csh shell, you can do this using the following command:
$ source /u/cs/class/cs143v/cs143vta/sqllib/db2cshrc
An alternative to having to do this "sourcing" step by hand each time is to set things up so it is done automatically for you. To do this, with your favorite editor, edit your .cshrc file and add the "sourcing" line somewhere near the end of the file. Then you can now work with DB2 immediately each time without the "sourcing" step.
If you are using the bash shell
$ source /u/cs/class/cs143v/cs143vta/sqllib/db2profile
Note that if you open a new window, you need to do the "sourcing" step again for that new window, unless you’ve had it in your configuration file (.cshrc for csh shell).
Students are each assigned a machine, database, and two ports from their SEASnet accounts. For assigned machine and database, please refer to Logging in. Port numbers are used for Project 2 and will be assigned later.
DB2
Documentation
DB2 SQL Reference
Message Reference
IBM DB2 Universal
Database Version 8.1 Trial code
(You can always work on your own DB2 and copy your scripts to SEAS, but make
sure it works on SEAS DB2!)
There are several ways to send commands to DB2. You can
Enter into a DB2 shell using the following command, i.e.,
$ db2
Issue DB2 commands from the UNIX command prompt by prefixing commands with "db2", i.e.,
$ db2 terminate
Put all your DB2 commands in a script file and run the file using the following command, i.e.,
$ db2 -tf <filename>
At your UNIX command prompt, use the following command, i.e.,
$ db2
Now you’re in! You'll get some messages telling you some current status about the system. Then you will get a DB2 prompt that looks something like "db2 =>". You can issue database manager commands and SQL statements while here. Some useful DB2 commands:
|
db2 => list database
directory |
Lists all the databases that exist on the system. |
|
db2 => connect to
<database> user <your_account> using <your_passwd> (if you
are on the local machine of DB2, you can ignore the user/passwd part) |
Connect to the designated database. |
|
db2 => drop table
<table name> |
Removes the table with the designated name. (This is a powerful and dangerous command!) |
|
db2 => connect reset |
Drops your connection to the database. (Always do this before leaving your session!) |
|
db2 => terminate |
Terminate current session and exit db2 interactive mode. (Always leave your session this way!) |
You will use the command connect to <database> user <your_account> using <your_passwd> to connect to an instance of DB2, where the name of a database instance is given to you by mode (i.e., STUDBL). You can either do this every time you enter the DB2 shell, or every time you start working with DB2 from the UNIX shell. (See below.)
You should always use the last two commands
above when you are done with your DB2 session. The connect reset command drops your
connection to the database (i.e., a database instance named "STUDBL"). You will still be
in the DB2 shell after this, and could do other things. Like you could then
connect to another database. (But you have no need for doing this in this
course.) Or you could try to create a new database! (But since you probably do
not have DBA privileges, DB2 probably won't let you.)
The next command, terminate ends your client connection to the DB2 server and drops you out of the DB2 shell. If you do not execute these commands and leave your DB2 shell some other way, some processes may be left running that can potentially muck things up.
If you are frustrated you have to connect each time, you can use the UNIX environment variable DB2DBDFT to tell DB2 to connect automatically to what every database DB2DBDFT names. For instance,
$ setenv
DB2DBDFT STUDBL
If you put that command in your, say, .cshrc file, then you can skip the connect to STUDBL command each time.
The DB2 system has some on-line help available. The command is "?? Some examples:
|
db2 => ? |
Provides general help. |
|
db2 => ? catalog
database |
For help on the CATALOG DATABASE command. |
|
db2 => ? catalog |
For help on all of the CATALOG commands. |
Anything you can do in the DB2 shell, you can likewise execute directly from the UNIX shell. For example:
% db2 connect to STUDBL user your_account using your_passwd
% db2 list database directory
% db2 connect reset
% db2 terminate
Again, assuming that "$" is your shell prompt here.
Let us say you wanted to write a bunch of SQL and DB2 commands in a file, and then have DB2 execute those commands. You can do that. Say that your file with the commands is named sql_file. At the UNIX command prompt, type:
$ db2 -tf sql_file
The file sql_file ought to be in your current directory. It should contain DB2 / SQL commands each ended by a semi-colon (";"). For example, your file sql_file might look like this.
|
connect to
c3421m01; list database
directory; create table
first (t1 INT); insert into
first values(1); select * from
first; connect reset;
terminate; |
To redirect the output resulting from the above DB2 command (the standard output) into a file, simply use UNIX's redirection command. For example,
$ db2 -tvf input_fname > outfile
will send the output into a file named
outfile.
Note that you have to end your DB2 / SQL commands with semi-colons when executing commands from a file, but don't need them to end your commands otherwise. What gives? Well, actually, DB2 gives you the choice in both cases really. You make the choice via the "-t" flag with the DB2 command, whether running DB2 as a UNIX command or to enter the DB2 shell. With the "-t" flag, semi-colons are expected. Without it (so the default), they are not expected. (In fact, DB2 will complain if you use them in the semi-colon-off case!!)
Why would you want to bother with the semi-colon-on option? Well, in the semi-colon-off option, a line return (CR) ends that DB2 / SQL command. And when you have long SQL commands to write, this is not very practical. Just try fitting one of your create commands for your project on one line!
Actually, if you are playing semi-colon-off and have a long DB2 / SQL command, ending the partial command with a "\" and going to the next line works.
So I always go with the semi-colon-on option. I need it as soon as I do anything serious. And I am used to it. Every other RDBMS like ORACLES expects the semi-colons. So it is best just to get used to it.
Curious what the other flags to DB2 above meant?
|
-f name |
Read the commands from file name. |
|
-v |
Echo to output the statements and commands. Otherwise, you will just get the results printed to output. |
|
-tdx |
Don't like ";"'s? Set the end-of-command character to x! |
Want an option permanently on, like -t without having to type DB2 -t every time? Well, you probably know about the UNIX command alias already. That is one approach. Another is the environment variable DB2OPTIONS. Whatever string DB2OPTIONS is set to is used as options every time you execute DB2. For example,
$ setenv DB2OPTIONS "-td."
To see the list of tables in the database, use the command LIST TABLES.
To see the schema of a table R, use the SQL command DESCRIBE SELECT * FROM R.
DB2 runs the command-line processor in the interactive mode. 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 STUDBL user your_account or db2 "select * from actors". 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.
One 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.
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. 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>.
For more detailed help, refer to the Online Reference Manual.
There are several methods for creating a typescript to turn in for your programming assignments.
A really simple way is to cut and paste your window's output, and then save it in a file. That is, if you have such windowing capabilities.
One can use the UNIX command script to record all the interactions with the terminal (also known as the shell or loosly window). The script command records everything typed to and printed to that shell ("window"). The syntax for the command is
$ script [ -a ] [ filename ]
Everything afterwards is written that file which you specified and named (filename). If you do not specify a file name, script saves by default into a file named typescript (in the directory where you were when you started script). The -a option allows you to append the session to filename, rather than rewriting it. To end the recording, type exit. For more information about script, check the man page.
One can create a file containing one's SQL commands, have DB2 execute the SQL commands in the file and redirect DB2's response (the standard output) to a file. For example, typing
$ db2 -tvf sql_file > out
at your UNIX prompt will execute the SQL commands written in filename (which should exist in your current directory), and will redirect the output to the file named out.
Here are some quick and dirty instructions on how to import data that you have in a file into a DB2 table. Likewise, you can export the data from a DB2 table into a file. The DB2 commands, surprise, are named import and export, respectively.
Yes, there is a DB2 load command. It offers a way to bulk-load schemas and their data into DB2. However, load requires DBA privileges and so is not for us. The import command is available to all, and is exactly what we want for purposes of the project.
First, the table to which you want to add data should exist. So create it first, if you do not have it already. Prepare a file with the "tuples" that you want to import. For a file in "DEL" format, each line in the file is a tuple, and the attribute values are separated (delimited) by commas (","'s). The file is just an ASCII file.
import from file.del of del
messages import.msg
insert into table;
This imports the "tuples" from
the file named file.del into the table named table. The import utility will
attempt to insert each line from file.del into table just as if you had issued
an insert command yourself (formatted correctly, of course). Note that the
order of your attribute values on each line of file.del must be the same as the
"order" of the attributes in the table. (The describe command will
show you the "order" of your table's attributes. This is the same
order as you specified them in your create command when you made the table.) To skip a line from
the data file, use "RESTARTCOUNT" before "messages"
Of course, some of the inserts may succeed, others may fail. (Why?) Whatever the import utility accomplishes is recorded in the message file import.msg for you perusal.
You can replace "insert" in the command above with insert_update. Then any new tuple that clashes with a tuple already in the table will replace ("update") the older tuple. (How can two tuples clash? They have the same primary key values.) To use this option, your table must exist and have a primary key declared. And, of course, you must have update privilege on the table. Likewise, you can replace "insert" with replace. This first wipes all tuples from the table, and then adds the "tuples" from the file.
Need to know what the format of you data must be in within file.del? A couple of pointers:
· Put strings (for instance, char, varchar) in the file within double-quotes. For example, "Parke Godfrey".
· Numbers (for instance, integer and real) are not quoted. So, 1234.
· Dates (date) on our system are in the format year-month-day in numerals. For instance, 1999-10-30. Put them in single quotes in your file. For instance, '1999-10-30'. The alternate format of month/day/year is also accepted. For instance, '10/30/1999'.
Well, I am not about to recreate all the documentation here! Need more? Read the documentation. More easily, do the following. Populate your table with a couple of tuples by hand, using the insert command. Then export your table to a file. (See the export command below.) This will show you precisely the format you need for an import file for that table.
An alternate import method is as follows. It uses an "ASC" file instead of a "DEL" file like above. Again, the file is an ASCII file. But instead of the attribute values being separated by a delimiter character (the comma, in this case), each attribute entry begins at a specified character column in the file.
import from file.asc of asc
method L(1 20, 21 26, 29 30, 33 51, 54 63, 66 73)
messages import.msg
insert into table;
The method line above is an example. It is for a table with six attributes. It specifies that the value for attribute one is between character columns 1 and 20, the second attribute's value between columns 21 and 26, and so forth.
Conversely, you can export tuples from a table into a file.
export to file.del of del
messages export.msg
select * from table;
As above, instead of a "DEL" file, you could create an "ASC" file instead by putting the phrase "to asc". Note that you use your favorite (favourite?) select SQL query as the last part of the command. So you could add a where clause if you wanted to select just some of the tuples from table. (Likewise, you could save the results of a join query into a data file, if you ever had any need to do so.)
Probably you will not need this, but just to be informative, there is another file format supported (besides "DEL" and "ASC") called IXF. It is special in that it also encodes the table's schema into the file. So it is a useful utility for moving tables from one database to another or from one system to another.
But is can be useful sometimes for database gurus too, for other purposes. Database folks, like professional programmers, have quite a bag of tricks. Here is one that Don Chamberlin suggests.
export to temp.ixf of ixf
select * from table where 1 < 0;
import from temp.ixf of ixf
create into copy;
The option create is available to import for IXF files, because the IXF file contains the schema specification. Note that the "where 1 < 0" condition seems odd: it fails always. So no tupes are selected by the export command. However, the schema specification of the table named table is written to temp.ixf. Then the import command creates a new table named copy with an identical schema to table. The table copy is empty with no tuples. Hence, a quick and dirty way to replicate a table's schema!
One can use the DB2 utility db2batch to collect runtime statistics on one's queries. To time commands, issue the following command:
$ db2batch -d DB-Name -f SQL-file
The database DB-Name is the database where the queries / commands are to be executed. (e.g., STUDBL). Note that the sql file SQL-file can contain multiple SQL or DB2 statements. Each statement within the SQL file should always end with a semi-colon. Each statement is timed separately. Naturally, these times may be affected by external factors such as system load, and so forth.
Try the command
$ db2batch -h
and you will get a laundry list of all of db2batch's many options. For yet more, see the man page on db2batch at the IBM site.
In UNIX, one could use a UNIX timing utility such as time. This can be used to time any command issued to UNIX, hence any DB2 command. For example,
$ time db2 -f SQL-file
The advantage is that this is simple. The disadvantage with respect to, say, db2batch is that it will not be as accurate, and it will not give you as much information. The reason it is not as accurate is that the time command will also measure certain OS overhead involved with the command and is probably more prone to "noise" from whatever else is going on in the OS at the time.
But if you are stuck and need to get your timing results out the door, time is an option.
In working on the projects (and in general), be careful to follow SQL syntax exactly. It is just as picky as any programming language, and perhaps is more arcane. For example,
create table books (
isbn varchar(20) not null,
...
primary key (isbn),
foreign key (AuthorID) references authors
on update restrict
on delete cascade,
...
)
Note there are no commas separating the pieces of the foreign key declaration! The "on update" and "on delete" proceed immediately after. There is a comma at the end of the whole foreign key declaration if there is another foreign key declaration or something else to follow.
The general rule of thumb is that commas separate each component such as a column, primary key, or foreign key declaration, but all modifiers, such as a "not null" for a column declaration, follow the declaration directly (no comma separating it from the main).
The DB2 system deviates from the standards just enough to cause us some troubles. But then again, all the other vendors' systems have their troublesome peculiarities too. These are just things we have to live with.
What I call a deviation is anything major that is in the SQL-2 standards and/or is discussed usually in textbooks that DB2 does not implement. Or anything standard across the other major systems but that is not in DB2. Here, I will try to map out some of the more pertinent deviations. I will add more as I run across them.
The describe command can be used for instance to describe the schema of an existing table. An oddity, however, is that one must call the table with its schema name. For instance, say that you are user fred, you are presently in your default schema space (so your schema space is fred), and you want to describe a table called matrix which belongs to you and "lives" in your schema. You must say
db2 => describe table fred.matrix;
If you just said
db2 => describe table matrix;
DB2 would complain that it could not find table matrix.
When issuing a "create table" command, one must explicitly declare all attributes (columns) as "not null" which will be part of the primary key declaration. In ORACLE 7.x, at least, one does not have to do this. It is implicitly declared that all columns that participate in the primary key are "not null". I believe the standards support ORACLE here.
The DB2 system does not allow the "on update cascade" option for foreign key constraints. The only options it allows for "on update" are "restrict and "no action". DB2 does offer the full range for "on delete". If one tries to declare an FK as "on update cascade", one simply gets a syntax error. By the way, ORACLE 7.x does not allow for "on update cascade" either.
DB2 has not been started on this machine. Please contact the TA as soon as possible.
The database instance is locked by another machine. Try logging on to another machine (landfair, lindbrook, or westholme) to connect to the database instance you want.
We shall be adding pointers and helpful information as things progress. However, database systems are very complicated pieces of software and they take quite a bit of effort to learn how to use, and much more effort to learn how to use well. Just like a programming language. It would be literally impossible for us to lay out here every command you will need to know.
This is a matter of pragmatics. But also you are computer scientists! (Or are soon to be released upon the world as such!) In the real world, you have to look up everything yourself. So be self-reliant. Go to the documentation when you need to.