Differences

This shows you the differences between two versions of the page.

--- software:db2:start [2017/01/24 13:42] – created seela
+++ software:db2:start [2017/03/29 11:23] (current) – seela
@@ Line 1: / Line 1: @@
 ====== Instructions for accessing DB2 in Prism Labs ======
+\\
+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 in Prism lab(EA)=====
+**Using db2** \\
+You have two ways to go.
+  - Entering into a db2 shell and working there
+  - issuing all your db2 commands from your UNIX shell. The choice is yours!
+\\
+<code>
+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.)
+</code>
+=====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:
+<code>
+    % 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.
+</code>
+\\
+=====How to find the JCC Version =====
+\\
+**How to find out the JCC driver version being used in your application** \\
+\\
+<code>
+% 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
+\\
+</code>
+**How to display the version of the Universal Driver**
+\\
+<code>
+% /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
+\\
+</code>
+====Technote====
+\\
+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 and SQL Drivers =====
+\\
+**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.
+<code>
+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();
+}
+</code>
+====A Sample JDBC Program with type 4 connectivity====
+\\
+ {{:private:software:db2:ezsailor.java|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
+\\
+{{:private:software:db2:ezjava.java|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
+----
+\\
+====A Sample Sqlj Program with type 4 connectivity====
+**How to execute the sqlj code**
+\\
+  * Download the sample code
+  * Table "sailor" should exist" or modify your code to use your own tables
+{{:private:software:db2:sailors.sqlj|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
 \\