Export table structure and import export table data in DB2

Source: Internet
Author: User
Tags command line db2 db2 installation

Method One

In the Object view window of the control center, select the datasheet you want to export the table structure to, hold down CTRL or SHIFT, and then select-> to generate the DDL by clicking the right mouse button.

Method Two

Step one: Open the DB2 command-line tool, create a new folder data in the Bin folder of the DB2 installation directory, and enter the directory.

Create this directory: mkdir data

Enter the directory: CD data

Step two: Export the table structure, the command line is as follows:

db2look-d dbname-e-a-x-i username-w password-o ddlfile.sql

After the execution is successful, you will find the SQL file in the folder you just created.

Step three: Export the data, the command line is as follows:

db2move databasename export-u username-p Password

At this point, the export data ends.

Export data in a table

Export to [path (example: D: "TABLE1.IXF)] of IXF SELECT [Field (example: * or COL1,COL2,COL3)] from TABLE1;

Export to [path (example: D: "Table1.del)] of the Del Select [Field (for example: * or COL1,COL2,COL3)] from TABLE1;

Import data from a table

Import from [Path (example: D: "TABLE1.IXF)] of ixf insert into TABLE1;

Load from [path (example: D: "TABLE1.IXF)] of ixf insert into TABLE1;

Load from [path (example: D: "TABLE1.IXF)] of ixf replace into TABLE1; Delete existing records before loading data

Load from [path (example: D: "TABLE1.IXF)] of ixf restart into TABLE1; Re-execute when Mount fails, and record export results and error messages

Import from [Path (example: D: "TABLE1.IXF)] of IXF savecount 1000 messages [path (example: D:" Msg.txt)] insert into table1;//where savecount Indicates that every 1000 actions are completed and recorded once.

Data import with self-growing field:

Load from [path (example: D: "TABLE1.IXF)] of IXF modified by Identityignore INSERT into table1;//joins modified by Identityignore.

The check that occurred when the data was lifted was suspended:

SET INTEGRITY for TABLE1 CHECK IMMEDIATE UNCHECKED;

The command is valid only for tables where the data is checked by the constraint, and if the execution is not lifted, it is necessary to check the integrity of the data, whether it does not meet the constraint, and attempt to reorganize the data before performing the mount operation.

In addition, for load and import, the literal difference is: Mount and import, but still do not understand the difference between the two.

Only performance load is obviously better than import. (load requires more permissions)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.