DB2 Data Movement detailed

DB2 in the so-called data movement, including: 1. Imports of data (import) 2. Export of data (exports) 3. Loading (load) of the data. Imports and loads use DB2 's related commands to save data from a file in a format to a table in a database, which means that the data in a table in a DB2 database is saved in 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.

Exports of 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 (' 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 Del Select char (a) | | char (b) | | char (c) | | char (d,5) | | e | | char (f) as TMP from N

The results of this export are very similar to the ASC format file. Just a pair of double quotes around the front and back of each line, we can use text tools (such as WordPad, Notepad, etc.) to remove double quotes, or ignore, in the future when imported directly control the format (ignore double quotes)

The format in the file is:

"2004-10-2123.12.23abc hh 3.52E1"

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

Example four: The export of large data

Export to D:\myfile.del of Del lobs to d:\lob\ lobfile lobs modified with Lobsinfile select * from Emp_photo

This command exports the data from the Emp_photo table to the D:\myfile.del file, and the result is:

"000130", "Bitmap", "lobs.001.0.43690/"

"000130", "gif", "lobs.001.43690.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.39795/"

"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 the Blob type, where only one flag is saved, the equivalent of a pointer, and the true LOB data is stored in the D:\lob directory of lobs.001, lobs.002 、...... And a series of files. In the command, specify where the large object data is saved under LOBs to (note that the path must already exist in advance, otherwise it will be an error), Lobfile Specify the large object data to save in what file, do not specify the extension, DB2 will automatically append according to the amount of data. 001,. 002 Extension, Also, don't forget to add the modified by Lobsinfile clause.

Example five: Save the export information in a message file.

Export to D:\AWARDS.IXF of IXF messages D:\msgs.txt select * FROM staff where dept = 20

This example exports the DEPT=20 data from the staff table to the D:\awards.ixf file, where all the exported information is stored in the D:\msgs.txt file (whether it is a success, a warning, or a failure) so that the administrator can find out the problem by observing the information file.

Example six: 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, each column of data that is exported is automatically named with the corresponding field name in the table, and we can rename each column by using the method n clause, and note that this clause is only valid in IXF and WSF format files and cannot be used in text files.