Differences

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

--- software:db2:setup [2015/01/05 15:32] – seela
+++ software:db2:setup [Unknown date] (current) – removed - external edit (Unknown date) 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Setup of Database Server ======
-====Teaching - Understanding DB2====
-**September 2012** \\
-<code>
-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. The default authentication is set to client
-so that students with a valid unix ID can login into to the database
-without creating accounts inside the database
-Entering db2 from any linux desktop in the department will
-allow you to access db2 eg:
-   % db2 connect to c3421a
-   Database Connection Information
-   Database server        = DB2/LINUXX8664 10.1.0
-   SQL authorization ID   = xxxxx
-   Local database alias   = C3421A
-For Teaching purposes , databases are created by the administrator
-every semester and always takes the course number and session. At
-the end of each semester, it is dropped once exams are completed.
-The dropping happens at the start of the new semester
-</code>
-**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====
-\\
-\\
-----
-\\
-<code>
-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
-</code>
-====Granting Permissions to access the database====
-DB2 accounts default to the Operating system accounts . Within the database
-configuration authentication is set to client
-When the database is setup for teaching, the access is set up.
-However, if one migrates a database, additional permissions have to be granted
-\\
-**Test create,insert,select and drop functions**
-\\
-db2 connect to c3421m
-db2 "create table emp (name varchar(20))"
-db2 "insert into emp values('seela')"
-db2 "select * from emp"
-db2 "drop table emp"
-\\
-\\
-**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// \\
-\\
-<code>
-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
-</code>
-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
-For applets , authentication should be set to server. However
-this will mean that all users who have to access the database
-will have to have accounts created inside the database