Migrate DB2 v9 on windows to DB2 v10.5 on linux
1. export db structure from windows server(use “-m” to generate statistics info):
db2look -d <dbname> -f -l -e –x [-m] -o <dbname>.ddl
2. export sequences:
db2 " select 'drop sequence '||rtrim(seqschema)||'.'||seqname||';' from syscat.sequences where seqschema not like 'SYS%'" | findstr drop > dropseq.sql
db2 "select 'select ''create sequence '||rtrim(seqschema)||'.'||seqname||' minvalue '''||'||char(nextval for '||rtrim(seqschema)||'.'||seqname||')||'';'' from syscat.sequences fetch first 1 row only;' from syscat.sequences where seqschema not like 'SYS%'" | findstr create > t.sql
db2 -tf t.sql | findstr create > crtseq.sql
3. export data from windows server:
db2move <dbname> export
4. record all the tables with "generated always" column:
db2 "export to genalwy.txt of del Select distinct tabschema||'\".\"'||tabname from SYSCAT.COLUMNS where generated='A'"
5. copy all above to linux server.
6. modify <dbname>.ddl, change tablespace container from windows style to linux and remove all the "create sequence" statements.
7. create database on Linux server:
db2 create db <dbname> using codeset GBK territory CN……
8. create db structure using <dbname>.ddl(ignore errors like create syscat/systool tablespace):
db2 -tvf <dbname>.ddl > <dbname>.log
run this command several times to make sure all the views are created.
9. create sequences
db2 -tvf crtseq.sql
10. remove all the lines in the "db2move.lst" file(generated automatically when export data on windows server) which contain tables in the "genalwy.txt" file, and put them in another file "genalwylst.txt":
mv db2move.lst db2move.lst.bak
sed -e "s/\"\"/\"/g" < genalwy.txt >genalwy2.txt
grep -v -f genalwy2.txt db2move.lst.bak > db2move.lst
grep -f genalwy2.txt db2move.lst.bak > genalwylst.txt
11. Load data on Linux server:
db2move <dbname> load -co NONRECOVERABLE –lo replace
12. generate sql to load tables with "generated always" column:
cat genalwylst.txt |sed -e "s/[\" ]//g"|sed -e "s/\!/ /g"|awk '{print "load from "$2," of ixf modified by identityoverride savecount 1000 messages "$3"\n insert into "$1" NONRECOVERABLE ; \n"}' >loadgenalwy.sql
13. load tables with "generated always" column:
db2 -tvf loadgenalwy.sql
14. generate check constraint sql:
db2 "select 'set integrity for '||rtrim(tabschema)||'.'||tabname||' immediate checked;' from syscat.tables where status = 'C'" | grep integrity >chkcnst.sql
15. set table from "check pending" to normal:
db2 -tvf chkcnst.sql
if there are still some table are in check pending state, put voilated rows in exception table:
db2 "select 'create table '||rtrim(tabschema)||'.'||rtrim(tabname)||'_EXCPT like '||rtrim(tabschema)||'.'||tabname||' immediate checked;' from syscat.tables where status = 'C'" | grep integrity >crtexcpttbl.sql
db2 "select 'set integrity for '||rtrim(tabschema)||'.'||tabname||' immediate checked for exception in '||rtrim(tabschema)||'.'||tabname||' use '||rtrim(tabschema)||'.'||rtrim(tabname)||'_EXCPT;' from syscat.tables where status = 'C'" | grep integrity >chkcnst_excpt.sql
db2 -tvf crtexcpttbl.sql
db2 -tvf chkcnst_excpt.sql
16. change start values of "generated by default" columns:
db2 "Select 'select ''alter table '||rtrim(tabschema)||'.'||tabname||' alter column '||colname||' restart with ''||'||'char(max('||colname||')+1)||'';'' from '||rtrim(tabschema)||'.'||tabname||';' from SYSCAT.COLUMNS where generated in ('D','A')" |grep alter > t.sql
db2 -tf t.sql |grep alter>t2.sql
db2 -tvf t2.sql
17. runstat(optional, do it if you didn’t run db2look with -m)
db2 “select 'runstats on table '||trim(t.TABSCHEMA)||'.'||trim(t.TABNAME)||' with distribution and index all tablesample system(10);' from syscat.tables T where t.TABSCHEMA not like 'SYS%'”|grep runstats>runstat.sql
db2 –tvf runstat.sql
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/75336/viewspace-2143116/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/75336/viewspace-2143116/