This is an old revision of the document!
Table of Contents
Setup of Database Server
Teaching - Understanding DB2
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.
Teaching with a Database
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 ....
Cataloging Nodes, Databases
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
Granting Permissions to access the database
When the database is setup for teaching, the access is set up. However, if one migrates a database, additional permissions have to be granted
Problem
User can connect to database that was migrated but cannot access existing tables
I do not have any rights in it. I tried to do a
“SELECT * from MEMBER”
and the answer was that mkant does not have the right to do SELECT on the table
mkant.member.
Resolution
After restoring the database from V9.7 to V10
User can see all existing tables but cannot use :select“
Grant dataaccess to database
eg db2 “grant DATAACCESS on database to group tech”
User could not drop a table created previous to restore
db2 => drop table books_fall DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0551N "CSE93164" does not have the privilege to perform operation "DROP TABLE" on object "CSE93164.BOOKS_FALL". SQLSTATE=42501
RESOLUTION The database was restored from Version9.7 to V10.1. Connect, createtab bindadd permissions were granted to group ugrad. However since the table was restored from V9, the user could not drop the restored table.
Grant control on the table for the user to be able to drop db2 “grant control on cse93164.books_fall to user cse93164 eg db2 grant accessctrl on database to user seela