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.
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();
}
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
—-
How to execute the sqlj code
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
will return your output