Step-by-step guide on db2move to migrate data in DB2

# Source server

--export table ddl
db2look -d CMSDEV -e -u cms -td @ > db2look_CMSDEV.ddl

mkdir CMSDEV
cd CMSDEV
db2move CMSDEV export -aw -sn cms
cd ..

------------------------------------------
# Target server:

db2 "create database NCMDB automatic storage yes on /home/db2inst1 dbpath on /home/db2inst1 using codeset UTF-8 territory CN collate using system"

db2 update db cfg for NCMDB using LOGARCHMETH1 DISK:/db2log
db2 update db cfg for NCMDB using trackmod on
db2 update db cfg for NCMDB using SELF_TUNING_MEM on
db2 update db cfg for NCMDB using CUR_COMMIT on
db2 update db cfg for NCMDB using DBHEAP automatic
db2 update db cfg for NCMDB using SORTHEAP automatic
db2 update db cfg for NCMDB using SHEAPTHRES_SHR automatic
db2 update db cfg for NCMDB using STMTHEAP automatic
db2 update db cfg for NCMDB using APPLHEAPSZ automatic
db2 update db cfg for NCMDB using STAT_HEAP_SZ automatic
db2 update db cfg for NCMDB using LOGFILSIZ 76800
db2 update db cfg for NCMDB using LOGPRIMARY 10
db2 update db cfg for NCMDB using LOGSECOND 5
db2 update db cfg for NCMDB using AUTO_MAINT on
db2 update db cfg for NCMDB using AUTO_TBL_MAINT on
db2 update db cfg for NCMDB using AUTO_RUNSTATS on

db2 backup db NCMDB compress
db2 activate db NCMDB
db2 connect to ncmdb
db2 grant connect,createtab,bindadd,load on database to user ncm
db2 "revoke connect,createtab,bindadd,implicit_schema on database from public"

# create tablespace and bufferpool
db2 "create bufferpool NCMBP32K size 6400 pagesize 32K"
db2 "create tablespace NCMTBS32K pagesize 32K bufferpool NCMBP32K autoresize yes"
db2 "grant use of tablespace NCMTBS32K to user ncm"

# change cms to ncm in db2look_CMSDEV.ddl, modify database name, tablespace name, execute as user ncm
db2 -zdb2look_ncmdb.out -td@ -vf db2look_CMSDEV_1.ddl

grep -c "@" db2look_CMSDEV_1.ddl -- 1163
grep -ci "db20000i" db2look_ncmdb.out -- 1162 , db2 connect does not print DB20000I info

grep -B1 "FOREIGN KEY" db2look_CMSDEV_1.ddl | sed '/--/d' |sed '$!N;s/\n/\t/' | sed 's/ADD CONSTRAINT/ALTER FOREIGN KEY/g' | sed 's/FOREIGN KEY$/NOT ENFORCED;/g' > ncmdb_disable_fk.sql
grep -B1 "FOREIGN KEY" db2look_CMSDEV_1.ddl | sed '/--/d' |sed '$!N;s/\n/\t/' | sed 's/ADD CONSTRAINT/ALTER FOREIGN KEY/g' | sed 's/FOREIGN KEY$/ENFORCED;/g' > ncmdb_enable_fk.sql
db2 connect to NCMDB user ncm using ncm
db2 -zncmdb_disable_fk.log -tvf ncmdb_disable_fk.sql
db2 terminate
cd CMSDEV

# change CMS to NCM in db2move.lst
db2move ncmdb import -u ncm -p ncm
cd ..
db2 connect to ncmdb user ncm using ncm

# check rejected rows and generate load commands
egrep "IMPORT: table|Rejected:" IMPORT.out| sed '$!N;s/\n/ /'| grep -v "-Rejected: 0" | awk '{print $6}' \
| awk -F\" '{print $3}'| tr "\n" "|" | more

JC_ADVERTISING|JC_ADVERTISING_SPACE|JC_CHANNEL|JC_CONTENT|JC_CONTENT_RECORD|JC_CONTENT_TAG|JC_DEPARTMENT|JC_DICTIONARY|JC_FRIENDLINK|JC_FRIENDLINK_CTG|JC_GROUP|JC_GUESTBOOK|JC_GUESTBOOK_CTG|JC_KEYWORD|JC_LOG|JC_MODEL_ITEM|JC_ROLE|JC_SEARCH_WORDS|JC_SENSITIVITY|JC_SITE|JC_SITE_ACCESS_COUNT|JC_SITE_ACCESS_STATISTIC|JC_TASK|JC_USER_MENU|JC_USER_SITE|JC_VOTE_ITEM|JC_VOTE_RECORD|JC_VOTE_SUBTOPIC|JC_VOTE_TOPIC|JC_WORKFLOW|JC_WORKFLOW_EVENT|JC_WORKFLOW_EVENT_USER|JC_WORKFLOW_RECORD|JC_WORK_STATISTICAL|JG_WEIXINMENU|JG_WEIXINMESSAGE|JO_FTP|JO_USER

egrep -w "JC_ADVERTISING|JC_ADVERTISING_SPACE|JC_CHANNEL|JC_CONTENT|JC_CONTENT_RECORD|JC_CONTENT_TAG|JC_DEPARTMENT|JC_DICTIONARY|JC_FRIENDLINK|JC_FRIENDLINK_CTG|JC_GROUP|JC_GUESTBOOK|JC_GUESTBOOK_CTG|JC_KEYWORD|JC_LOG|JC_MODEL_ITEM|JC_ROLE|JC_SEARCH_WORDS|JC_SENSITIVITY|JC_SITE|JC_SITE_ACCESS_COUNT|JC_SITE_ACCESS_STATISTIC|JC_TASK|JC_USER_MENU|JC_USER_SITE|JC_VOTE_ITEM|JC_VOTE_RECORD|JC_VOTE_SUBTOPIC|JC_VOTE_TOPIC|JC_WORKFLOW|JC_WORKFLOW_EVENT|JC_WORKFLOW_EVENT_USER|JC_WORKFLOW_RECORD|JC_WORK_STATISTICAL|JG_WEIXINMENU|JG_WEIXINMESSAGE|JO_FTP|JO_USER" \
db2move.lst | sed 's/"NCM "."//' | sed 's/"//'| awk -F \! '{printf("db2 load from %s of ixf modified by identityoverride insert into NCM.%s \n",$3,$2);}' > db2load_cmd.sh

# load table
chmod u+x ./db2load_cmd.sh
db2 connect to ncmdb
./db2load_cmd.sh > db2load_cmd.out

# check load output
grep "Number of rows read" db2load_cmd.out | awk '{print $NF}' > read_count.txt
grep "Number of rows committed" db2load_cmd.out | awk '{print $NF}' > commit_count.txt
diff read_count.txt commit_count.txt

db2 -zncmdb_enable_fk.log -tvf ncmdb_enable_fk.sql
grep -ci "db20000i" ncmdb_enable_fk.log -- should be 143
db2 terminate
db2 connect to ncmdb
db2 "select tabname from syscat.tables where status='C'" # verify that no records selected

db2 deactivate db ncmdb
db2 backup db ncmdb compress
db2 activate db ncmdb
db2pd -d ncmdb -tablesp
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s