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 -f -l -e –x [-m]
-o .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 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
.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 using codeset
GBK territory CN……
8.create
db structure using .ddl(ignore errors like create syscat/systool tablespace):
db2 -tvf .ddl > .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 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.COLUMNSwhere 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