"DB2" DB2 command export and Import

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