DB2 import/export tool

Contents
-Use the DB2 BACKUP command to back up data
-Use the DB2 restore command to restore Data
-Use db2look to extract database structure DDL
-File format used for data movement
-Use db2move to export all data
-Use the DB2 Export command to export data
-Use db2move to import data
-Use the DB2 import command to import data

Back up data using the DB2 BACKUP command

First, close all connections to the database and set the database to "silent:
$ DB2 connect to testdb user db2inst1 using thepasswd
$ DB2 quiesce database immediate force connections
$ DB2 connect Reset

Now you can start the backup:
$ DB2 backup database testdb to "/home/backup" User db2inst1 using thepasswd

The "silent" Status of the database is removed:
$ DB2 connect to testdb user db2inst1 using thepasswd
$ DB2 unquiesce Database
$ DB2 connect Reset

Note:
1. The preceding command backs up the database testdb to the specified directory/home/backup. Therefore, make sure that the current login user (db2inst1) has read and write permissions on the directory.
If you want to use the root user for backup, edit the file/etc/group and add the root user to the DB2-related groups: db2grp1, db2fgrp1, and dasadm1.
2. The generated backup file name is as follows:
Testdb.0.db2inst1. node).catn).20050131205259.001

Restore using the DB2 restore command

Restore with the same database name:
$ DB2 Restore database testdb from "/home/backup"
Note:
This will restore the database testdb from the specified location.

Recovery from different database names:
$ DB2 Restore database testdb from "/home/backup" into testdb_new
Note:
This command will create a new database named testdb_new. Its structure and content come from the backup of the original testdb.

Use db2look to extract database structure DDL

Extract DDL
$ Db2look-D testdb-a-e-x-o testdb. SQL
For the meaning and usage of parameters, see the help of db2look.

The edited DDL File
The DDL file obtained by using db2look cannot be used directly, because there are some special information related to the current system, so you need to edit the file. There are several aspects:
1. Instructions for removing the beginning and end of a file:
Connect to testdb;
Commit work;
Connect reset;
Terminate;
Between these commands is the DDL statement that defines the database. Removing these statements turns this file into a pure DDL file. In addition, sometimes it is easy to encounter a DB2 command execution error. It is more helpful to manually execute four commands out of the file.
2. Remove schema name.
In this case, the schema name is db2inst1. Because schema name changes occur during data movement on heterogeneous platforms, the most common is migration from UNIX systems to Windows platforms, its default schema names are db2inst1 and db2admin.
3. Remove all quotation marks.
This experience mainly comes from Oracle, an object. When defining it, using quotation marks is two different objects than without quotation marks.
4. Remove the specified tablespace In the create table statement.
This is because table space name changes frequently during data movement. Therefore, do not specify the table space name for different systems.
It is very important to emphasize that if a large field exists, you must create a bufferpool with a relatively large pagesize and use this bufferpool when creating a tablespace.

File format for data movement

This section briefly introduces the file formats used for DB2 data movement. There are four types:

1. ASC
A non-bounded ASCII file is an ASCII delimiter stream. The rows in the data stream are separated by line delimiters, and each column in the row is defined by the start and end positions. For example:

10 head office 160 initialize ate New York
15 New England 50 Eastern Boston
20 mid Atlantic 10 Eastern Washington
38 South Atlantic 30 Eastern Atlantic
42 Great Lakes 100 Midwest Chicago
51 plains 140 Midwest Dallas
66 Pacific 270 Western San Francisco
84 mountain 290 Western Denver

2. DEL
It is also an ASCII delimiter stream. The rows in the data stream are separated by line delimiters, and the column values in the rows are separated by column delimiters. File Type modifiers can be used to modify the default values of these delimiters. For example:

10, "head office", 160, "initialize ate", "New York"
15, "New England", 50, "Eastern", "Boston"
20, "Mid Atlantic", 10, "Eastern", "Washington"
38, "South Atlantic", 30, "Eastern", "Atlantic"
42, "Great Lakes", 100, "Midwest", "Chicago"
51, "plains", 140, "Midwest", "Dallas"
66, "Pacific", 270, "Western", "San Francisco"
84, "Mountain", 290, "Western", "Denver"

3. WSF
Work Sheet format, Worksheet format, used for data exchange with the lotus series software.

4. PC/ixf
An adapted version of the integrated exchange format (ixf) data exchange architecture, which consists of records with Variable Length of some columns, it includes the header record, Table Record, column descriptor record of each column in the table, and one or more data records of each row in the table. A pc/ixf file record consists of fields that contain character data.

Use db2move to export all data

Db2move is an integrated data migration tool that supports export, import, and load operations. In fact, the three methods of db2move are implemented by simply using the DB2 export, DB2 import, and DB2 load commands.

This section only describes its export function. Import and load will be described later. The format of the data file exported using db2move is ixf.

Create and enter the data storage directory:
$ Mkdir/home/backup/mydata
$ CD/home/backup/mydata

Export all data in the specified database:
$ Db2move testdb export-u db2inst1-P thepasswd

Note:
1. This will extract all the data in testdb to the current directory (/home/backup/mydata. The content of each table is stored in one. in the ixf file, each. each ixf file corresponds to one. MSG file ,. the MSG file describes the information when exporting data from the table. There are also two files. db2move. lst is used to record the one-to-one correspondence between the. ixf file and the. MSG file and the table. Export. Out records the screen output when exporting data.
2. For more details about the db2move command, run the command directly to print the help information.

Use the DB2 Export command to export data

Unlike the export function of db2move mentioned above, DB2 export is a more detailed export tool that supports three data file formats: del, WSF, and ixf.

The following example exports the mytbl data in the table testdb and stores it in the/home/backup directory.

Establish a connection to the database:
$ DB2
DB2 => connect to testdb user db2inst1 using thepasswd

Export in del format:
DB2 => export to/home/backup/mytbl.txt of del select * From mytbl

Export in ixf format:
DB2 => export to/home/backup/mytbl. ixf of ixf select * From mytbl
Note: to record the message during the export process, use:
DB2 => export to/home/backup/mytbl. ixf of ixf messages/home/backup/mytbl. MSG select * From mytbl

Disconnect:
DB2 => connect Reset
DB2 => quit
$

For more help on DB2 export, please:
$ DB2? Export

Use db2move to import (import) data

Log on to host 2 as db2inst1.

Create a database mytestdb:
$ DB2
DB2 => Create Database mytestdb on '/home/db2inst1' using codeset UTF-8 territory CN
DB2 => connect to mytestdb user db2inst1 using thepasswd

Create a 16 K bufferpool named mybigpool:
DB2 => Create bufferpool mybigpool immediate size 1000 pagesize 16 K

Create a tablespace and use the bufferpool created above, named mybigspace:
DB2 => Create regular tablespace mybigspace pagesize 16 K managed by system using ('/home/db2inst1/db2inst1/node0000/sql00004/sqlt0003.0 ') extentsize 16 overhead 12.67 prefetchsize 16 transferrate 0.18 bufferpool mybigpool dropped table recovery off

Note:
The extentsize, overhead, prefetchsize, and transferrate parameters are related to the servers in use. Here, the values are based on common PC servers that use SCSI hard disks.

Complete the creation of the empty Library:
DB2 => commit work
DB2 => connect Reset
DB2 => terminate

Import data:
To use the import method, you do not need to create a table structure first, that is, prepare an empty database. This is different from the load method. The load method must first create a table structure.
I copied all the data files exported from host1 (under/home/backup/mydata of host1) to a directory under host2, which is assumed to be/home/movedata
$ CD/home/movedata
$ Db2move mytestdb import-u db2inst1-P thepasswd
The imported data is displayed on the screen.

Problems:
The db2move import method can only import "normal" tables. If the table has a self-increasing identity column, an error occurs when db2move import is used. This is because if the table created by the identity column is defined as always, the column data cannot be assigned a value during data import, but should be generated by the system, you cannot import such a table using db2move. For such tables containing the identity column, only the DB2 import command can be used for import. The relevant parameters are identityignore and identitymissing. In the next section, I will provide specific instructions for operations.
A digress:
If you need to implement a unique primary key, you can use sequence instead of using the identity column, which is easier to maintain and manage.

Use the DB2 import command to import data

The Import and Export commands have a ing relationship. The difference is that the import supports four formats: ASC, Del, WSF, ixf, export only supports three types (see the description in the corresponding section above ).

The following example shows how to import the data file/home/movedata/mytbl. ixf to mytestdb.
$ DB2
DB2 => connect to mytestdb user db2inst1 using thepasswd
DB2 => import from/home/movedata/mytbl. ixf of ixf insert into mytbl
DB2 => commit work
DB2 => connect Reset
DB2 => quit
$

In the previous section, I mentioned that if the table has a self-increasing identity column, you must use the corresponding parameters to import the table. For example:
DB2 => import from/home/movedata/mytbl2.ixf of ixf modified by identityignore insert into mytbl2

For more help on DB2 import, please:
$ DB2? Import

Appendix

[Root @ tiv06 root] # uname-
Linux tiv06.cn.ibm.com 2.4.9-e.57 #1 Thu Dec 2 20:56:19 est 2004 i686 unknown

[Root @ tiv06 root] # Cat/etc/RedHat-release
Red Hat Linux Advanced Server Release 2.1as (Pensacola)

[Db2inst1 @ tiv06 db2inst1] $ db2level
Db21085i instance "db2inst1" uses "32" bits and DB2 code release "sql08010" with level identifier "01010106 ".
Informational tokens are "DB2 v8.1.0.0", "s021023", "", and Fixpak "0 ".
Product is installed at "/opt/IBM/DB2/v8.1 ".

[Root @ sea root] # uname-
Linux sea.cn.ibm.com 2.4.21-20. elsmp #1 SMP Wed Aug 18 20:46:40 EDT 2004 i686 i686 i386 GNU/Linux

[Root @ sea root] # Cat/etc/RedHat-release
Red Hat Enterprise Linux as Release 3 (taroon Update 3)

[Db2inst1 @ sea db2inst1] $ db2level
Db21085i instance "db2inst1" uses "32" bits and DB2 code release "sql08020" with level identifier "03010106 ".
Informational tokens are "DB2 v8.1.0.64", "s040812", "mi00086", and Fixpak "7 ".
Product is installed at "/opt/IBM/DB2/v8.1 ".

Reference
None