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:
file type |
Import |
Export Load |
delimitation
support |
support |
support |
non-delimitation
support |
|
support |
IXF
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