DB2 is running in the PRISM lab for EECS3421 students. The database name uses the format c3421 and the section code eg c3421a. The database assigned to you will be according to the section code you are in. Every semester the databases are regenerated. You can only run the command from a unix shell (Linux host). At present we do not have a windows client. All the machines in Las1006 and Las1004 has a linux operating system. If you are using a remote login: red.eecs.yorku.ca is our external host that you can access the database using db2 commands

Using db2
You have two ways to go.

1. Entering into a db2 shell and working there
2. issuing all your db2 commands from your UNIX 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:(examples) 

db2 => list database directory. 
db2 => connect to c3421a 	 
db2 => create table first (id int) 	
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 duration of 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.)

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.

How to find out the JCC driver version being used in your application

% db2jcc -version
red 278 % source ~db2leduc/sqllib/db2cshrc
red 280 % db2jcc -version
IBM Data Server Driver for JDBC and SQLJ 4.22.29
Alternatively

 % java com.ibm.db2.jcc.DB2Jcc -version
IBM Data Server Driver for JDBC and SQLJ 4.22.29 \\
\\
% /cs/local/bin/java -cp /eecs/local/pkg/db2/V11/java/db2jcc.jar com.ibm.db2.jcc.DB2Jcc -version
   results in \\
IBM DB2 JDBC Universal Driver Architecture 3.72.24
\\

How to display the version of the Universal Driver

% /cs/local/bin/java -cp /cs/local/pkg/db2/V11/java/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -version \\
Returns \\
IBM Data Server Driver for JDBC and SQLJ 4.22.29
\\

Question

Which JDBC driver (JCC) version corresponds with each DB2 release and Fix Pack level? Answer

The IBM Data Server Driver for JDBC and SQLJ package includes two JDBC drivers:

  db2jcc.jar - This driver is based on the JDBC 3 specification
  db2jcc4.jar - This driver is based on the JDBC 4 or later specifications

The db2jcc.jar driver is now deprecated. After version 3.72, which is delivered with DB2 Version 11.1 for Linux, UNIX, and Windows Modification Pack 1 Fix Pack 1, db2jcc.jar will include no new features

JDBC Drivers

For the DB2 JDBC Type 4 Driver, specify a URL of the following form: TYPE 4:(Class.forName(“com.ibm.db2.jcc.DB2Driver”); Connecting to a data source using the DriverManager interface with the IBM Data Server Driver for JDBC and SQLJ

A JDBC application can establish a connection to a data source using the JDBC DriverManager interface, which is part of the java.sql package.

The steps for establishing a connection are:

    Load the JDBC driver by invoking the Class.forName method.
If you are using JDBC 4.0 or later, you do not need to explicitly load the JDBC driver.
For IBM Data Server Driver for JDBC and SQLJ, you load the driver by invoking the Class.forName method with the following argument:

com.ibm.db2.jcc.DB2Driver

The following code demonstrates loading the IBM Data Server Driver for JDBC and SQLJ:

try {
  // Load the IBM Data Server Driver for JDBC and SQLJ with DriverManager
  Class.forName("com.ibm.db2.jcc.DB2Driver");
} catch (ClassNotFoundException e) {
     e.printStackTrace();
}

JDBC sample program

Once downloaded , rename the file to EzSailor.java
Edit the java source file to modify the database and tables you have created
Another sample table
Must be renamed to EzJava.java and edited to make the right connection
How to execute the jdbc program
Make sure you have the table “sailor” created or else the query will not be able to execute

• source the client configuration file : ~db2leduc/sqllib/db2cshrc
• javac EzSailor.java
• java EzJava c3421m

—-

How to execute the sqlj code

• Download the sample code
• Table “sailor” should exist“ or modify your code to use your own tables

sqlj
This is a sample sqlj program that does the precompiling for you . Remember these are just example scripts to aid the development of your own code

• Program name : sailor.sqlj
• source ~db2leduc/.cshrc
• sqlj sailors.sqlj
• db2sqljcustomize -url jdbc:db2:c3421m sailors_SJProfile0.ser
• java sailors 71

will return your output