Moving Databases from DB2 9.7 to 10.1 on Windows 2012 R2 for C3-C5 upgrade.

Those people familiar with Connections will be aware of the different methods to upgrade.   The one that we use most often, due to the fact that we have the best chance of success, is the Side-by-Side migration. With this migration you essentially setup a new environment at the version you are upgrading to , then transition your data. This also allows for the old version to be available to the customer, so that historical data is still available.

We mainly use DB2 for the backend, and most of time it’s hosted on a Linux machine. But some times we come across Windows.

This is one of those cases….

I ran into an issue with migration of DB2 servers between versions of DB2, hosts and Operating Systems.
Moving from DB2 9.7 on Windows 2003 – DB2 10.1 on Windows 2012 Server R2.

Firstly, here is an overview of the process used.

On source host – this requires an outage of your Connections host.
Login as DB2ADMIN to the local machine.
Databases are backed up offline using the DB2 Control Center.
Backups are to Files.
On Target DB2 server
Logged as Domain user
Copy from the Source host to the Target host the DB Backups.
Switch to DB2Admin
Restore Databases. Nice thing with this is that the databases were automagically upgraded to 10.1.

The issue

When the databases were restored to the DB2 10.1 server, DB2Admin on the Target machine did not get the correct permissions and authorities to the databases. Even though DB2Admin was explicitly listed in the Database, this did not help.
The problem propagated itself further in the DB2 Upgrade wizards in Connections. When the DBUpgrade wizard ran, it could not verify which Connections version the databases were. This leads me onto the next thing..how do the Connections wizards identify what Connections version the database is?
Each database has a specific table that holds what version of Connections the database is. Being IBM, having these be the same table would be way too easy. The Table for HOMEPAGE is HOMEPAGE.SCHEMA.

When viewing the data in HOMEPAGE.SCHEMA, as DB2Admin, nothing was returned, We could see no data.
DB2Admin had no rights to the database, which makes no sense.. DB2Admin should be god.

The fix

Was to perform the following on the Target DB2 Server before the restore.

Set the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES registry variable BEFORE preforming the restore into a new database.
Example:

db2stop
db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
db2start

Just to automate the restore process, I created a batch script.

 D:
 db2 RESTORE DATABASE BLOGS USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022174241 TO "D:" INTO BLOGS WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE DOGEAR USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022174313 TO "D:" INTO DOGEAR WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE FILES USER db2admin USING 1password FROM "D:\DB2TFR2" TAKEN AT 20141022174401 TO "D:" INTO FILES WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE FORUM USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022180225 TO "D:" INTO FORUM WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 REM db2 RESTORE DATABASE HOMEPAGE USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022180322 TO "D:" INTO HOMEPAGE WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE OPNACT USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022180348 TO "D:" INTO OPNACT WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE PEOPLEDB USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022180432 TO "D:" INTO PEOPLEDB WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE SNCOMM USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022180459 TO "D:" INTO SNCOMM WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE WIKIS USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022180525 TO "D:" INTO WIKIS WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1

I performed the above, restored the Databases. This time the DB’s took considerable time to restore….more stuff happening! I was now able to successfully access the Databases, and run the wizards.
Happy days,

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