Migrating DB2 databases to DB2 UDB Version 9.1

coolwinds

===================================================================================
xcoolwinds@gmail.com      2007.08.25 实战过程  随后即将推出 Migrating DB2 databases to DB2 UDB Version 9.5

1) Pre-Migration Tasks

Ensure that you backup any configuration that could get lost during the migration and that are not included in a database backup.

mkdir /db2bckup/<TempDirecty>

cd /db2bckup/<TempDirecty>
db2support . -d <DBNAME> -cl 0

db2 LIST PACKAGES FOR ALL SHOW DETAIL > /db2bckup/<TempDirecty>/<DBNAME>_pckg.txt

db2 GET DBM CFG > /db2bckup/<TempDirecty>/dbm_<DBNAME>.cfg

db2 GET DB CFG FOR <DBNAME> > /db2bckup/<TempDirecty>/db_<DBNAME>.cfg

db2look -d <DBNAME> -e -o /db2bckup/<TempDirecty>/db2look.out -l -x -f

db2set -all > /db2bckup/<TempDirecty>/db2set.out

set |grep DB2 > /db2bckup/<TempDirecty>/set.out

db2 "select * from syscat.bufferpools" > /db2bckup/<TempDirecty>/bufferpools.out

db2 UPDATE DBM CFG USING diaglevel 4

Extract the DDL and drop any views that may face problems during the migration (usually the ones that reference the catalog tables)

3) Take offline backup

db2 force application all

db2 list applications for db <DBNAME>| grep <DBNAME> | wc -l

db2 deactivate db <DBNAME>

db2 backup db <DBNAME> to /db2bckup/<TempDirecty>


4) Migrate DB2 Server

-- Install DB2 9.1 on the server


5) Migrate DB2 instance

-- db2licd -end

-- db2 force application all

-- db2stop

-- ipclean -a

-- Logon as root

-- /opt/IBM/db2/V9.1_FP2/bin/db2ckmig <DBNAME> -l db2ckmig.log

        Need to see the message below:
        db2ckmig was successful. Database(s) can be migrated.

-- /opt/IBM/db2/V9.1_FP2/instance/db2imigr -u db2inst1 password

-- db2start

-- db2level


6) Migrate DB2 DAS

Create a new DB2 Administration Server (DAS) on each database partition server.
If you need to keep your existing DAS settings, you can migrate the DAS on each participating
database partition server instead of creating a new DAS.

-- /opt/IBM/db2/V9.1_FP2_FP2/das/bin/db2admin stop

-- Login as root

-- /opt/IBM/db2/V9.1_FP2/instance/dasmigr

-- /opt/IBM/db2/V9.1_FP2/das/bin/db2admin start

-- /opt/IBM/db2/V9.1_FP2/das/bin/db2daslevel

-- db2 get admin cfg


7) Migrate DB2 database

-- nohup db2 MIGRATE DATABASE <DBNAME> > /db2bckup/<TempDirecty>/migratedb.out &

-- db2 connect to <DBNAME>

8) Post-migration tasks

db2 activate database <DBNAME>

db2 UPDATE DBM CONFIGURATION USING diaglevel 3

db2 LIST PACKAGES FOR ALL SHOW DETAIL > /db2bckup/<TempDirecty>/<DBNAME>_pckg_new.txt
diff /db2bckup/<TempDirecty>/<DBNAME>_pckg.txt  /db2bckup/<TempDirecty>/<DBNAME>_pckg_new.txt

-- Verify table spaces information and utilization

-- Take a new database backup copy.

-- Revoke EXECUTE privilege on PUBLIC for migrated routines ? If you ran the db2undgp command after
        you migrated to DB2 UDB Version 8, you do not have to run this command again after your database
        is migrated to DB2 Version 9:

        db2undgp -d <DBNAME> -o revoke.db2

-- db2 connect to <DBNAME>
-- db2rbind <DBNAME> -l /db2bckup/<TempDirecty>/rbind.out all

Procedure:

To rebind packages in migrated databases:

1. Log on as the instance owner.
2. Rebind all invalid packages in each database by running the db2rbind command:
db2rbind database–name -l logfile all -u userid -p password
The all clause rebinds valid and invalid packages.
3. Test your applications
Verify that your DB2 server migration was successful. Test your applications
and tools to ensure the server is working as expected.

-- Recreate views dropped in step 1 using the saved DDL.


9) Changes in DB2 registry variables, configuration parameters, and database physical design characteristics:

These are the changes the DBM needs to make to enable all the automatic DB2 memory settings once the database has been migrated to v9:

Update DB cfg:
self_tuning_mem on
database_memory automatic
avg_appls automatic
locklist automatic
maxlocks automatic
num_iocleaners automatic
num_ioservers automatic
pckcachesz automatic
sheapthres_shr automatic
app_ctl_heap_sz 512
IE:
  db2 update db cfg for <DBNAME> using self_tuning_mem on database_memory automatic avg_appls automatic locklist automatic maxlocks automatic num_iocleaners automatic num_ioservers automatic pckcachesz automatic sheapthres_shr automatic app_ctl_heap_sz 512

Update DMB cfg
sheapthres 0
java_heap_sz 2048
IE:
  db2 update dbm cfg using sheapthres 0 java_heap_sz 2048

Enable automatic storage on the bufferpools:
db2 alter bufferpool ibmdefaultbp size automatic
db2 alter bufferpool bp_sys size automatic
db2 alter bufferpool bp_snap size automatic
db2 alter bufferpool bp_32k size automatic

macrozeng

不错，设为 Tip

hareluyang

migrate的大致时间 以及 migrate之后的参数变更是否可以补充下？

coolwinds

回复楼上兄弟，migrate 的大致时间常规是根据你的系统环境决定的，一般情况10－20分钟可以搞定. 另外你说的参数变更是指migrate 之前和之后的 DB/DBM CFG 的变更? 这个我当时做过比较，变化不是很大，可惜结果没有保留. 主要针对V9版本新特性需要调整的地方 就在 第 9) Changes in DB2 registry variables, configuration parameters, and database physical design characteristics.

macrozeng

呵，Migrating DB2 databases to DB2 UDB Version 9.5 的做好了没？

macrozeng

我记得在做 v8 的 migration 是需要 bind   db2ubind.lst 和 db2cli.lst 的

bpmfhu

最近做了几个db2 migrate到9.5的项目。不用bind   db2ubind.lst 和 db2cli.lst

zhangzongjun

不错，支持～

coolwinds

回 macrozeng 版主，Migrating DB2 databases to DB2 UDB Version 9.5 的还没有机会实施，不过在 IBM developerworks 论坛倒是看过一篇，为了方便查找我就新开个帖子啊^_^

Look here http://www.itpub.net/thread-1080391-1-1.html