How to setup a new schema in a DB2 database

Objective: Create a new client schema on the existing DB2 database
Environment: RHEL5 and DB2 V9


Steps:

1. create operating system user
[root@db1 ~]# useradd -c 'DB2 account for jephe' -m jephe
[root@db1 ~]# passwd jephe
Changing password for user jephe
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@db1 ~]# chage jephe
Changing the aging information for jephe
Enter the new value, or press ENTER for the default

        Minimum Password Age [7]:
        Maximum Password Age [90]: 99999
        Last Password Change (YYYY-MM-DD) [2010-01-11]:
        Password Expiration Warning [7]:
        Password Inactive [-1]:
        Account Expiration Date (YYYY-MM-DD) [1969-12-31]:

2. create tablespace directory
su - db2inst1
cd /db2/db2inst1/db/DB1 (assuming db2 database directory is /db2/db2inst1/db)
mkdir tb_jephe

3. create database and tablespace using db2 control center

Create database by using control center, you'd better to choose 16k tablespace page size instead of 4k, and choose UTF-8 as codeset

CREATE DATABASE JEPHE AUTOMATIC STORAGE NO  ON '/home/db2inst1' USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 16384;


CONNECT TO DB1;
CREATE  REGULAR  TABLESPACE TB_JEPHE PAGESIZE 16 K  MANAGED BY SYSTEM  USING ('/db2/db2inst1/db/DB1/tb_jephe' ) EXTENTSIZE 16 OVERHEAD 10.67 PREFETCHSIZE 16 TRANSFERRATE 0.04 BUFFERPOOL  IBMDEFAULTBP  DROPPED TABLE RECOVERY ON;
CONNECT RESET;

4. add user jephe to tablespace tb_jephe
CONNECT TO DB1;
GRANT  CREATETAB,CONNECT,IMPLICIT_SCHEMA ON DATABASE  TO USER JEPHE;
GRANT USE OF TABLESPACE TB_JEPHE TO USER JEPHE;
CONNECT RESET;

5. use db2look to duplicate schema from the existing ones
 db2look -d db1 -e -z existingschemaname -o existingschema.sql

then use vi to batch change the existingschemaname to jephe

6. use db2 control center to assign table and view privileges for the new user

# TABLES
CONNECT TO DB1;
GRANT  SELECT,INSERT,UPDATE,DELETE,ALTER,INDEX,REFERENCES ON TABLE JEPHE.USERNAME TO USER JEPHE;
...
CONNECT RESET

#VIEWS
CONNECT TO DB1;
GRANT  SELECT,INSERT,UPDATE,DELETE ON VIEW JEPHE.USERNAME TO USER JEPHE;
...
CONNECT RESET

7. login as new client jephe and test
db2 connect to db1 user jephe
db2 "select * from certaintablename"