Migrating IBM DB2 from 32 to 64-bit platform 2

In my previous post I described how to restore IBM DB2 backup which was made on 32-bit architecture to a 64-bit server. It wasn’t wise to call it “migration”. In fact, problem is much more complicated than just restoring from backup. Here I’d like to describe all hidden rocks I ran into during migration.

As I’ve already described previously you will have to provide the path to 64-bit decompression library during restore. You can read about that in detail in the first part.

The second big issue is TOOLSDB database. In case you have scheduled jobs (and you certainly do) you’d like to restore them too. Well, if you have just few of them then it would probably be easier to recreate them manually. In my case it wasn’t. First of all you need to create an empty TOOLSDB. You can do that in Tools – Tools Settings – Scheduler Settings – Create New. You have to make schema name and database name equal to what you have on the original database. You can find these values either in Control Center or via db2 get admin cfg command. Look for TOOLSCAT_DB, TOOLSCAT_INST and TOOLSCAT_SCHEMA. If you screwed up, take a look at this article. As your second step you will need to restore TOOLSDB to your newly created empty database. And lastly you’ll need to copy C:\ ProgramData\ IBM\ DB2\ DB2COPY1\ DB2DAS00\ metadata\ db2contact file to the new server. Otherwise your tasks will work but you won’t be able to edit them. It’s a bug described here.

If you use the DB2 audit facility you’ll need to activate it on the freshly installed server. To read audit configuration use db2audit describe. This way you can compare old and new configurations. On the new server auditing will be switched off. To activate it run db2audit start.

Another issue is CPU affinity. The interesting thing is that by default our new DB2 installation worked on two cores (0 and 1). Since we have 4 core license (in other words 200 PVUs on Xeons) we wanted to use 4. Solution here is to use DB2PROCESSORS configuration variable. To spread the load on our two 4-core Xeons we used db2set DB2PROCESSORS=1,3,5,7.

If you have more memory on the new server you will probably want to increase buffer pools. It doesn’t require explanation.

There is also a bug in DB2 version 9.7.3 where stored procedures from modules SYSIBMADM.UTL_FILE, SYSIBMADM.UTL_DIR and maybe some others won’t work after 32 to 64 migration. Invocations will fall back with the error:

“SQL0471N  Invocation of routine “SYSIBMADM.UTL_DIR.CREATE_DIRECTORY” failed due to reason “1”.  SQLSTATE=55023″.

Explanation of the reason code is:

“The invocation failed because the SQL routine was created on a previous release of the product and the DBMS was not able to migrate it to the current release. The routine must be dropped and re-created to become operative again”.

To resolve this error you will have to update to 9.7.4 or higher. You can find detailed explanation is here.

One more issue I personally had is that DAS didn’t start upon reboot. I had to run service manually every time. Error I had in event log was:

rcmdTcpipListener Failed to configure listener’s socket address. Check for port (db2ccmsrv) in SERVICES file, rc = 118423736

Even though DB2 documentation says that db2ccmsrv is deprecated it somehow screwed up DAS. The solution I found was to add the following line in Windows services file: “db2ccmsrv 50100/tcp” and the problem went away.

If you have your own functions or stored procedures you will have to recreate them on 64-bit server. Otherwise they won’t work.

In regards to authentication DB2 has four custom super users groups: SYSADM, SYSCTRL, SYSMAINT and SYSMON. In our case we use them to grant super users rights to particular users using Active Directory groups. You can read values for these variables by db2 get dbm cfg. To configure these parameters run:

db2 update dbm cfg using sysadm_group DB2_ADM
db2 update dbm cfg using sysctrl_group DB2_CTRL
db2 update dbm cfg using sysmaint_group DB2_MNT
db2 update dbm cfg using sysmon_group DB2_ADM

This is it. I guess I didn’t forget anything important. As you can see it’s hellova nuances. I hope this post will help someone in their own migration.


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 )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: