September 2012

The database server is not accessible by students or faculty directly
Any connection to the database will occur from the client's instance

As of September 2012, we are running DB2 -Express-C Version 10.1

This is a freely available version for universities.

You need to register with IBM's Academic Initiative Alliance for valid downloads


The client's connection can be accessed from any host users can login into 
with a valid unix login.

  
Entering db2 from any linux desktop in the department will
allow you to access db2 eg:

   gemini 316 % db2 connect to c3421a
 
 

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.0
 SQL authorization ID   = SEELA
 Local database alias   = C3421A



For Teaching purposes , databases are created by the administrator 
once a semester and always takes the course number and session. At
the end of each semester, it is dropped .

Faculty members who are teaching the course must remember to request the creation of the database for that particular semester.

In order to create a database for the current semester

First drop the previous term. It's always good to consult with the teaching faculty that they have already submitted their marks If not DO NOT DROP

Then proceed to dropping the database if you have permission

• On the client host (red as an example and using the client id db2leduc)

Become user db2leduc

   db2 "uncatalog database c3421a"
   
   If successful and you issue the following command , the database should not
   exist on the list
   db2 list database directory

• On the DB Server (gold)

as the instance owner

   db2 drop database c3421a
 
 * Now create the database for the winter semester
   db2 create database c3421m
       The CREATE DATABASE command completed successfully.

• Back on the client host

db2leduc (now 64bit Version 10)

   db2 catalog database c3421m as c3421m at node gold
       The CATALOG DATABASE command completed successfully.

• make the database available to all users

On the DB server, connect with the instance ID and do the following

           $db2 connect to database name
     $db2 "revoke connect , createtab, bindadd on database from public"
     $db2 "grant connect , createtab, bindadd on database to group faculty"
     $db2 "grant connect , createtab, bindadd on database to group ugrad"
     $db2 "grant connect,createtab,bindadd on database to group grad"

• Test the connection from any client host in the lab (all linux machines in the department)

    From any host in prism
     jun04 > db2
             db2=> connect to c3421a
                   list tables
              ....

If the client is a fresh install, you will have to catalog the node 
from where the database server resides using tcpip 
and then catalog the database
   Two steps
   seen as examples

   On client host (as client user id)
   $ db2 list node directory 
     The list should appear empty
   Now catalog the node
   $ db2 "catalog tcpip node gold remote gold server server1"
   $ db2 list node directory
     Number of entries in the directory = 1
     Node name                      = GOLD
     Directory entry type           = LOCAL
     Protocol                       = TCPIP
     Hostname                       = gold
     Service name                   = server1


   Next you will catalog the database
   $ db2 "catalog database sample as sample at node gold
   $ db2 list database directory
     Number of entries in the directory = 1
     Database alias                       = SAMPLE
     Database name                        = SAMPLE
     Node name                            = GOLD
     Database release level               = f.00
     Comment                              =
     Directory entry type                 = Remote
     Catalog database partition number    = -1


   Now try connect to the database sample
   $ db2 connect to sample
     Database Connection Information
     Database server        = DB2/LINUXX8664 10.1.0
     SQL authorization ID   = DB2LEDUC
     Local database alias   = SAMPLE