The DB2 system (UDB v8.2 for linux) is running in PRISM. You can access it from red.cs.yorku.ca or any other PRISM machine. A database has been created for the CSE-3421(M) class (winter 2009). This is named c3421m. You will be connecting to this database in which to create all your tables.

Everyone has an implicit account on our DB2 system. You do not need to log in though. Your PRISM account drops through as your DB2 account too. So if my CS / PRISM account were cse00000, my DB2 account likewise is cse00000.

Within "database" c3421m, each individual has a schema space. Your schema space is named by default the same as your account name; cse00000, for example. A schema space is meant to be a "logical" database as we know the term from our textbook: a collection of related tables.

If you typed

% db2 list tables

you would see only your tables, which are in your schema space. If you typed instead

% db2 list tables for all

you will see all the tables in c3421m, including other users's and the system's.

Although you can see what tables others have, privileges (database systems's term for permissions) are set so folks cannot look in other people's tables, or make tables in other people's schema spaces.

Well, I figure you all know how to do this! Login to, say, red.cs.yorku.ca. (This is a linux box.) DB2 is a client-server system, so you can access it as well from any other PRISM machine.

You have two ways to go. Entering into a db2 shell and working there, or issuing all your db2 commands from your Linux shell. The choice is yours!

At your Linux command prompt, type:

% db2

Now you are 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:

You will pretty much issue the command connect to c3421m every time you enter the db2 shell, or every time you start working with db2 from the Linux shell. (See below.)

You should always do the last two commands above when you are done with your DB2 session. The connect reset command drops your connection to the database (say, c3421m). 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 priviledges, 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. And if you do this habitually, tech staff will hunt you down and... Need I say more?

UNIX / Linux weenies:

• Frustrated you have to connect each time? The Linux environment variable DB2DBDFT tells DB2 to connect automatically to whatevery database DB2DBDFT names. For instance,

  % setenv DB2DBDFT c3421m

  If you put that command in your, say, .cshrc file, then you can skip the connect to c3421m part each time.

  The disadvatage? You have to say connect reset and then connect to other to connect to database other. Big deal. Anyway, you'll be living in c3421m for this course.

The DB2 system has some on-line help available. The command is "?. Some examples:

Anything you can do in the db2 shell, you can likewise execute directly from the Linux shell. For example:

% db2 connect to c3421m

% db2 list database directory

% db2 "create table first (id INT)"

% db2 "insert into first values(1)"

% db2 "select * from first"

% db2 "? SQL0104N"

% db2 connect reset

% db2 terminate

(I am assuming that "%" is your shell prompt here.) Why are some of the line quoted in double-quotes? Well, you are submitting shell commands. The shell (e.g., csh, tcsh, etc.) interprets certain characters in a special way, such as '*' and '?'. However, for some of these as in "select *", you are passing them along to DB2 for processing, and you do not want the shell muffing with them. The quotes tell the shell not to process the special characters.

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

To redirect the output resulting from the above db2 command (the standard output) into a file, simply use Linux'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 Linux 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 ORACLE expects the semi-colons. So it is best just to get used to it.

Curious what the other flags to db2 above meant?

UNIX / Linux weenies:

• Want an option permanently on, like -t without having to type db2 -t every time? Well, you probably know about the Linux command alias already. That is one approach. Another is the environment variable DB2OPTIONS. Whatever string DB2OPTIONS is set to is used as options everytime you execute db2. For example,

  % setenv DB2OPTIONS "-td."

If you are inside a db2 shell session, type

db2 => terminate;

This will return you to your Linux shell.

Otherwise, if you have been issuing your commands via db2 at your Linux shell command line prompt instead, type

% db2 terminate

at your Linux shell prompt when you are done playing with DB2 for now.

Please try to remember to TERMINATE whenever you are done for the time being. The DB2 system starts a background process whenever you first CONNECT. These background processes can just accumulate over time and eat resources if they are not regularly terminated. (Yes, the DB2 system is supposed to clean up periodically, but it does not always do such a great job.)

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 just a matter of pragmatics. However, you folks 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. And go to the documentation when you need to.