DB2 at PRISM 

DB2 is running in the PRISM lab for 3421 students. A database has been created for the COSC-3421 A section. The database is named c3421a. 

Logging in
If you are remote (that is, logging in from anywhere but in the PRISM lab), login to red (that is, red.cs.yorku.ca). If you are at the PRISM lab, log in to your favorite machine. All jun01-jun40 are also available to run db2.

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 UNIX 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 c3421a 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 c3421a (as an example) every time you enter the db2 shell, or every time you start working with db2 from the UNIX 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, c3421a). 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. 
  • Frustrated you have to connect each time? The UNIX environment variable DB2DBDFT tells DB2 to connect automatically to whatevery database DB2DBDFT names. For instance, 
      • % setenv DB2DBDFT c3421a
      If you put that command in your, say, .cshrc file, then you can skip the connect to c3421a 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 the same database for the durationof the 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. 

    Running db2 commands from the UNIX shell
    Anything you can do in the db2 shell, you can likewise execute directly from the UNIX shell. For example:
    % db2 connect to c3421a
    % db2 list database directory
    % db2 "create table first (id INT)"
    % db2 "insert into first values(1)"
    % db2 "select * from first"
    % db2 connect reset
    % db2 terminate
    (I am assuming that "%" is your shell prompt here.)

    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 c3421a;
    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.

    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 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 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 weenies:
    • 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 everytime you execute db2. For example, 
      • % setenv DB2OPTIONS "-td." 

    More?
    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.

    For more detailed help, refer to the Online Reference Manual.