DB2 file Import Export Common commands Summary

DB2 data migration, the most commonly used is to import the export function, and the import and export of the command seemingly simple, but actually contains mystery, the ever-changing, slightly inattentive, the error is full, here on the work of the command, summed up, to share to everyone! Welcome everyone to shoot bricks enthusiastically! J
Of course, before this, I think it is necessary to mention a little about the import and export basics!
DEL: defined ASCII files, row and column delimiters separate the data.
ASC: A fixed-length ASCII file in which rows are separated by row breaks and the columns are set to length.
PC/IXF: can only be used to guide data between DB2, based on the type numeric value is packaged into decimal or binary, characters are saved in ASCII, only the length of the variable has been used, the file includes the definition of the table and table data.
WSF: Worksheet Import Export, the file type in this format is relatively small.
DB2 to different data import export way, support different file types, here personally feel very necessary attention.
File type Import export load
-------------------------------------------------------
Delimitation Support Support Support
Support not supported by non-delimitation support
IXF Support Support Support
WSF Worksheet support Support is not supported
For a simple introduction to 3 Import and export operations:
Export: Exporting data to support Ixf,del or WSF
Import: Imports data, you can import data into a table, support the 4 file types mentioned above.
Load: Import data, functionality and import are basically the same. Support for several file types mentioned above.
About Export
This is actually relatively simple, nothing to say, General Order: Export to filename of filetype select x from xx where; OK, here's the thing to note:
1. About the export of different character sets
MODIFIED by codepage=
Exprot to Filename.del for del MODIFIED by codepage=1386 Select ... from ... where ...;
Here, when the data is poured out of the database, it will do a conversion of the database code page
2. Time Field formatted
MODIFIED by timestampformat= "Yyyy-mm-dd hh:mm:ss tt"
Example: Exprot to Filename.del for del MODIFIED by timestampformat= "YYYY-MM-DD hh:mm:ss tt" SELECT ... where ...;
About Import
Introduction to 1.Import Mode
Create/insert/insert_update/replace/replace_create
Create: First creates the target table and its index, and then imports the data into the new table. The only file format supported by this option is PC/IXF. You can also specify the name of the table space where the new table resides
Insert: Inserts the imported data into the table. The target table must already exist.
Insert_update: Inserts data into a table, or updates a row with a matching primary key in the table. The target table must already exist and a primary key is defined.
REPLACE: Deletes all existing data and inserts the imported data into an existing destination table.
Replace_create: If the target table already exists, the import utility deletes the existing data and inserts the new data, just like the REPLACE option. If the target table is not yet defined, first create the table and its associated indexes, and then import the data. As you might imagine, the input file must be a file in PC/IXF format, because that format contains a structured description of the exported table. If the target table is a parent table referenced by a foreign key, you cannot use Replace_create.
2. Bulk Submission
Commitcount, ensuring that the insert data is committed after Commitcount is a good way to import files with large amounts of data.
Example: Import from filename of del commitcount 50000 inserts into TabName; 1 2 Next page > full text reading tips: Try "←→" button, turn the page more convenient Oh!