The so-called data movement in DB2 includes:
1. Import of data
2. Export of data (Export)
3. Data loading (Load)
Importing and loading use DB2 related commands to save data in a file of a certain format to a table in the database.
Export refers to saving the data in the table of the DB2 database into a file of a certain format.
The role of data movement:
If you want to transfer data between different database management systems, data movement is usually the most practical method, because any database management system supports several commonly used file formats. Through this common interface, it is easy to transfer data between different systems.
Of these three commands, Export is the simplest, because transferring data from a table to a file usually does not cause errors or illegal data.
Before explaining the command, let’s first introduce the file format. There are four file formats for DB2 data movement:
1. ASC - a non-delimited ASCII file, is an ASCII character stream. Rows in the data stream are separated by row delimiters, while each column in the row is defined by the start and end positions. For example:
10 Head Office 160 Corporate New York
15 New England 50 Eastern Boston
20 Mid Atlantic 10 Eastern Washington
38 South Atlantic 30 Eastern Atlanta
42 Great Lakes 100 Midwest Chicago
51 Plains 140 Midwest Dallas
66 Pacific 270 Western San Francisco
84 Mountain 290 Western Denver
2. DEL - Delimited ASCII file, which is also an ASCII character stream. Rows in the data stream are separated by row delimiters, and column values in rows are separated by column delimiters. File type modifiers can be used to modify the default values of these delimiters. For example:
10,"Head Office",160,"Corporate","New York"
15,"New England",50,"Eastern","Boston"
20,"Mid Atlantic",10,"Eastern","Washington"
38,"South Atlantic",30,"Eastern","Atlanta"
42,"Great Lakes",100,"Midwest","Chicago"
51,"Plains",140,"Midwest","Dallas"
66,"Pacific",270,"Western","San Francisco"
84,"Mountain",290,"Western","Denver"
3. WSF—(work sheet format) is a worksheet format, used to exchange data with Lotus series software.
4. PC/IXF - is an adapted version of the data exchange architecture of the Integration Exchange Format (IXF) data exchange architecture. It consists of some columns of 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 are composed of fields containing character data.
Part 1: Export of data
Example 1: Export all data in the Org table to file C:\.
Export to c:\ of del select * from org
Among them, of del represents the type of the exported file, which in this case is exported to a non-delimited text file; the subsequent select * from org is an SQL statement, and the result of the query of this statement is the data to be exported.
Example 2: Change the format controller of the del format file
export to c:\ of del modified by coldel$ chardel decplusblank select * from staff
In this example, the modified clause is used to control various symbols. Coldel represents the spacer between fields. The default is a comma, and now it is changed to the $ sign; chardel represents what symbol to quote the string field. By default, it is enclosed in a pair of double quotes, and now it is enclosed in a pair of single quotes; decplusblank represents the use of spaces instead of the top plus sign for decimal data types, because by default, the positive and negative signs will be added in front of the decimal data.
Example 3: Export data to file in ASC format
The Export command does not support ASC format files, so if you want to export a regular format like ASC, the programmer needs to convert it himself. The idea is to convert various data types into fixed-length strings, and then merge the fields to be exported into one field.
For example, create a table n with the following structure:
create table n(a int,b date,c time,d varchar(5),e char(4),f double)
Then insert two pieces of 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 pieces of data to the file in a regular format, perform the following operations:
export to c:\ of d
1. Import of data
2. Export of data (Export)
3. Data loading (Load)
Importing and loading use DB2 related commands to save data in a file of a certain format to a table in the database.
Export refers to saving the data in the table of the DB2 database into a file of a certain format.
The role of data movement:
If you want to transfer data between different database management systems, data movement is usually the most practical method, because any database management system supports several commonly used file formats. Through this common interface, it is easy to transfer data between different systems.
Of these three commands, Export is the simplest, because transferring data from a table to a file usually does not cause errors or illegal data.
Before explaining the command, let’s first introduce the file format. There are four file formats for DB2 data movement:
1. ASC - a non-delimited ASCII file, is an ASCII character stream. Rows in the data stream are separated by row delimiters, while each column in the row is defined by the start and end positions. For example:
10 Head Office 160 Corporate New York
15 New England 50 Eastern Boston
20 Mid Atlantic 10 Eastern Washington
38 South Atlantic 30 Eastern Atlanta
42 Great Lakes 100 Midwest Chicago
51 Plains 140 Midwest Dallas
66 Pacific 270 Western San Francisco
84 Mountain 290 Western Denver
2. DEL - Delimited ASCII file, which is also an ASCII character stream. Rows in the data stream are separated by row delimiters, and column values in rows are separated by column delimiters. File type modifiers can be used to modify the default values of these delimiters. For example:
10,"Head Office",160,"Corporate","New York"
15,"New England",50,"Eastern","Boston"
20,"Mid Atlantic",10,"Eastern","Washington"
38,"South Atlantic",30,"Eastern","Atlanta"
42,"Great Lakes",100,"Midwest","Chicago"
51,"Plains",140,"Midwest","Dallas"
66,"Pacific",270,"Western","San Francisco"
84,"Mountain",290,"Western","Denver"
3. WSF—(work sheet format) is a worksheet format, used to exchange data with Lotus series software.
4. PC/IXF - is an adapted version of the data exchange architecture of the Integration Exchange Format (IXF) data exchange architecture. It consists of some columns of 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 are composed of fields containing character data.
Part 1: Export of data
Example 1: Export all data in the Org table to file C:\.
Export to c:\ of del select * from org
Among them, of del represents the type of the exported file, which in this case is exported to a non-delimited text file; the subsequent select * from org is an SQL statement, and the result of the query of this statement is the data to be exported.
Example 2: Change the format controller of the del format file
export to c:\ of del modified by coldel$ chardel decplusblank select * from staff
In this example, the modified clause is used to control various symbols. Coldel represents the spacer between fields. The default is a comma, and now it is changed to the $ sign; chardel represents what symbol to quote the string field. By default, it is enclosed in a pair of double quotes, and now it is enclosed in a pair of single quotes; decplusblank represents the use of spaces instead of the top plus sign for decimal data types, because by default, the positive and negative signs will be added in front of the decimal data.
Example 3: Export data to file in ASC format
The Export command does not support ASC format files, so if you want to export a regular format like ASC, the programmer needs to convert it himself. The idea is to convert various data types into fixed-length strings, and then merge the fields to be exported into one field.
For example, create a table n with the following structure:
create table n(a int,b date,c time,d varchar(5),e char(4),f double)
Then insert two pieces of 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 pieces of data to the file in a regular format, perform the following operations:
export to c:\ of d