Restore DB2 database

IBM has documentations for this scenario. See this page on DeveloperWorks: Using DB2 utilities to clone databases across different platforms

Summary: Two DB2 utilities, db2move and db2look, can be used to clone databases when there is no support for cross-platform backup and restore operations. This article provides an overview of these utilities and gives a step-by-step example of how to clone a DB2 database using this approach.

I've used already a similar procedure (involving db2move) to transfer a DB2 database from Windows to AIX and vice-versa.

Introduction

DB2’s native backup utility creates a backup image that is not portable from W2K to AIX or back. This document presents the procedures required for moving any DB2 database from one platform to the other.

Assumptions

For purposes of example, this document assumes that the database to be ported from W2K (source platform) to AIX (target platform) is MYDB.

Procedure Details

Run the db2look command for MYDB database to generate the DDL for the MYDB database.

db2look -d mydb -e –l –x -o mydb_create.sql

Create a directory in which to store the logical backup of the source database MYDB.

mkdir backup

Change to the new directory, and create the logical backup using the db2move utility

cd backup
db2move mydb export

Transfer the backup of the database and DDL script from Source to Destination. Transfer mydb_create.sql in ASCII mode, and all backup*.ixf files in binary mode.

The following steps are to be performed on the target platform.

Split the mydb_create.sql script into two scripts. The first is named mydb_tables.sql, and should contain all alias, table, and view creation commands. The second script is to be named mydb_ri.sql, and picks up where the first stopped, including check and foreign key constraints and grants. Make sure that both scripts have a CONNECT command at the top, and COMMIT, CONNECT RESET, and TERMINATE commands at the end.

Edit the mydb_tables.sql and ensure that any user tablespaces use only relative path names. If absolute path names are used, the database cannot be restored multiple times on the same system without a REDIRECTED RESTORE.

Create an empty MYDB database.

create db mydb

Run the mydb_tables.sql script to create aliases, tables, and views.

db2 –tvf mydb_tables.sql > mydb_tables.out

Review mydb_tables.out for errors. An easy method of doing this is to run the following command.

grep SQL0 mydb_tables.out|grep –v SQL0403W|grep –v SQL0598W|more

Import the data to the tables (the INSERT option can be used instead of REPLACE, but REPLACE is rerunnable).

db2move mydb load -lo replace

Run the mydb_ri.sql script to create referential integrity constraints, etc.

db2 –tvf mydb_ri.sql > mydb_ri.out

Review mydb_ri.out for errors. An easy method of doing this is to run the following command.

grep SQL0 mydb_ri.out|grep –v SQL0403W|grep –v SQL0598W|more

Verify that the source and target databases are identical.