Duplicate a db2 schema to another for a new client

Jephe Wu -  http://linuxtechres.blogspot.com

Objective: duplicate a db2 schema to another for a new client

Environment: RHEL 5 server, IBM db2 V9, duplicate from existing client user1 to user2

# db2level
DB21085I  Instance "db2inst1" uses "32" bits and DB2 code release "SQL09013"
with level identifier "01040107".
Informational tokens are "DB2 v9.1.0.3", "s070719", "MI00202", and Fix Pack
"3".
Product is installed at "/opt/ibm/db2/V9.1".

Concept:
create OS user and use db2look to duplicate all table structures to another schema

Steps:
1. Create OS user user2
login as root, run commands below:
useradd -c 'DB2 account for user2' -m user2
passwd user2
chage user2 (to change maximum password expiry to 99999 if you have defined default expiry days)
su - db2inst1
cd /db2/db2inst1/db/DB1  (go to the place where all the tablespace directory resides if any)
mkdir tb_user2

2. use db2 control center or command line to create tablespace for user2

preparing the following content for file create_tablespace.sql
# more create_tablespace.sql
--please login as root to create user and assign password first before creating tablespace
CONNECT TO DB1;
CREATE  REGULAR  TABLESPACE TB_USER2 PAGESIZE 16 K  MANAGED BY SYSTEM  USING ('/db2/db2inst1/db/DB1/tb_user2' ) EXTENTSIZE 8 OVERHEAD 10.67 PREFETCHSIZE 8 TRANSFERRATE 0.04 BUFFERPOOL  IBMDEFAULTBP  DROPPED TABLE RECOVERY ON;
GRANT  CREATETAB,CONNECT,IMPLICIT_SCHEMA ON DATABASE  TO USER user2;
GRANT USE OF TABLESPACE TB_USER2 TO USER user2;
CONNECT RESET;

su - db2inst1
db2 -tvf create_tablespace.sql -l create_tablespace.sql.log -s

3. use db2look to duplicate schema objects structures
db2look -d db1 -e -z user1 > user1.sql
vi user1.sql
%s#USER1#USER2#g

cat user1.sql | grep -i tb_ | grep -v tb_user1 (to check any other tablespace the user1 objects is residing in)

vi user1.sql
search 'DDL Statements for foreign keys' to separate file into 2 files as user1-1.sql and user1-2.sql so that you can import data into all tables without constrains, that will be faster if you can make sure all the data is in line with constrain already.

4. user db2 control center to give permission for tables and views for user2
open db2 control center - all databases, locate the db1, go to 'User and Group Objects' - 'DB Users', right click on user 'user2' - change,  go to 'table' column to add all tables for user2 schema then give 'yes' to all privileges except for 'control'. As well as views etc