Data movement in DB2 (i) _db2

The so-called data movement in the DB2 includes:
1. Imports of data (import)
2. Exports of data (export)
3. Loading (load) of data

Import and mount are all using DB2 's related commands to save data in a file in a format to a table in the database
Exporting refers to saving data from a table in a DB2 database to a file in a format

The role of data movement:

If you want to transfer data between different database management systems, data movement is usually one of the most useful methods, because any database management system supports several commonly used file formats, and through this generic interface it is easy to transfer data between different systems.

Of these three commands, export is the simplest because data is transferred from a table to a file, and there is usually no error and no illegal data.

Before explaining the command, first describe the format of the file, which is used for DB2 data movement in four different formats:
1. asc--non-bounding ASCII file, is an ASCII character stream. Rows in the data flow are delimited by row delimiters, and each column in the row is defined by the starting and ending positions. For example:

Ten head Office 160 Corporate New York
New England Eastern Boston
Mid Atlantic Eastern Washington
South Atlantic Eastern Atlanta
Great Lakes Midwest Chicago
Wuyi Plains 140 Midwest Dallas
Pacific 270 Western San Francisco
Mountain 290 Western Denver

2. del---bound ASCII file is also an ASCII character stream. Rows in the data flow are delimited by row delimiters, and column values in rows are delimited by column delimiters. File type modifiers can be used to modify the default values for these delimiters. For example:

"Head Office", 160, "Corporate", "New York"
"New England", "Eastern", "Boston"
"Mid Atlantic", "Eastern", "Washington"
"South Atlantic", "Eastern", "Atlanta"
"Great Lakes", "Midwest", "Chicago"
Wuyi, "Plains", 140, "Midwest", "Dallas"
"Pacific", 270, "Western", "San Francisco"
290, "Mountain", "Western", "Denver"

3. wsf--(Work sheet format) is a working table type for data exchange with the Lotus series of software.

4. pc/ixf--is an adapted version of the Integrated Interchange Format (integration Exchange FORMAT,IXF) Data Interchange architecture, consisting of a number of column variable-length records, including header records, table records, Column descriptor records for each column in the table and one or more data records for each row in the table. PC/IXF file records consist of fields that contain character data.

Part I: Exporting data (export)
Example one: Export all the data from the ORG table to the file C:\ORG. TXT medium.
Export to C:\org.txt of del select * from org

wherein, the type of the exported file is exported to a non-bounding text file in this example, and the following select * from org is an SQL statement that queries the result of the data to be exported.

Example two: Change the format control character of the Del format file
Export to C:\staff.txt of Del Modified by coldel$ chardel Decplusblank SELECT * from staff
In this example, the modified clause is used to control the various symbols, Coldel represent the spacer between the fields, the default is a comma, and now the $ number; Chardel represents the string field by what symbolic reference, by default, a pair of double quotes, and now it's enclosed in a pair of single quotes. Decplusblank indicates that for decimal data types, spaces are substituted for the first plus sign, because the decimal data is prefixed by default.

Example three: Export data to a file in ASC format
The Export command does not support the ASC format file, so if you want to derive the regular format of ASC, you need the programmer to convert the various data types into fixed-length strings, and then merge the fields you want to export into a single field.
For example, CREATE table N of the following structure:
CREATE Table N (a int,b date,c time,d varchar (5), E char (4), F double)
Then insert two data:
INSERT into n values (15,2004-10-21,23:12:23,abc,hh,35.2)
INSERT into n values (5,2004-1-21,3:12:23,bc,hhh,35.672)
To export these two data to a file in a structured format, do the following:
Export to C:\test.txt of D