"DB2" DB2 command export and Import

Source: Internet
Author: User
Tags db2 numeric value table definition

Environment preparation

1. Create a new table

[Email protected]:~> DB2 "CREATE TABLE TB1 (ID int,dt timestamp,name varchar (100))";
db20000i the SQL command completed successfully.

2. Inserting data

[Email protected]:~> DB2 "INSERT into TB1 values (1,current timestamp, ' Jack ')";
db20000i the SQL command completed successfully.
[Email protected]:~> DB2 "INSERT into TB1 values (2,current timestamp, ' numy ')";
db20000i the SQL command completed successfully.

3. Create a new table (import standby)

[Email protected]:~> DB2 "CREATE table tb1_tmp like Tb1";
db20000i the SQL command completed successfully.


Data import and Export in DB2 are: Import vs. export

DEL: Delimited ASCII file, row delimiter and column delimiter separate data

ASC: Fixed-length ASCII file, line separated by row delimiter, column length

PC/IXF: can only be used to guide the data between DB2, according to the type numeric value is packaged into decimal or binary, the character is saved as ASCII, only the length of the variable is saved, the file includes the table definition and table data

WSF: Worksheet mode Import and export, this format of file type is used relatively little

Different data import and export methods in DB2 support different file types:

delimitation non-delimitation IXF
file type Import Export Load
support support support
support support
support support support

WSF table

Support Support Not supported


For a brief introduction to the 3 Import and export operations:
Export: Exporting data, supporting Ixf,del or WSF
Import: Importing data, you can import data into a table and support the 4 file types mentioned above.
Load: Import data, function and import are basically the same. Support for the above mentioned file types. Export and import considerations

<1> different character sets, fields contain timestamp format

Export

[Email protected]:~> DB2 "Export to Exp.dat of Del MODIFIED by codepage=1386 timestampformat=\" Yyyy-mm-dd hh:mm:ss Tt\ "SELECT * from Tb1" sql3104n the  export utility was beginning to export data to file "Exp.dat". sql3105n  The Export utility has finished exporting "2" rows. Number of rows Exported:2


Codepage=1386 refers to the conversion of a database code page when data is exported.

1208→gbk

1386→utf-8

Import

[Email protected]:~> DB2]Import from Exp.dat of Del MODIFIED by codepage=1386 timestampformat=\ "Yyyy-mm-dd hh:mm:ss tt\" INSERT into tb1_tmp
    "; sql3109n The utility is beginning-to-load data from file "Exp.dat".  SQL3110N The utility has completed processing. "2"Rows were read from Theinput file. SQL3221W ... Begin COMMIT work. Input Record Count = "2". sql3222w. COMMIT of any database changes is successful. sql3149n "2"Rows were processed from the input file. "2"Rows weresuccessfully inserted into the table. "0"Rows were rejected.  Number of rows read = 2Number of rows skipped = 0Number of rows inserted = 2Number of rows updated = 0Number of rows rejected = 0Number of rows committed = 2
Import imports a large number of


DB2 Import from data file name of IXF modified by compound=100 INSERT into table name

In the above command, import waits for the SQL execution result to be returned after every 100 records, not every record, is inserted.

Setting the Commitcount parameter for faster import

DB2 Import from data file name of IXF modified by compound=100 commitcount 10000 INSERT INTO table name

"DB2" DB2 command export and Import

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.