Shulou(Shulou.com)05/31 Report--

This article shows you how to import and export db2move database, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

When doing data migration, it is convenient to use DB2MOVE to import and export data.

(1) use db2move to export all data

Db2move is an integrated data movement tool, which supports three operation modes: export, import and load. In fact, these three working modes of db2move are accomplished by simply using db2 export, db2 import, and db2 load instructions.

This section only introduces its export functionality, and import and load will be introduced in a later section. 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 password

Description:

1, which will extract all the data from the database testdb to the current directory (/ home/backup/mydata). The contents of each table are stored in an .ixf file, and each .ixf file has a corresponding .msg file that describes the information when the data is exported from the table. There are two other files, db2move.lst is used to record the one-to-one correspondence between .ixf files, .msg files and tables, and EXPORT.out records the screen output when exporting data.

2. For more details about the db2move instruction, please execute the instruction directly and its help information will be printed.

(2) use db2move to import (import) data

Log in to Host 2 as db2inst1.

Create the 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 bufferpool with a pagesize of 16K, named mybigpool:

Db2 = > create bufferpool mybigpool immediate size 1000 pagesize 16K (this step can be omitted)

Create a tablespace using the bufferpool created above, named mybigspace:

Db2 = > create regular tablespace mybigspace pagesize 16K 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 (this step can be omitted)

Note:

The values of extentsize, overhead, prefetchsize, and transferrate are related to the server used. The values I use here are based on a normal PC server that uses a SCSI hard disk.

Complete the creation of the empty library:

Db2 = > commit work

Db2 = > connect reset

Db2 = > terminate

Import (import) data:

Using the import approach, you don't need to build a table structure first, that is, just prepare an empty library. This is different from the load approach, which requires the establishment of a table structure first.

I copied all the data files exported from Host1 (under / home/backup/mydata of Host1) to a directory under Host2, assuming / home/movedata

$cd / home/movedata

$db2move mytestdb import-u db2inst1-p thepasswd

Information about the imported data is displayed on the screen.

Existing problems:

The db2move import method can only import "normal" tables, and if there are self-growing IDENTITY columns in the table, there will be an error when using db2move import. This is because if the IDENTITY column is defined as always when creating the table, then the column data cannot be assigned when the data is imported, but should be generated by the system, and such a table cannot be imported using db2move. For such a table with an IDENTITY column, you can only use the db2 import directive to import, and the related parameter is IDENTITYIGNORE,IDENTITYMISSING. I will give specific instructions in the next section.

A digression:

If you need to implement a unique primary key, you can use sequence instead of the IDENTITY column, which is easier to maintain and manage.

(3) db2move loading data

Db2move mytestdb load

If a table import failure is found, it needs to be handled manually.

Note:

Mybigspace may have to set a value according to the actual situation.

When importing, remove some of the constraints in the table structure of the database, and add them after import.

The above is how db2move imports and exports the database. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.