Export the table structure and data of the DB2 database (reproduced)

For DB2 databases, importing and exporting table structures and data is simply a matter of using only the Db2look and db2move two commands. Both commands need to be executed in the client's command-line processor, but for the database server and the client are not on the same machine, the catalog command needs to be completed to load the remote database locally before the export import operation.

   

If it is a remote operation, you need to load the remote database information locally, and if it is local, skip 1 and 2, starting with 3, as follows:

1, the catalog server side node , the command is as follows:

DB2 catalog TCPIP node node_name remote hostname server service_port

DB2 uncatalog Node node_name (canceling the catalog of nodes)

Where node_name is a node name that you randomly start, the node name cannot be duplicated with the existing node name (can be viewed through DB2 list node directory ),hostname can also be IP address,service_port The port number is typically 50000 by default. The node directory is used to store all connectivity information for the remote database.

2, catalog remote DB2 database, the command is as follows:

DB2 Catalog db db_name [as Alias_name] at node node_name

DB2 Uncatalog DB db_name (de-cataloging the database)

Db_name refers to the name of the remote database, alias_name is the client instance name (which can be ignored), Db2node refers to the node you specified above !

3, Connect the database, the command is as follows:

DB2 Connect to db_name user user_name using password

db_name refers to the name of the database, user_name is the database user name, password is the database password

4. dll script to generate table structure with db2look command

db2look-d db_name -i user_name - w password -A -e-o d:\script.sql

db_name refers to the name of the database, user_name is the database user name, password is the database password

5. Export all table data with Db2move

Db2move db_name export-u user_name - p password

db_name refers to the name of the database,user_name is the database user name, password is the database password

6. Exporting the specified table data with export

DB2 "Export to d:\data\tab1.ixf of ixf lobs to d:\data\ lobfile lobs modified by LOB Sinsepfiles messages d:\data\tab1.msg SELECT * from schema_name. table_name"

schema_name is the table name, table_name is the table, lobsinsepfiles or Lobsinfile is the one that builds each before the LOB file is generated, followed by the build into a file

7. Execute SQL Script

DB2-TVF d:\script.sql- z d:\script.log

8. Import data from all tables with Db2move

db2move db_name import-io replace-u user_name -P Password

9. Import the specified table data with import

DB2 "Import from d:\data\tab1.ixf of ixf messages d:\data\tab1.msg Insert into schema_name. table_name"

Export the table structure and data of the DB2 database (reproduced)