Using DB2 utilities to clone databases across different platforms
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>]
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
1 Contains a summary of the completed
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:
- 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.
- 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.
- Generates the required UPDATE statements to replicate statistics on tables, columns, and indexes.
- Generates DDL statements for user-defined table spaces, database partition groups, and buffer pools.
- 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
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.
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.