DB2 at CSE/York

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. To work within a database, you will need to connect to a specific database managed by the system. Let us call the database cxxxx in the examples below. You will be connecting to your class's database in which to create all your tables. (For COSC-6421 folks, use database c6421.)

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 cs000000, my DB2 account likewise is cs000000.

Within "database" cxxxx, each individual has a schema space. Your schema space is named by default the same as your account name; cs000000, 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 cxxxx, 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.

Logging in

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.

Using db2

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!

The db2 shell

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:

db2 => list database directory; Lists all the databases that exist on the system.
db2 => connect to cxxxx; Connect to our section's database.
db2 => create table first (id int); Creates a table called first with one attribute id of type integer.
db2 => insert into first values(1); Inserts a tuple into first.
db2 => select * from first; Selects all tuples from first.
db2 => drop table first; Removes first from the database.
This is a powerful and dangerous command!
db2 => connect reset; This drops your connection to the database.
You should always do this before leaving.
db2 => terminate; Terminate current session and exit db2 interactive mode.
Always leave your session this way!

You will pretty much issue the command connect to cxxxx 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, cxxxx). 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 cxxxx

    If you put that command in your, say, .cshrc file, then you can skip the connect to cxxxx 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 cxxxx for this course.

db2 help: ?

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.
db2 => ? SQL0104N; Will tell you what the error code `SQL0104N' means.

Running db2 commands from the Linux shell

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

% db2 connect to cxxxx
% 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.

Running db2 commands from a file

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.

connect to cxxxx;
list database directory;
create table first (t1 INT);
insert into first values(1);
select * from first;
connect reset;

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.

The semi-colon versus no semi-colon issue

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?

-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!

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

Logging out

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.