Migrating IBM DB2 from 32 to 64-bit platform

The best way to move your database from one server to another is a backup/restore procedure. You can also use db2move utility but it’s not much of help here because it moves only the tables.

If you use a built-in compression to reduce size of your backups which is a normal thing to do then if you’ll try to restore a backup made on a 32-bit architecture to a 64-bit platform using a command like this

RESTORE DATABASE mydb FROM “D:\dbbackup” TAKEN AT 20111215030019 TO “D:” WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT ROLLING FORWARD WITHOUT PROMPTING;

then you will get an error

SQL2570N  An attempt to restore on target OS “NT-64” from a backup created on source OS “NT-32” failed due to the incompatibility of operating systems or an incorrect specification of the restore command.  Reason-code: “2”.

The reason why this happens is a compression library. Each time you make a compressed backup DB2 puts a compression library into a backup itself. When restoring on a 64-bit platform DB2 refuses to use a 32-bit library. There are two solutions to this problem. First is to make a plane uncompressed backup. But if your backup file is quite large then it can be rather painful to move it between servers. Second solution is to add COMRLIB clause into the original query

RESTORE DATABASE mydb FROM “D:\dbbackup” TAKEN AT 20111215030019 TO “D:” WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 COMPRLIB C:\SQLLIB\BIN\db2compr.dll WITHOUT ROLLING FORWARD WITHOUT PROMPTING;

If you restore to an existing database you will get SQL2539 warning. Which just means that original database files will be deleted.

Use this workaround description at IBM site as a reference: IY71307: SQL2570N when restoring a compressed backup image to another platform or wordsize.

Advertisement

DB2 advanced concepts

Now lets look at some advanced level terms of DB2 product. First comes Buffer Pool which is an extremely useful object for tuning DB2 performance. In fact Buffer Pool is just a region of memory where database keeps indexes and data. Initially pools are small. So it’s a rule of thumb to always tune them during database installation.

Next one is Transaction Logs. Transaction Logs are of big importance to any database. They keep track of transactions happening in database and in case of failure allow to “roll-forward” to particular point in time. In other words if you have full database backup made at T0 and transaction logs from that point and something bad happens at T1 then you can always recover your database to any point in time between T0 and T1 by recovering from full backup and then roll-forwarding.

Large Objects or LOBs are objects like video, audio, scanned documents, etc. It’s a type which is available during table creation. One special thing about LOBs is that they are logically created as a column of table which is stored in particular tablespace but are physically located in separate so called large object tablespace.

General view of IBM DB2 architecture

No one would argue that DB2 is one of the leading (R)DBMS products in the market. As any other powerful software product it has complex architecture. Here I’d like to introduce some basic terminology of it in series of posts because sometimes this terminology can become somewhat vague.

Lets kick off by explaining some general view of DB2. Each IBM DB2 installation has following basic levels of architecture:

• Instance can be understood as a completely independent environment with it’s own security configuration, resource allocation and contains databases and partitions isolated from all other instances. Each instance has its own system processes which manage data. Instance may contain several databases.
• Database is a most familiar term. It’s a logical unit which holds your data. It has complicated structure which we will explain later on. Database can reside in one or more partitions.
• Partitions (or nodes) is a way of creating DB2 database cluster for the sake of higher performance. You can split your database onto several servers where each of them will have their own chunk of data. Since more servers means more CPU cores, memory and disk I/O it’s a natural way of scaling DB2. Particular storage resource is called Container.
• Database is then mapped onto several Tablespaces. Tablespace allows you to manage how database tables are held on your storage resources. For example you can hold your data in files as well as on raw hard drives (which is faster). In addition it’s possible to put frequently used data on SAS hard drives and rarely used on SATA by means of creating two different tablespaces. On top of that you can set up a different page size for each of your tablespaces.

Here is the basic idea of what DB2 represents from the view of systems administrator. To better understand a folding of different levels please refer to this well-known among dbtwoers picture: link.