Restore_DB2_Database_And_Change_Schema

#
# This is how I moved a DB2 database from one server to another.  
#
# The source DB2 instance was setup to point to db2inst3 with a schema of DB2INST3
# The target DB2 instance was setup to point to db2inst1 with a schema of DB2INST1
#
# The basic flow was to: 
#  1. Restore the backup on the target server
#  2. Create a user that matched the source DB user, and grant rights to the target DB user
#  3. Export the source Schema and Table Data
#  4. Replace the old schema value with the new one in all exported files
#  5. Drop the old schema and table data
#  6. Import the modified schema and table data files
# 
# Gotcha's along the way were:
#   - change the user from db2inst3 to db2inst1 (to fix the grants and be able to EXPORT the old database)
#   - change the schema from DB2INST3 to DB2INST1 (because Sugar doesn't let you specify a schema config var)
#

# Load the backup
db2 restore db sugcrmdv from /home/db2inst1/ on /home/db2inst1/ replace existing

# Verify it loaded into the catalog
db2 list database directory

# Roll the log forward
db2 rollforward db SUGCRMDV to end of backup and complete;

# Take a look at the schema - if username and schema don't match we need to follow the below steps to fix
db2 connect to SUGCRMDV
db2 select schemaname from syscat.schemata

# View Role Grants - they need to match your current user
db2 connect to SUGCRMDV
db2 "select char(grantor,35) as grantor, char(grantee,35) as grantee from syscat.dbauth where securityadmauth='Y'"

# GRANTOR                             GRANTEE                            
# ----------------------------------- -----------------------------------
# SYSIBM                              DB2INST3                           
#
#  1 record(s) selected.

# Add the original db2inst3 user so we can fix the grants for the target db2inst1 user
useradd -d /home/db2inst3 -g db2iadm1 -G dasadm1 -s /bin/bash -m db2inst3
su - db2inst3
. /home/db2inst1/sqllib/db2profile
db2 connect to SUGCRMDV
db2 GRANT SECADM ON DATABASE TO USER db2inst1
db2 GRANT DBADM ON DATABASE TO USER db2inst1

# Fix binding issues - otherwise db2look complains about binding...
cd /home/db2inst1/sqllib/bnd
db2 connect to SUGCRMDV
db2 "bind db2look.bnd BLOCKING ALL GRANT PUBLIC sqlerror continue"
db2 "bind db2lkfun.bnd BLOCKING ALL GRANT PUBLIC sqlerror continue"

# Dump the old database schema
db2look -d SUGCRMDV -e -o SUGCRMDV.sql

# Replace instances of DB2INST3 -> DB2INST1
perl -p -i -e 's/DB2INST3/DB2INST1/g' SUGCRMDV.sql

# Export table data and update Schema references
mkdir SUGCRMDV.export
cd SUGCRMDV.export
db2move SUGCRMDV export
perl -p -i -e 's/DB2INST3/DB2INST1/g' db2move.lst

# Drop tables pointing to the old schema
db2 "Select 'DROP TABLE DB2INST3.',tabname, ';' from syscat.tables where owner='DB2INST3'" | grep DROP  > drop_tables.sql
perl -p -i -e 's/DB2INST3\.\ /DB2INST3\./g' drop_tables.sql
db2 -tvf drop_tables.sql

# Load the new schema and tables
db2 -tvf SUGCRMDV.sql
db2move SUGCRMDV import

# View tables
db2 list tables