DB2 data movement details

The so-called data movement in DB2 includes:

1. Import data)

2. Export data Export)

3. Load data ).

Both import and load use DB2 commands to save data in files of a certain format to tables in the database; export refers to saving the data in the table of the DB2 database to a file of a certain format.

The role of data movement:

To transfer data between different database management systems, data movement is usually the most practical method, because any database management system supports common file formats, this universal interface makes it easy to transfer data between different systems.

Among the three commands, Export is the simplest. Because data is transferred from a table to a file, there is usually no error or illegal data.

Before explaining commands, we will first introduce the file formats. There are four file formats for DB2 data movement:

1. ASC -- a non-bounded ASCII file, which is an ASCII plain stream. The rows in the data stream are separated by line delimiters, and each column in the row is defined by the start and end positions. For example:

10 Head Office 160 initialize ate New York

15 New England 50 Eastern Boston

20 Mid Atlantic 10 Eastern Washington

38 South Atlantic 30 Eastern Atlantic

42 Great Lakes 100 Midwest Chicago

51 Plains 140 Midwest Dallas

66 Pacific 270 Western San Francisco

84 Mountain 290 Western Denver

2. DEL -- specifies an ASCII file, which is also an ASCII transfer stream. The rows in the data stream are separated by line delimiters, and the column values in the 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, "initialize ate", "New York"

15, "New England", 50, "Eastern", "Boston"

20, "Mid Atlantic", 10, "Eastern", "Washington"

38, "South Atlantic", 30, "Eastern", "Atlantic"

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 for data exchange with the Lotus series software.

4. PC/IXF -- an adapted version of the integrated Exchange Format, IXF) data Exchange architecture, which consists of records with Variable Length of some columns, it includes the header record, Table Record, column descriptor record of each column in the table, and one or more data records of each row in the table. A pc/IXF file record consists of fields that contain character data.

Data Export)
Example 1: export all data in the Org table to the file C: \ ORG. TXT.

Export to c: \ org.txt of del select * from org

Here, of del indicates the type of the exported file. In this example, it is exported to a non-bounded text file. The select * from org following is an SQL statement, the result of this statement is the data to be exported.

Example 2: Change the control operator 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 various symbols. coldel indicates the delimiter between fields. By default, it is a comma. Now it is changed to $. chardel indicates the symbol used to reference string fields, by default, it is enclosed by a pair of double quotes. Now it is enclosed by a pair of single quotes. decplusblank indicates that the front plus sign is replaced by space for the decimal data type, by default, digits plus and minus signs are added before the decimal data.

Example 3: export data to a file in ASC format

The Export command does not support files in ASC format. to Export such a regular format as ASC, the programmer needs to convert all data types into fixed-length strings, then combine the fields to be exported into one field.

For example, create table n with the following structure:

Create table n (a int, B date, c time, d varchar (5), e char (4), f double)

Insert two pieces of data:

Insert into n values (15, '1970-10-21 ', '23: 12: 23', 'abc', 'hh', 2004)

Insert into n values (5, '2017-1-21 ', '3: 12: 23', 'bc', 'hhh', 2004)

To export the two data to a file in a regular format, perform the following operations:

Export to c: \ test.txt of del select char (a) | char (B) | char (c) | char (d, 5) | e | char (f) as tmp from n

In this way, the exported results are very similar to files in ASC format, but there is a pair of double quotation marks before and after each row. For this reason, we can use text tools such as WordPad and notepad to delete double quotation marks, you can also ignore the double quotation marks and directly control the format during future import)

The format in the file is:

"15 2004-10-2123.12.23abc hh 3.52E1"

"5 2004-01-2103.12.23bc hhh 3.5672E1"

Example 4: Export Big Data

Export to d: \ myfile. del of del lobs to d: \ lob \ lobfile lobs modified by lobsinfile select * from emp_photo

This command exports data from the emp_photo table to the d: \ myfile. del file. The result is:

"000130", "bitmap", "lobs.001.0.43690 /"

"000130", "gif", "lobs.001.43790.29540 /"

"000130", "xwd", "lobs.001.73230.45800 /"

"000140", "bitmap", "lobs.001.119030.71798 /"

"000140", "gif", "lobs.001.190828.29143 /"

"000140", "xwd", "lobs.001.219971.73908 /"

"000150", "bitmap", "lobs.001.293879.73438 /"

"000150", "gif", "lobs.001.367317.000095 /"

"000150", "xwd", "lobs.001.407112.75547 /"

"000190", "bitmap", "lobs.001.482659.63542 /"

"000190", "gif", "lobs.001.546201.36088 /"

"000190", "xwd", "lobs.001.582289.65650 /"

The third field is of the BLOB type. Only one flag is saved in the file, which is equivalent to a pointer. The real LOB data is stored in d: \ lobs.001, lobs.002 ,...... and other files. In the command, specify the path in which the big object data is stored after lobs to. Note that this path must already exist in advance; otherwise, an error will be reported.) The lobfile specifies the file in which the big object data is stored, do not specify an extension. DB2 automatically appends data based on the data volume. 001 ,. 002 extension. Do not forget to add the modified by lobsinfile clause.

Example 5: Save the export information in the message file.

Export to d: \ awards. ixf of ixf messages d: \ msgs.txt select * from staff where dept = 20

In this example, dept = 20 Data in the staff table is exported to d: \ awards. in the ixf file, all exported information is stored in the d: \ msgs.txt file, whether it is a success, warning, or failure message. In this way, the administrator can find the problem by observing the information file.

Example 6: Rename the exported data column.

Export to d: \ awards. ixf of ixf method n (c1, c2, c3, c4, c5, c6, c7) messages d: \ msgs.txt select * from staff where dept = 20

By default, the exported data in each column is automatically named by the corresponding field name in the table. We can use the method n clause to rename each column. Note that, this clause is only valid in ixf and wsf format files and cannot be used in text files.

DB2's data movement was first explained in the above section, and then explained through examples, so as to give users a better understanding of DB2's data movement, I hope you will be able to gain some benefits after learning the above.