Using DB2 utilities to clone databases across different platforms

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.

Share:

Roman Melnyk (roman_b_melnyk@hotmail.com), DB2 Information Development, IBM Canada Ltd.

Roman Melnyk photoRoman B. Melnyk , Ph.D., is a senior member of the DB2 Information Development team, specializing in database administration, DB2 utilities, and SQL. During more than nine years at IBM, Roman has written numerous DB2 books, articles, and other related materials. Roman coauthored DB2 Version 8: The Official Guide (Prentice Hall Professional Technical Reference, 2003), DB2: The Complete Reference (Osborne/McGraw-Hill, 2001), DB2 Fundamentals Certification for Dummies (Hungry Minds, 2001), and DB2 for Dummies (IDG Books, 2000).



18 March 2004

In a previous article (Cloning DB2 Databases Using Redirected Restore), I described a step-by-step procedure for cloning a database residing on one system to another machine (or instance) running on the same platform. The idea is to perform a redirected restore operation on a full database backup image. But what if the source and target platforms are not the same? In this case, a redirected restore operation won't do the trick, because you can't usually back up a database on one operating system, and restore it on another operating system. There has been support for cross-platform backup and restore operations between some of the UNIX® "flavors" for a while now, but what if you want to copy a DB2® database from a Windows® server to a UNIX server, or the reverse?

DB2 Universal Database™ (DB2 UDB) has a couple of useful tools to help you do just that. The db2move utility exploits the DB2 data movement utilities (export and import or load) to move the database tables. However, because a database is so much more than user tables, you need some way to transfer other database objects, such as constraints, triggers, indexes, sequences, table spaces, buffer pools, among others. That's where the db2look utility comes in. Using this tool, you can capture the data definition language (DDL) for such objects in the source database, and apply it to recreate those objects in the target database.

"Let's get a move on!" - The database movement tool (db2move)

The db2move utility retrieves a list of all user tables in a database from the system catalog. It then exports these tables in PC/IXF format, which is a version of an adaptation of the Integration Exchange Format (IXF) data interchange architecture.

The PC/IXF files can be imported or loaded to another local DB2 database on the same system, or can be transferred to another workstation platform and imported or loaded to a DB2 database on that platform. Files that db2move generates during an export operation are used as input files for the ensuing import or load operation (see Table 1). If a db2move operation is to succeed, the requesting user ID must have the correct authorization required by the underlying DB2 data movement utilities. A database connection is not required prior to invoking the db2move command; the utility does that for you.

The basic syntax of the db2move command is as follows:

Listing 1. The db2move command
	db2move <database-name> <action> [<option> <value>]

More about PC/IXF files

A PC/IXF file consists of a sequence of variable-length records, including a header record, a table record, a column descriptor record for each column in the table, and one or more data records for each row in the table. PC/IXF file records are composed of fields containing character data.

PC/IXF is the recommended file format for transferring data across platforms, because it preserves many of the table attributes and enables the DB2 data movement utilities to process data in a machine-independent way. Table attributes that are preserved in PC/IXF files include: primary key and unique constraints, some column information (such as name, data type and length, nullability attribute, and identity properties), and some index information. Table attributes that are not preserved in PC/IXF files include: referential and check constraints, and some column information (such as default values and generated column properties).

You must specify the name of the database whose tables you want to move, and the action (export, import, or load) that is to be performed. You can then specify an option to define the scope of the operation. For example, you can limit the operation to certain tables (-tn), table spaces (-ts), table creators (-tc), or schema names (-sn). Specifying a subset of tables, table spaces, or table creators is valid with the export action only. If multiple values are specified, they must be separated by commas; no blanks are allowed between items in the list of values. The maximum number of items that can be specified is 10.

Alternatively, you can specify the -tf option with the name of a file that lists the tables to export. The fully qualified table names should be listed one per line. You can also specify:

-io import-option
Specifies one of the modes under which the DB2 import utility can run. Valid options are: CREATE, INSERT, INSERT_UPDATE, REPLACE, and REPLACE_CREATE. The default is REPLACE_CREATE. For more information about these modes, see the DB2 product documentation.
-lo load-option
Specifies one of the modes under which the DB2 load utility can run. Valid options are: INSERT and REPLACE. The default is INSERT. For more information about these modes, see the DB2 product documentation.
-l lobpaths
Specifies the location in which LOB files are to be created or found. One or more absolute path names must be specified. If multiple paths are specified, they must be separated by commas; no blanks are allowed between values. The default is the current directory.
-u userid
Specifies a user ID with which the utility can log on to a remote system.
-p password
Specifies a password that authenticates the user; the utility requires a valid user ID and password to log on to a remote system.

Table 1. Files that are required or generated during export, import, and load operations managed by the db2move utility

EXhPORTIMPORTLOAD
InputOutputInputOutputInputOutput
EXPORT.out1IMPORT.out1LOAD.out1
db2move.lst2db2move.lst2db2move.lst2
tabn.ixf3tabn.ixf3tabn.ixf3
tabn.msg4tabn.msg4tabn.msg4
tabna.nnn5tabna.nnn5tabna.nnn5
system.msg6
1 Contains a summary of the completed action (ASCII).
2 Contains a list of original table names, their corresponding PC/IXF file names, and message file names (ASCII).
3 Contains exported data from a user table, identified by n (binary).
4 Contains messages about the requested action against a user table, identified by n (ASCII).
5 Contains exported large object (LOB) data for a user table, identified by n. The file name extension is a number ranging from 001 to 999, and a is an alphabetic character. These LOB files, which are created only if the table being exported contains LOB data, are placed in the LOB path directories (binary).
6 Contains system messages; created only if the action is export, and a LOB path has been specified (ASCII).

"Look and see!" - The DB2 statistics and DDL extraction tool (db2look)

The db2look utility extracts the DDL statements that are required to reproduce database objects on a different system. A database connection is not required prior to invoking the db2look command; the utility does that for you.

The basic syntax of the db2look command is as follows:

Listing 2. The db2look command
	db2look -d <database-name> [<option1> <option2 … <optionn>]

You must specify the name of the database whose objects you want to describe. You can then specify one or more options (in any order) to define the scope of the extraction, including:

-e
Extracts DDL statements for database objects such as tables, views, automatic summary tables, indexes, triggers, sequences, primary key, referential, and check constraints, user-defined functions, and procedures.
-a
Extracts DDL statements for all objects created by all users. For example, if this option is specified with the -e option, all objects in the database are processed.
-z schema-name
Limits the output to objects with the specified schema name.
-t table-name
Limits the output to one or more (maximum 30) specified tables. Table names must be separated by a blank space.
-m
Generates the required UPDATE statements to replicate statistics on tables, columns, and indexes.
-l
Generates DDL statements for user-defined table spaces, database partition groups, and buffer pools.
-x
Generates DDL statements that grant or revoke privileges on database objects.
-td delimiter
Specifies the statement delimiter that the db2look utility is to use; the default is the semicolon (;).
-o file-name
Writes the output to a file. If this option is not specified, writes to standard output.
-i userid
Specifies a user ID that the utility requires to log on to a remote system.
-w password
Specifies a password that authenticates the user; the utility requires a valid user ID and password to log on to a remote system.

"Cloning around" - A step-by-step example

Let's work through an actual example. A database called MYMOVIES is located on an AIX® Version 5 system running DB2 UDB Version 8.1.2. MYMOVIES has five tables, each of which is populated with data: MOVIE, ACTOR, APPEARS_IN, DIRECTOR, and DIRECTS. Some of these tables have primary keys defined on them, and the ACTOR table has a check constraint defined on it: the ACTOR_AGE constraint requires that any value for the ACT_YR_OF_BIRTH column pre-date the year 2004.

The MYMOVIES database is to be duplicated on a Windows 2000 Version 5.0 (SP4) system also running DB2 UDB Version 8.1.2. The strategy will be to first use db2move to export all the table data to PC/IXF files, then to use db2look to capture the DDL for existing database objects, including the ACTOR_AGE check constraint, which is not preserved in the PC/IXF file. The next steps will be to ftp the output files from these utilities to the Windows system, recreate the database and its objects there, and then finally to run the db2move utility to load the data contained in the PC/IXF files.

Step 1: On AIX, run db2move to export the data from all of the user tables in the MYMOVIES database:

Listing 3. Exporting the data
elk /home/melnyk>db2move mymovies export

*****  DB2MOVE  *****

Action:      EXPORT

Start time:  Fri Mar 12 23:04:15 2004


Connecting to database MYMOVIES ... successful!  Server: DB2 Common Server V8.1.2

Binding package automatically ...
Bind file: /home/melnyk/sqllib/bnd/db2move.bnd

Bind was successful!

EXPORT:     44 rows from table "MELNYK  "."DIRECTS"
EXPORT:     76 rows from table "MELNYK  "."APPEARS_IN"
EXPORT:     40 rows from table "MELNYK  "."MOVIE"
EXPORT:     70 rows from table "MELNYK  "."ACTOR"
EXPORT:     42 rows from table "MELNYK  "."DIRECTOR"

Disconnecting from database ... successful!

End time:  Fri Mar 12 23:04:18 2004

Step 2: On AIX, run db2look to capture the DDL for objects in the MYMOVIES database. Write the output to a file called db2look.sql.

Listing 4. Capturing the data definition language
elk /home/melnyk>db2look -d mymovies -e -a -o db2look.sql
-- Generate statistics for all creators
-- Creating DDL for table(s)
-- Output is sent to file: db2look.sql
-- Binding package automatically ...
-- Bind is successful

Step 3: On Windows, ftp to the AIX system and retrieve the required input files for the final db2move operation. Ensure that you transfer the PC/IXF files in binary mode, and the db2move.lst file, as well as the db2look.sql file, in ASCII mode.

Listing 5. Retrieving the required files
ftp> prompt
Interactive mode Off .

ftp> bin
200 Type set to I.

ftp> mget *.ixf
200 Type set to I.
200 PORT command successful.
150 Opening data connection for tab1.ixf (4513 bytes).
226 Transfer complete.
ftp: 4513 bytes received in 0.13Seconds 34.45Kbytes/sec.
 ...
200 PORT command successful.
150 Opening data connection for tab5.ixf (6289 bytes).
226 Transfer complete.
ftp: 6289 bytes received in 0.12Seconds 52.41Kbytes/sec.

ftp> asc
200 Type set to A; form set to N.

ftp> get db2move.lst
200 PORT command successful.
150 Opening data connection for db2move.lst (205 bytes).
226 Transfer complete.
ftp: 210 bytes received in 0.01Seconds 21.00Kbytes/sec.

ftp> get db2look.sql
200 PORT command successful.
150 Opening data connection for db2look.sql (2754 bytes).
226 Transfer complete.
ftp: 2876 bytes received in 0.15Seconds 19.17Kbytes/sec.

ftp> bye
221 Goodbye.

Step 4: On Windows, create the MYMOVIES database, and then run the script that was generated by the db2look utility to create the database objects, including the user tables and the check constraint, ACTOR_AGE. Run db2move to load the data from the PC/IXF files into all of the user tables in the MYMOVIES database.

Listing 6. Creating the MYMOVIES database and its objects on the Windows system
D:\WorkDir>db2 create db mymovies

D:\WorkDir>db2 -tvf db2look.sql

D:\WorkDir>db2move mymovies load



*****  DB2MOVE  *****

Action:      LOAD

Start time:  Sat Mar 13 20:59:56 2004


Connecting to database MYMOVIES ... successful!  Server: DB2 Common Server V8.1.2

Binding package automatically ...
Bind file: D:\SQLLIB\BND\DB2MOVE.BND

Bind was successful!

* LOAD:  table "MELNYK  "."DIRECTS"
  -Rows read:  44  -Loaded:  44  -Rejected   0  -Deleted   0  -Committed  44

* LOAD:  table "MELNYK  "."APPEARS_IN"
  -Rows read:  76  -Loaded:  76  -Rejected   0  -Deleted   0  -Committed  76

* LOAD:  table "MELNYK  "."MOVIE"
  -Rows read:  40  -Loaded:  40  -Rejected   0  -Deleted   0  -Committed  40

* LOAD:  table "MELNYK  "."ACTOR"
  -Rows read:  70  -Loaded:  70  -Rejected   0  -Deleted   0  -Committed  70

* LOAD:  table "MELNYK  "."DIRECTOR"
  -Rows read:  42  -Loaded:  42  -Rejected   0  -Deleted   0  -Committed  42


Disconnecting from database ... successful!

End time:  Sat Mar 13 21:01:25 2004

Step 5: On Windows, verify that the cloned MYMOVIES database is intact, and that the ACTOR_AGE check constraint is operational:

Listing 7. Verifying the integrity of the cloned MYMOVIES database
D:\WorkDir>db2 connect to mymovies

D:\WorkDir>db2 select * from movie fetch first 5 rows only

MOVIE_ID TITLE                          YR_RELEASED
-------- ------------------------------ -----------
23154    Carousel                              1956
44524    El Cid                                1961
78456    Giant                                 1956
45692    African Queen                         1951
67845    Casablanca                            1942

  5 record(s) selected.


D:\WorkDir>db2 select * from actor fetch first 5 rows only

ACTOR_ID ACTOR_NAME           ACT_YR_OF_BIRTH
-------- -------------------- ---------------
SQL0668N  Operation not allowed for reason code "1" on table "MELNYK.ACTOR".
SQLSTATE=57016


D:\WorkDir>db2 set integrity for actor immediate checked


D:\WorkDir>db2 insert into actor values ('58825','Naomi Watts',2968)

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "MELNYK.ACTOR.ACTOR_AGE".  SQLSTATE=23513


D:\WorkDir>db2 insert into actor values ('58825','Naomi Watts',1968)


D:\WorkDir>db2 "select * from actor where act_yr_of_birth > 1960"

ACTOR_ID ACTOR_NAME           ACT_YR_OF_BIRTH
-------- -------------------- ---------------
58825    Naomi Watts                     1968
46739    Elaine Cassidy                  1980
44333    Adam Baldwin                    1962
44445    Tom Cruise                      1962

  4 record(s) selected.

The ACTOR table is initially in check pending state (because of the check constraint) and requires execution of the SET INTEGRITY statement to move it to normal state. The subsequent insert operation fails, however, because the year value violates the check constraint. A valid value is successfully inserted.


Summary

We have seen how two DB2 utilities, db2move and db2look, can be used to facilitate database cloning in situations where there is no support for cross-platform backup and restore operations. You can easily adapt the example that is described in this article to clone DB2 databases in your environment.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13466
ArticleTitle=Using DB2 utilities to clone databases across different platforms
publish-date=03182004