Exports data from a database to one of several external file formats. The user specifies the data to be exported by supplying an SQL SELECT statement.
Authorization
One of the following:
or CONTROL or SELECT privilege on each participating table or view.
Required Connection
Database. If implicit connect is enabled, a connection to the default database is established.
>>-EXPORT TO--filename--OF--filetype----------------------------> >--+-------------------------+--+-------------------------+-----> | +-,---------+ | | +-,---------+ | | V | | | V | | +-LOBS TO----lob-path--+--+ +-LOBFILE----filename--+--+ >--+---------------------------------+--------------------------> | +---------------+ | | V | | +-MODIFIED BY----filetype-mod--+--+ >--+-----------------------------------+------------------------> | +-,------------+ | | V | | +-METHOD N--(----column-name--+--)--+ >--+-------------------------+-select-statement---------------->< +-MESSAGES--message-file--+ |
Command Parameters
If the name of a file that already exists is specified, EXPORT overwrites the contents of the file; it does not append the information.
For more information about file formats, see Appendix C. "IMPORT/EXPORT/LOAD Utility File Formats".
When creating LOB files during an export, filenames are constructed by appending the current base name from this list to the current path (from lob-path), and then appending a 3-digit sequence number. For example, if the current LOB path is the directory /u/foo/lob/path, and the current LOB file name is bar, then the LOB files created will be /u/foo/lob/path/bar.001, /u/foo/lob/path/bar.002, and so on.
Examples
The following example shows how to export information from the STAFF table in the SAMPLE database (to which the user must be connected) to myfile.ixf, with the output in IXF format:
db2 export to myfile.ixf of ixf messages msgs.txt select * from staff
The following example shows how to export the information about employees in Department 20 from the STAFF table (in the database to which the user must be connected) to awards.ixf, with the output in IXF format:
db2 export to awards.ixf of ixf messages msgs.txt select * from staff
where dept = 20
Usage Notes
Be sure to complete all table operations and release all locks before issuing the EXPORT command. This can be done either by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK. A COMMIT is performed during the export process.
Table aliases can be used in the SELECT statement.
The messages placed in the message file include the information returned from the message retrieval service. Each message begins on a new line.
The export utility produces a warning message whenever a character column with a length greater than 254 is selected for export to DEL format files.
PC/IXF import should be used to move data between databases. If character data containing row separators is exported to a delimited ASCII (DEL) file and processed by a text transfer program (moving, for example, between OS/2 and AIX systems), fields containing the row separators will shrink or expand.
PC/IXF file format specifications permit migration of data between OS/2 (IBM Extended Services for OS/2, OS/2 Extended Edition and DB2 for OS/2) databases and DB2 for AIX databases via export, binary copying of files between OS/2 and AIX, and import. The file copying step is not necessary if the source and the target databases are both accessible from the same client.
DB2 Connect can be used to export tables from DRDA servers such as DB2 for MVS, SQL/DS, and OS/400. Only PC/IXF export is supported.
The EXPORT command will not create multiple-part PC/IXF files when invoked from an AIX system.
| Note: | The export utility does not issue a warning if an attempt is made to use unsupported file types with the MODIFIED BY option. If this is attempted, the export fails, and an error code is returned. |
Table 6. Valid File Type Modifications (EXPORT)
| Modification | Description | ||
|---|---|---|---|
| All File Formats | |||
| lobsinfile | lob-path specifies the path to the files containing LOB values. | ||
| DEL (Delimited ASCII) File Format | |||
| chardel x | x is a single character string delimiter. The default value is
a double quotation mark ("). The specified character is used in place of
double quotation marks to enclose a character string.a
The single quotation mark (') can also be specified as a character string delimiter as follows: modified by chardel'' | ||
| coldel x | x is a single character column delimiter. The default value is
a comma (,). The specified character is used in place of a comma to
signal the end of a column.a
In the following example, coldel; causes the export utility to interpret any semicolon (;) it encounters as a column delimiter: db2 "export to temp of del modified by coldel;
select * from staff where dept = 20"
| ||
| datesiso | Date format. Causes all date data values to be exported in ISO format. | ||
| decplusblank | Plus sign character. Causes positive decimal values to be prefixed with a blank space instead of a plus sign (+). The default action is to prefix positive decimal values with a plus sign. | ||
| decpt x | x is a single character substitute for the period as a decimal point character. The default value is a period (.). The specified character is used in place of a period as a decimal point character.a | ||
| WSF File Format | |||
| 1 | Creates a WSF file that is compatible with Lotus 1-2-3 Release 1, or Lotus 1-2-3 Release 1a.b This is the default. | ||
| 2 | Creates a WSF file that is compatible with Lotus Symphony Release 1.0.b | ||
| 3 | Creates a WSF file that is compatible with Lotus 1-2-3 Version 2, or Lotus Symphony Release 1.1.b | ||
| 4 | Creates a WSF file containing DBCS characters. | ||
| |||
| Hex | Char | Character Name | ||
|---|---|---|---|---|
| X'22' | " | Double Quotation Marks | ||
| X'25' | % | Percent Sign | ||
| X'26' | & | Ampersand | ||
| X'27' | ' | Apostrophe | ||
| X'28' | ( | Left Parenthesis | ||
| X'29' | ) | Right Parenthesis | ||
| X'2A' | * | Asterisk | ||
| X'2C' | , | Comma | ||
| X'2E' | . | Period (not valid as a character string delimiter) | ||
| X'2F' | / | Slash | ||
| X'3A' | : | Colon | ||
| X'3B' | ; | Semicolon | ||
| X'3C' | < | Less Than Sign | ||
| X'3D' | = | Equals Sign | ||
| X'3E' | > | Greater Than Sign | ||
| X'3F' | ? | Question Mark | ||
| X'7C' | | | Vertical Bar | ||
| X'5F' | _ | Underscore (valid in the SBCS environment only) | ||
| ||||
See Also