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